In Excel, I have a table that I'm making a summary table for.
My Main data table has lots of client names and dates. I can't really change the order of the main data table, but where there's a value in column C, I want to return the Client Name but in the order of their effective date. I just check Column C (Checked?
below) if there's a value, and if so, grab that client name.
Data:
Client Name Effective Date Checked?
Client A 01/01/20 Yes
Client B 05/01/20 Yes
Client C 01/01/19 Yes
Client D 01/01/25
So I can get the names of clients I have checked: =FILTER(A2:A5,C2:C5<>"","")
which returns (SPILL):
Client A
Client B
Client C
Is there a way to return that list but using the Effective Date? So then the order would be:
Client C
Client A
Client B
I am aware that I can get all my data, put in a table, then sort by the date. However, I'm just looking for a quick way to see if I can sort these by Date, without any extra steps. (Don't have to use FILTER()
if another option exists).
I tried:
=SORT(FILTER(A2:A5,C2:C5<>"",""),B2:B5,1,FALSE)
but it just returns #VALUE!
.
Try using the following:
=DROP(SORT(FILTER(A2:B5,C2:C5<>"",""),2),,-1)
Or,
=CHOOSECOLS(SORT(FILTER(A2:B5,C2:C5<>"",""),2),1)
Or,
=INDEX(SORT(FILTER(A2:B5,C2:C5<>"",""),2),,1)