Find Duplicates and keep the Last record in a sequence.

mrjsilva

New Member
Joined
Oct 2, 2006
Messages
1
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

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,696
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!
 

Forum statistics

Threads
1,136,345
Messages
5,675,225
Members
419,555
Latest member
Paddington

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top