Dec 12 2018 04:59 PM
Hi excel community,
what is the best lookup formula to return all result in table_array.
This is my sample dataset
Customer name | Item sold | Date |
Ali | Books | 1/1/2010 |
Ali | Table | 2/5/2010 |
Ali | Furniture | 6/8/2010 |
James | Furniture | 7/1/2010 |
James | Books | 8/3/2010 |
James | Table | 9/10/2010 |
I want to generate the report how much item sold by Customer name.
If i select customer name (Ali), i want all result for item sold and date for Ali will return as per below
Customer name | Ali | |
Item sold | Books | 1/1/2010 |
Table | 2/5/2010 | |
Furniture | 6/8/2010 |
Dec 12 2018 05:46 PM
Dec 12 2018 11:28 PM
Alternatively you can use a pivot table. Please check the attached file.
Dec 13 2018 08:25 AM
This is a good solution!
But a problem that may occur when the same customer buys the same item again in a different day.
In this case, the PivotTable will show up only the most recent item, this is because you choose to summarize the values by max.
I think the most perfect solution is done by using Power Query as the below screenshot and the attached workbook.
Try to change the customer name, then right-click and refresh the orange table and see how it works.