Forum Discussion
Extracting data in the new sheet
the attached excel sheet contain 3 columns
i want to extract the rows for every (customer no) with the oldest dates
for instance for customer no: 345467 i need
| 2 | 21/04/2018 | 345467 |
| 3 | 20/04/2018 | 345467 |
on the sheet 2
for customer no 321450
i need to extract
| 5 | 19/04/2018 | 321450 |
and so on
for more clarification if there are 5 records saves for each customer no i need to extract the oldest four in a new sheet if there are 4 records for any customer no i need to extract the oldest 3 ....
Reciept No | Date | Customer No
| 1 | 22/04/2018 | 345467 |
| 2 | 21/04/2018 | 345467 |
| 3 | 20/04/2018 | 345467 |
| 4 | 20/04/2018 | 321450 |
| 5 | 19/04/2018 | 321450 |
| 6 | 16/04/2018 | 32134 |
| 7 | 15/04/2018 | 111111 |
| 8 | 15/04/2018 | 111111 |
| 9 | 14/04/2018 | 111111 |
2 Replies
Hi,
That could be done with Power Query (add-in for 2010, 2013, built-in in 2016 as Get&Transform)
- first query loads all receipts
-duplicate it in second query, sort by customer and date and remove duplicates keeping only newest receipts for each customer
- left anti join first query with second one, land the result into any place in workbook
Please see attached
- Nice approach
