Search code examples
excelmultidimensional-arrayexcel-formulaoffice365unique

Using Excel O365, How to use unique on two columns, sort, and copy over related data?

I have a 4 column, dynamic row array with names, process, order #, and dates. The array is dynamic and being collected using FILTER() function and grabbing the 4 columns I want from a table on a different sheet.

=FILTER(WelderQualifications[[Employee Name]:[Date Performed]],
 {1,0,0,1,0,0,0,0,0,0,0,0,0,0,0,1,0,1})

I need a Non-VBA solution to find unique values using only the first two columns (ie. Person 1 GTAW and Person 1 MGTAW are collected but the older Person 1 GTAW date gets ignored.)

Also need it to grab the most recent record when selecting the unique data, maybe a preliminary sort?

Finally, I want to show all 4 columns of filtered data but I don't want the order number or date to be part of the UNIQUE() FILTER(). See image.

Sample data and desired filtered results

Name Processes Order # Date
Person 1 GTAW 438113 10/29/2022
Person 1 GTAW 159623 9/4/2020
Person 1 MGTAW 478734 8/13/2021
Person 2 GTAW 968533 4/22/2021
Person 2 GTAW 864934 3/6/2021
Person 2 VPPAW 841763 2/22/2022
Person 3 GTAW 916397 10/23/2022
Person 3 GTAW 573528 11/21/2023
Person 3 MGTAW 488440 9/1/2022
Person 3 GTAW 224110 6/18/2021

Outputs:

Name Processes Order # Date
Person 1 GTAW 438113 10/29/2022
Person 1 MGTAW 478734 8/13/2021
Person 2 GTAW 968533 4/22/2021
Person 2 VPPAW 841763 2/22/2022
Person 3 GTAW 573528 11/21/2023
Person 3 MGTAW 488440 9/1/2022

I can get the unique two columns to work but I'm not sure how to collect the related data I need.

This sorts the persons name and the process correctly but drops the other two columns obviously:

=UNIQUE(SORT(FILTER(WelderQualifications[[Employee Name]:[Date Performed]],
{1,0,0,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0}),1,1),FALSE,FALSE)

Name and process filtered

Name Processes
Person 1 GTAW
Person 1 MGTAW
Person 2 GTAW
Person 2 VPPAW
Person 3 GTAW
Person 3 MGTAW

I've dabbled with the LET() function and some SEQUENCE(), LAMBDA(), and a few other options but can't seem to get the arrays to filter properly.

Solution

  • Try using the following formula:

    enter image description here

    =LET(
     _Data, A2:D11,
     _Headers, TAKE(_Data,1),
     _Body, DROP(_Data,1),
     _Sort, SORT(_Body,{1,2,4},{1,1,-1}),
     _Uniq, UNIQUE(TAKE(_Sort,,2)),
     VSTACK(_Headers,CHOOSEROWS(_Sort,
     XMATCH(INDEX(_Uniq,,1)&"|"&INDEX(_Uniq,,2),
     INDEX(_Sort,,1)&"|"&INDEX(_Sort,,2)))))

    And if you need only the first two columns then:

    enter image description here

    =LET(
     _Data, A2:D11,
     _Headers, TAKE(_Data,1),
     _Body, DROP(_Data,1),
     _Sort, SORT(_Body,{1,2,4},{1,1,-1}),
     _Uniq, UNIQUE(TAKE(_Sort,,2)),
     VSTACK(TAKE(_Headers,,2),_Uniq))

    Also, could try:

    =FILTER(A2:B11,MAP(A2:A11,B2:B11,LAMBDA(α,δ,COUNTIFS(A2:α,α,B2:δ,δ)))=1)

    Or, more specifically :

    =TAKE(FILTER(A2:D11,MAP(INDEX(A2:D11,,1),INDEX(A2:D11,,2),
 LAMBDA(α,δ,COUNTIFS(A1:α,α,B1:δ,δ)))=1),,2)

    Or, without a LAMBDA() helper :

    =LET(
     _Data, A2:D11,
     _Body, SORT(DROP(_Data,1),{1,2,4},{1,1,-1}),
     CHOOSECOLS(VSTACK(TAKE(_Data,1),
     UNIQUE(CHOOSEROWS(_Body,XMATCH(INDEX(_Body,,1)&"|"&INDEX(_Body,,2),
     INDEX(_Body,,1)&"|"&INDEX(_Body,,2))))),{1,2}))

    If applicable, then can use GROUPBY() presently available in MS365 Office Insiders

    =LET(α,SORT(A2:D11,{1,2,4},{1,1,-1}),GROUPBY(TAKE(α,,2),TAKE(α,,-2),SINGLE,,0))

    Or,

    =GROUPBY(SORTBY(A2:B11,D2:D11,-1),SORT(C2:D11,2,-1),SINGLE,,0)