filter values with earliest record (in a list with duplicates)

[jioben]

hello everyone,

i am working on a dataset that is about 500k of entries with duplicates.

the ideal output i need is to get the unique names in the list but should be with the oldest/earliest date recorded and consider the two given dates.

what would be the best way so i can come up with the data i need considering the two scenario above?

suggestions other than using filter will also be appreciated.

TIA

 

[KRice]

See if this works for you:

MrExcel_20220603.xlsx
	A	B	C	D	E	F	G	H
1	Code	Name	RelDate	IntDate		Code	Name	Date
2	1	Leroy	3/12/2020	3/12/2020		1	Leroy	8/14/2019
3	1	Leroy	8/14/2019	11/27/2020		2	Daisy	3/6/2020
4	1	Leroy	3/12/2020	11/27/2020		3	Bob	7/15/2019
5	1	Leroy	10/29/2021	11/27/2020		4	Maggie	1/14/2019
6	1	Leroy	3/12/2020	10/29/2020
7	2	Daisy	10/22/2020	3/6/2020
8	2	Daisy	9/24/2021	3/6/2020
9	2	Daisy	3/12/2020	3/6/2020
10	2	Daisy	9/24/2021	3/6/2020
11	2	Daisy	3/12/2020	3/12/2020
12	3	Bob	7/15/2019	8/16/2019
13	4	Maggie	1/17/2019	1/20/2019
14	4	Maggie	1/15/2019	1/14/2019
Sheet10

Cell Formulas
Range	Formula
F2:G5	F2	=UNIQUE($A$2:$B$14)
H2:H5	H2	=LET(fndt,FILTER($C$2:$D$14,$B$2:$B$14=INDEX($F$2#,ROWS(F$2:F2),2)),MIN(FILTER(fndt,INDEX(fndt,,1)=MIN(INDEX(fndt,,1)))))
Dynamic array formulas.

 

[jioben]

thank you very much

 

[KRice]

I'm happy to help. I hope I understood the objective...for any given unique name, find the minimum Released Date to identify the row of interest, and then on that same row, choose the minimum of either the Release Date or the Interview Infodate?

 

[jioben]

hello sir,

a follow up question on the data given above;

a. how about if i want to extract that unique value from the given dataset but should consider also the earliest date?

b. remove the duplicate names and retain the unique names with the earliest date?

 

[KRice]

The solution I offered in Post #2 returns unique combinations of Codes and Names that are present in the source data, and if there are duplicates, it uses the row where the Released Date is earliest. Then it compares the Released Date to the Interview Date on that same row and chooses the earliest. Is that what you mean by “extract that unique value from the given dataset”? As for removing duplicate names and retaining only the unique ones with the earliest dates, there is no way to do that with a formula if your objective is to modify the original source data. However, you could do that using VBA or Power Query. Alternatively, you could use the formulas to produce the trimmed down table of unique codes/names/early dates and then copy those results and Paste Special > Values somewhere to preserve the results independent of the linkage to the original source table.

 

[jioben]

thank you so much for the help and clarification

 

[KRice]

You're welcome.