Return unique values based on multiple conditions and sorted by date

[emoandy05]

Hello,

I have data similar to whats shown in the "Orig Data' table. I would like to pull unique Names only, based on two criteria (Start Date <= today AND End Date >= today. I would also like the data sorted (oldest to newest) based on the Name's corresponding End Date . My goal is to have a table similar to the 'Result' table below. I do not necessarily need the End Dates to be in an adjacent column, just need the Names sorted.

All duplicate Names will have the same Start Date and End Date.

Thank you very much in advance!

Orig Data
Name	Start Date	End Date
333​	20-Nov​	24-Nov​
333​	20-Nov​	24-Nov​
444​	25-Nov​	10-Dec​
444​	25-Nov​	10-Dec​
555​	24-Nov​	5-Dec​
111​	20-Nov​	20-Dec​
111​	20-Nov​	20-Dec​
222​	26-Nov​	26-Dec​

Result
Name	End Date
555​	5-Dec​
444​	10-Dec​
111​	20-Dec​
222​	26-Dec​

 

[DRSteele]

That's easy with the new functions in Excel 365 Insider, UNIQUE and SORT.
Which version of Excel are you using?

 

[Eric W]

Without the new functions, you can do it like this:

Book1
	A	B	C	D	E	F
1	Orig Data				Result
2	Name	Start Date	End Date		Name	End Date
3	333	11/20/2019	11/24/2019		555	12/5/2019
4	333	11/20/2019	11/24/2019		444	12/10/2019
5	444	11/25/2019	12/10/2019		111	12/20/2019
6	444	11/25/2019	12/10/2019		222	12/26/2019
7	555	11/24/2019	12/5/2019
8	111	11/20/2019	12/20/2019
9	111	11/20/2019	12/20/2019
10	222	11/26/2019	12/26/2019
11
Sheet3

Cell Formulas
Range	Formula
E3:E9	E3	=IFERROR(INDEX(A:A,MOD(AGGREGATE(15,6,($C$3:$C$10*1000+ROW($C$3:$C$10))/(COUNTIF($E$2:$E2,$A$3:$A$10)=0)/($B$3:$B$10<=TODAY())/($C$3:$C$10>=TODAY()),1),1000)),"")
F3:F9	F3	=IF(E3<>"",INDEX($C$3:$C$10,MATCH(E3,$A$3:$A$10,0)),"")