How to find earliest record based on unique identifier from a list ofdata

[Lee]

I have a bunch of test data. Column A is the date, Column C is the
time and Column E is the unique identifier. Because this is test
data, a failed unit might be tested multiple times. What I need is a
way to find the earliest test time of the unique identifier in Column
E.

How do I restrict the data to return me the earliest date and time for
a unique unit from Column E, when the date and time are in two
separate columns?


Column A Column B Column C Column
D Column E
12-Mar-08 XXXXXXX 15:22:25
XXXXXXXX 12345678
12-Mar-08 XXXXXXX 15:25:25
XXXXXXXX 12345679
12-Mar-08 XXXXXXX 15:28:27
XXXXXXXX 12345680
12-Mar-08 XXXXXXX 15:32:45
XXXXXXXX 12345681
12-Mar-08 XXXXXXX 16:22:25
XXXXXXXX 12345678
12-Mar-08 XXXXXXX 16:25:25
XXXXXXXX 12345679
12-Mar-08 XXXXXXX 15:28:27
XXXXXXXX 12345698
12-Mar-08 XXXXXXX 15:32:45
XXXXXXXX 12345699

In the example above there are two duplicates (12345678, and
12345679). I would only want to see the earliest of the two records
based on the identifier in column E.

Thanks!
Lee

 

[Bernd P]

Hello Lee,

I suggest to take mz UDF Mfreq with the Min parameterÖ
http://sulprobil.com/html/mfreq.html

Regards,
Bernd

 

[Luke M]

Try this array** formula to find earliest time for 12345678:

=MIN(IF($E$2:$E$200=12345678,$A$2:$A$200+$C$2:$C$200))

Increase range sizes as needed. Do not use a column callout (A:A)
Note that you can replace the 12345678 with a cell reference, if desired.

**Array formulas need to be confirmed using Ctrl+Shift+Enter, not just Enter.

 

[Lee]

Try this array** formula to find earliest time for 12345678:

=MIN(IF($E$2:$E$200=12345678,$A$2:$A$200+$C$2:$C$200))

Increase range sizes as needed. Do not use a column callout (A:A)
Note that you can replace the 12345678 with a cell reference, if desired.

**Array formulas need to be confirmed using Ctrl+Shift+Enter, not just Enter.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*

That works, but I want to be able to get all unique records, not just
12345678. In other words... there may be 1000 rows. Column E may
represent 768 unique units tested. I want 768 unique rows with the
earliest test time for each row. Hope I am making sense!!!

Thanks for your help!

lee

 

[Luke M]

You can use Data - Filter, Advanced Filter to create a unique list of numbers
(copy somewhere). Then use the formula I gave, base it off of this new list,
copy down the list of unique numbers to generate list of earliest time.