excelexcel-formula

Filter and Sort by Date, in single formula


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!.


Solution

  • Try using the following:

    enter image description here

    =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)