Find Duplicates and keep the Last record in a sequence.

[mrjsilva]

I have searched the forum for an example of what I am trying to do with no avail. So I ask the powers that be for a little help.

I have product quality data that has been exported from a database to an excel spreadsheet. This data contains rows of information from quality test performed on various samples. When a sample is tested, if it is out of spec it will be adjusted and retested until the data comes within spec. Any given sample can have 1 test or 10 test associated with it. I need to extract the final test value for a particular sample, and remove all other data for that sample.

Here is a sample of the data:
Date/Time Sample ID Test Value A Test Value B Test Value C
1/1/06 9:00 SAMPLE01 10% 10% 10%
1/1/06 9:30 SAMPLE01 11% 11% 11%
1/1/06 9:40 SAMPLE01 12% 12% 12%
1/1/06 9:59 SAMPLE01 15% 15% 15%
1/2/06 8:00 SAMPLE02 15% 15% 15%
1/3/06 7:10 SAMPLE03 14% 14% 14%
1/3/06 7:20 SAMPLE03 15% 15% 15%

A sample can have mulitple test associated with it, but a test can only have one sample. I basically want to extract the last data point for a sample. Once I have all the final sample tests, I then can perform statistical analysis on the data, and not worry about data being tainted by previous tests.

Thank you in advance for the help!!

 

[Domenic]

Assuming that the data is sorted first by Sample ID, in ascending order, and then by Date/Time, also in ascending order, and that A2:E8 contains the data, try the following...

F1: 0

(Enter a zero in F1)

F2, copied down:

=IF(LOOKUP(B2,B2:$B$8,ROW(B2:$B$8))=ROW(F2),LOOKUP(9.99999999999999E+307,$F$1:F1)+1,"")

G1:

=LOOKUP(9.99999999999999E+307,F2:F8)

H2, copied down and across:

=IF(ROWS(H$2:H2)<=$G$1,LOOKUP(ROWS(H$2:H2),$F$2:$F$8,A$2:A$8),"")

Hope this helps!