# Find Duplicates and keep the Last record in a sequence.

#### mrjsilva

##### New Member
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!!

### Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

#### Domenic

##### MrExcel MVP
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!

Replies
6
Views
613
Replies
3
Views
223
Replies
2
Views
309
Replies
2
Views
170
Replies
15
Views
349

1,171,968
Messages
5,878,537
Members
433,352
Latest member
horna

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back