Forum Discussion
Data is spread in multiple rows, need to put into tabular format
Hi all,
Stuck on a problem and not sure what the correct terms are in order to search the answer.
I have a table with data in a journal style with duplicate lines for each product the customer has bought ie this.
| Customer | Date | Product Sold | Price |
| Sam | 01/01/2024 | Watch | £50.00 |
| Sam | 01/01/2024 | Bike | £100.00 |
| Fred | 03/03/2024 | Bike | £100.00 |
| Fred | 03/03/2024 | Watch | £399.00 |
| Fred | 03/03/2024 | Jacket | £300.00 |
| Peter | 03/03/2024 | Bike | £100.00 |
| Jane | 03/03/2024 | Watch | £50.00 |
| Jane | 03/03/2024 | Pen | £100.00 |
| Dave | 09/01/2024 | Watch | £75.00 |
| Dave | 09/01/2024 | Jacket | £300.00 |
| Steve | 11/01/2024 | Watch | £50.00 |
| Steve | 11/01/2024 | Jacket | £300.00 |
| Steve | 11/01/2024 | Bike | £100.00 |
What I need to get to is the data with one line per customer record, showing all products bought.
| Customer | Date | Watch | Bike | Jacket | Pen |
| Sam | 01/01/2024 | 50 | 10 | ||
| Fred | 03/03/2024 | 399 | 100 | 300 | |
| Peter | 03/03/2024 | 100 | |||
| Jane | 03/03/2024 | 50 | 100 | ||
| Dave | 09/01/2024 | 75 | 300 | ||
| Steve | 11/01/2024 | 100 |
What is the best way to achieve this please 🙂
An alternative could be Power Query. In the attached file you can add data to the blue dynamic table. Then you can click in any cell of the green table and right-click with the mouse and select refresh to update the green result table.
The data layout in the screenshot and in the attached file is for illustration. You can place the green result table below the blue table or in another worksheet.
=PIVOTBY(HSTACK(A2:A14,B2:B14),C2:C14,D2:D14,SUM,,0,,0)
With Office 365 you can apply PIVOTBY. However as far as i know the formula is currently only available in the insider channel. The screenshot is from Excel for the web.
If you're using 365, PIVOTBY is the way to go:
=PIVOTBY(Salestbl[[Customer]:[Date]], Salestbl[Product Sold], Salestbl[Price], SUM)This formula was drawnup with your sample data tabled. Some of your expected calculations seem to be missing some totals. I've attached a workbook for your review.
