Hi - I have a large number of customer feedback scores I need to compare the start and end survey (there are 3 different surveys) score of
The data looks like this
| Customer ID | Salesman | Product ID | Area | Meeting Number | M Date | Survery 1 | Survey 2 | Survey 3 |
| 365661 | GD | 8311 | North | 2 | 06/10/17 | 7 | 1 | 10 |
| 368466 | GD | 8311 | North | 2 | 06/10/17 | 3 | 3 | 10 |
| 200497 | GD | 8311 | North | 2 | 06/10/17 | 13 | 12 | 23 |
| 383866 | GD | 8311 | North | 2 | 06/10/17 | 0 | 2 | 0 |
| 197282 | GD | 8311 | North | 2 | 06/10/17 | 14 | 4 | 22 |
| 384841 | GD | 8311 | North | 2 | 06/10/17 | 1 | 0 | 0 |
| 384846 | GD | 8311 | North | 2 | 06/10/17 | 2 | 1 | 5 |
| 386631 | GD | 8311 | North | 2 | 06/10/17 | 2 | 8 | 0 |
| 386721 | GD | 8311 | South | 2 | 06/10/17 | |||
| 388591 | GD | 8311 | North | 2 | 06/10/17 | 11 | 11 | 8 |
| 365661 | GD | 8311 | North | 3 | 13/10/17 | |||
| 368466 | GD | 8311 | North | 3 | 13/10/17 | 3 | 4 | 8 |
| 200497 | GD | 8311 | North | 3 | 13/10/17 | 9 | 9 | 18 |
| 383866 | GD | 8311 | North | 3 | 13/10/17 | 0 | 1 | |
| 197282 | GD | 8311 | North | 3 | 13/10/17 | 18 | 7 | 22 |
| 384841 | GD | 8311 | North | 3 | 13/10/17 | 1 | 0 | 0 |
| 384846 | GD | 8311 | North | 3 | 13/10/17 | 4 | 0 | 0 |
| 386631 | GD | 8311 | North | 3 | 13/10/17 | 2 | 2 | 0 |
| 386721 | GD | 8311 | South | 3 | 13/10/17 | 0 | 3 | 0 |
| 388591 | GD | 8311 | North | 3 | 13/10/17 | 12 | 9 | 25 |
| 365661 | GD | 8311 | North | 4 | 20/10/17 | 0 | 0 | 0 |
| 368466 | GD | 8311 | North | 4 | 20/10/17 | 3 | 3 | 7 |
| 200497 | GD | 8311 | North | 4 | 20/10/17 | |||
| 383866 | GD | 8311 | North | 4 | 20/10/17 | 0 | 1 | 0 |
| 197282 | GD | 8311 | North | 4 | 20/10/17 | 15 | 6 | 19 |
| 384841 | GD | 8311 | North | 4 | 20/10/17 | 1 | 0 | |
| 384846 | GD | 8311 | North | 4 | 20/10/17 | 6 | 5 | 4 |
| 386631 | GD | 8311 | North | 4 | 20/10/17 | 3 | 3 | 3 |
| 386721 | GD | 8311 | South | 4 | 20/10/17 | 0 | 3 | 0 |
| 388591 | GD | 8311 | North | 4 | 20/10/17 | 9 | 10 |
Any suggestions on a formula I can use that will use the customer ID as a base, then pull out the earliest survey score and the latest survey score?
I'd need it to look a bit like
CUSTOMER ID - EARLIEST SURVEY 1 SCORE - LATEST SURVEY 1 SCORE - DIFFERENCE BETWEEN THE SCORES
Thanks!!
Hi Daniel,
Assuming that the data table is sorted by date, smallest to largest, you can use these 2 formulas to get the first and last matches:
=INDEX([Survery 1],MATCH([@[Customer ID]],[Customer ID],0)) (Match function will get the first match always)
=LOOKUP(2,1/([Customer ID]=[@[Customer ID]]),[Survey 3]) LOOKUP will get the last match.
Attached is the file with these formulas applied.
