Question

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.

 2  90  2
1 Jan 1970

Solution

 2

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)

2024-07-20
Mayukh Bhattacharya

Solution

 0

If it would be sorted this would work:

=FILTER(A2:D11,MAP(A2:A11,B2:B11,LAMBDA(a,b,SUM((A2:a=a)*(B2:b=b))=1)))

MAP loops through range A2:A11 as a and likewise for range B2:B11 as b if the sum of A2 up to current row of a and B2 up to current row of b is 1 (first occurrence of the combination of the 2) it returns TRUE else FALSE. This is used to filter the row's of range A2:D11

Else you can't make use of range reference, but can stimulate this using sequence:

=LET(s,SORT(A2:D11,4,-1),FILTER(s,MAP(SEQUENCE(ROWS(s)),LAMBDA(m,SUM((INDEX(s,SEQUENCE(m),1)=INDEX(s,m,1))*(INDEX(s,SEQUENCE(m),2)=INDEX(s,m,2)))=1))))

2024-07-20
P.b

Solution

 0

So, did this using the unique() function:

enter image description here

The output matches the results shown by you and others.

2024-07-20
Solar Mike

Solution

 0

MAXIFS

Select the most recent (MAXIF(date_…), record for name and process, and if multiple records are found for the date, select the one with the highest order number (MAXIF(order_num,…):

=LET(
    header, A1:D1,
    data, A2:D11,
    name, INDEX(data, , 1),
    process, INDEX(data, , 2),
    order_num, INDEX(data, , 3),
    date_, INDEX(data, , 4),
    VSTACK(
        header,
        FILTER(
            HSTACK(
                name,
                process,
                order_num,
                TEXT(date_, "m/d/yyyy")
            ),
            (
                date_ =
                    MAXIFS(date_, name, name, process, process)
            ) *
                (
                    order_num =
                        MAXIFS(
                            order_num,
                            name, name,
                            process, process,
                            date_, date_
                        )
                )
        )
    )
)

Formula and result

2024-07-20
nkalvi