Working with multiple headings and subheadings in Excel sheet

unknown

I have an Excel sheet with headings for 'Year' and 'Make of Car'. However, under each of the 'Make of Car' headings, there are the subheadings 'Cost Price', 'Selling Price' and 'Total Sales'.

Please see example below:

I am wanting the final output to only have the headings:

• Model Year
• Make of Car
• Cost Price
• Selling Price
• Total Sales

Please see desired final output below:

I have attempted to do this with with the following steps:

1) Rename all the 'Cost Price' columns to the car make. For example, the cost price column for Ford has been renamed to 'Ford'.

2) Unpivot / convert columns into rows of keys (car make) and values (cost price)

3) Repeat steps 1 and 2 for both Selling Price and Total Sales

This however leaves me with duplicate 'Make of Car' columns. I can find a way to filter these so that only the relevant ones show, but I feel like there is probably a less tedious way to do this transformation.

Some help would be much appreciated!

Trifacta_Alumni

Hi @Casey Butcher? ?,

Thank you for sharing these instructions and this information! You can accomplish this by using the “Merge columns” transformation. Select the “Cost Price”, “Selling Price”, and “Total Sales” columns to merge:Name the new column the car make, “Ford” for example. Repeat this step with the other car makes. You can delete the old columns once merging columns is done.

Next, you can use the “Unpivot” transformation to convert the “Ford”, “Toyota”, and "Hyundai” columns into rows of keys (“Make”) and values (“Cost Price”, “Selling Price”, and “Total Sales”) while keeping the group size to 1:

Your screen should now have a “key and “value” column. To split the “Cost Price”, “Selling Price”, and “Total Sales” values in “value” column, you can use the “Split between delimiters” transformation. Select the “value” column. Under option, select “By delimiter”. In the “Delimiter” field, type in the comma (including the quotation marks). Lastly, select the number of commas you want to split in the “Number of splits” field. Your screen should look like this after composing your step:

Once your values have been split, you can use the “Rename columns” transformation to rename the “key” (“Make”), “value1” (“Cost Price”), “value2” (“Selling Price”), and “value3” (“Total Sales”) columns:

Let me know if this helps! If it does, please mark the answer "Best answer" so that other users know that your question has been resolved. 🙂