Forum Discussion

ligg_1's avatar
ligg_1
Copper Contributor
Feb 02, 2022
Solved

Sortby combined with Filter and Index Functions

Hello -

 

Is possible to use the SORTBY function on the Communication Details table to sort by "next communication date" and then "next communication responsible party" (or at a minimum "next communication date")? I am not able to figure this out.

 

https://docs.google.com/spreadsheets/d/1CSFQv6ht2AjxAIKM1NEn4Vy-tR2RTbLm/edit?usp=sharing&ouid=110429419544736917162&rtpof=true&sd=true is the link to the document on Google docs. I believe you will need to download it to access the formulas. 

 

 

 

Thank you! 

 

excel
Formulas and Functions
  • Lorenzo's avatar
    Lorenzo
    Feb 02, 2022

    Hi ligg_1 

    Much easier with a sample => Thanks. On 'Summary Dashboard' sheet, to sort the array in H9# by column2 (Next Communication Date) then by column4 (Next Communication Responsible Party), formula should be:

    =LET(
    arr, FILTER(
            INDEX(StakeholderCommPlan, SEQUENCE(ROWS(StakeholderCommPlan)), MATCH(H8:K8,StakeholderCommPlan[#Headers],0)),
            StakeholderCommPlan[IDN]='Summary Dashboard'!C4,
            "No Upcoming Communication Dates"
    ),
    SORTBY(arr, INDEX(arr,,2),1, INDEX(arr,,4),1)
)

    Given you use other dynamic array functions I would suggest you replace MATCH with XMATCH

3 Replies

  • Lorenzo's avatar
    Lorenzo
    Silver Contributor
    Feb 02, 2022

    Hi ligg_1 

    Much easier with a sample => Thanks. On 'Summary Dashboard' sheet, to sort the array in H9# by column2 (Next Communication Date) then by column4 (Next Communication Responsible Party), formula should be:

    =LET(
    arr, FILTER(
            INDEX(StakeholderCommPlan, SEQUENCE(ROWS(StakeholderCommPlan)), MATCH(H8:K8,StakeholderCommPlan[#Headers],0)),
            StakeholderCommPlan[IDN]='Summary Dashboard'!C4,
            "No Upcoming Communication Dates"
    ),
    SORTBY(arr, INDEX(arr,,2),1, INDEX(arr,,4),1)
)

    Given you use other dynamic array functions I would suggest you replace MATCH with XMATCH