Search code examples
arraysexcelfilterexcel-formuladynamic

Creating a Multi-Step FILTER Dynamic Array Function in Excel

I have a spreadsheet with five January entries and five February entries.

Column A: Date Column B: Sales Person's Name Column C: Sales Person's Sales Amount for the month

Date Sales Person's Name Sales Person's Sales Amount
1/31/2023 Ann 500
1/31/2023 Bill 200
1/31/2023 Tim 100
1/31/2023 Lee 400
1/31/2023 Bob 300
2/28/2023 Ann 40
2/28/2023 Bill 20
2/28/2023 Tim 10
2/28/2023 Lee 30
2/28/2023 Bob 50

Desired Result (not sorted in any particular order): Bob Ann Lee

I would like to display the three people with the highest sales for the most recent month with the expectation that I would be appending new month's data as it becomes available.

I tried using the below FILTER Dynamic Array formula but noticed that my criteria are mutually exclusive since the three largest values are in January but I am forcing it to look only at February data.

=FILTER(B2:B100,A2:A100=MAX(A2:A100)*(C2:C100=LARGE(C2:C100,3)))

I believe that I need to perform a multi-step FILTER but am unsure if this is possible in a single equation. The first step would for the FILTER to include only the most recent month, and the second step would be to filter on the three largest sales figures.

Are there any suggestions on a non-VBA solution?

Solution

  • Try using the following formula:

    enter image description here

    • Formula used in cell E2

    =CHOOSECOLS(TAKE(SORT(Salestbl,{1,3},{-1,-1}),3),2)
    • The above formula first sorts the date in descending order,
    • Next, it sorts the sales price in the same above order,
    • And takes the first 3 people with the highest sales.
    • lastly extracting only the names of the sales person.

    Test Case:

    enter image description here

    Or, do it as commented by JvdV Sir.

    enter image description here

    • Formula used in cell E2

    =TAKE(SORTBY(Salestbl[Sales Person''s Name],Salestbl[Date],-1,Salestbl[Sales Person''s Sales Amount],-1),3)

    The above formula is basically as given by Sir

    =TAKE(SORTBY(B2:B11,A2:A11,-1,C2:C11,-1),3)

    I have used Structured References aka Tables so when you add the new data it picks up the data ranges automatically.