Apr 15 2021 01:31 AM
Hi, I'm hoping someone can help. I have 3 columns of data (Customer, Discount & Discount %).
As there is a lot of data, I am trying to convert this to a table
I need to basically cross reference customer (Column A) & discount (Row 1) and the result be the corresponding cell as below:
I hope this makes sense and any help would be greatly appreciated as it is driving me crazy :)
Apr 15 2021 01:47 AM
Why dont you make a Pivot Table?
Insert->Pivot Table
Customer in rows
Discount in column
Discount% in Value
Copy the pivot table and change the function in the copy to Count if you want to make sure you don't have duplicates.
Apr 15 2021 01:54 AM
Select the data.
On the Data tab of the ribbon, in the Get & Transform Data group, click 'From Table/Range'.
If your data aren't formatted as a table yet, Excel will ask whether your range has headers. Make sure that the check box is ticked, then click OK.
The Power Query Editor will open.
Select the Discount column in the data preview.
Activate the Transform tab.
Click 'Pivot Column' in the 'Any Column' group.
Specify Discount % as the column to use for the values.
Click OK.
Activate the Home tab of the Power Query Editor again.
Click Close & Load.
Apr 15 2021 01:56 AM
Apr 15 2021 02:29 AM