Return unique values based on multiple conditions and sorted by date

emoandy05

Board Regular
Joined
Sep 4, 2013
Messages
60
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
NameStart DateEnd 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
NameEnd Date
555​
5-Dec​
444​
10-Dec​
111​
20-Dec​
222​
26-Dec​
 

Some videos you may like

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

DRSteele

Well-known Member
Joined
Mar 31, 2015
Messages
2,236
Office Version
365
Platform
Windows
That's easy with the new functions in Excel 365 Insider, UNIQUE and SORT.
Which version of Excel are you using?
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
9,750
Without the new functions, you can do it like this:

Book1
ABCDEF
1Orig DataResult
2NameStart DateEnd DateNameEnd Date
333311/20/201911/24/2019
555
12/5/2019
433311/20/201911/24/2019
444
12/10/2019
544411/25/201912/10/2019
111
12/20/2019
644411/25/201912/10/2019
222
12/26/2019
755511/24/201912/5/2019
 
 
811111/20/201912/20/2019
 
 
911111/20/201912/20/2019
 
 
1022211/26/201912/26/2019
11
Sheet3
Cell Formulas
RangeFormula
E3:E9E3=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:F9F3=IF(E3<>"",INDEX($C$3:$C$10,MATCH(E3,$A$3:$A$10,0)),"")
 

Watch MrExcel Video

Forum statistics

Threads
1,099,064
Messages
5,466,380
Members
406,478
Latest member
Amar kumar

This Week's Hot Topics

Top