|
How to find earliest record based on unique identifier from a list ofdata
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 |
How to find earliest record based on unique identifier from alist of data
Hello Lee,
I suggest to take mz UDF Mfreq with the Min parameterÖ http://sulprobil.com/html/mfreq.html Regards, Bernd |
How to find earliest record based on unique identifier from a list
Try this array** formula to find earliest time for 12345678:
=MIN(IF($E$2:$E$200=12345678,$A$2:$A$200+$C$2:$C$2 00)) 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!* "Lee" wrote: 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 |
How to find earliest record based on unique identifier from alist
On Jul 24, 9:47*am, Luke M wrote:
Try this array** formula to find earliest time for 12345678: =MIN(IF($E$2:$E$200=12345678,$A$2:$A$200+$C$2:$C$2 00)) 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!* "Lee" wrote: 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 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 |
How to find earliest record based on unique identifier from a
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. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Lee" wrote: On Jul 24, 9:47 am, Luke M wrote: Try this array** formula to find earliest time for 12345678: =MIN(IF($E$2:$E$200=12345678,$A$2:$A$200+$C$2:$C$2 00)) 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!* "Lee" wrote: 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 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 |
| All times are GMT +1. The time now is 09:29 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com