Search code examples
excelexcel-formulaoffice365array-formulasdynamic-arrays

Using Office365 excel array formulas, how to remove duplicates, keeping the last value?

With data in A1 - B5:

A 1  //remove
A 2
B 3  //remove
B 2
C 1

How do you remove duplicates in column A, keeping the last set of values in other columns? Results should look like this:

A 2
B 2
C 1

I've tried combinations of Filter, Unique, and xlookup but haven't found an approach that works yet.

Solution

  • There are many possibilities of doing this, I assume these are two more methods which one can apply, although I have not made any speed test yet.

    enter image description here

    Method One:

    =LET(α, A1:A5, δ, UNIQUE(α), HSTACK(δ, LOOKUP(δ,α,B1:B5)))

    Method Two:

    =LET(α, A1:A5, δ, UNIQUE(α), HSTACK(δ, XLOOKUP(δ,α,B1:B5,,,-1)))

    Method Three: (using a reverse binary xlookup for speed)

    =LET(d,SORT(A1:B5,,-1),  a,CHOOSECOLS(d,1),  b,CHOOSECOLS(d,2),  u,SORT(UNIQUE(a)),
    HSTACK(u,XLOOKUP(u,a,b,"",0,-2))  )