CODEWITHSUNDEEP
excelexcel-formula
sstecht
sstecht

Reputation: 41

Transpose single column to array of unique rows

I'm trying to get a list of values that contain duplicates into unique rows.

Column A
Cell 1
Cell 2
Cell 3
Cell 1
Cell 2
Cell 3
Cell 1
Cell 2
Column A Column B Column C
Cell 1 Cell 1 Cell 1
Cell 2 Cell 2 Cell 2
Cell 3 Cell 3

I've tried using

=TRANSPOSE(UNIQUE(A1:A3,TRUE,TRUE))

But any combination of the formula removes the duplicates and I want to maintain them.

Upvotes: 0

Views: 138

Answers (2)

JvdV
JvdV

Reputation: 75960

Alternatively, you can try:

enter image description here

Formula in C1:

=IFERROR(DROP(REDUCE(0,UNIQUE(TOCOL(A:A,1)),LAMBDA(x,y,VSTACK(x,EXPAND(y,1,COUNTIF(A:A,y),y)))),1),"")

Or, with a build-in sort function:

=SORT(IFERROR(DROP(REDUCE(0,UNIQUE(TOCOL(A:A,1)),LAMBDA(x,y,VSTACK(x,EXPAND(y,1,COUNTIF(A:A,y),y)))),1),""),1)

Upvotes: 3

Scott Craner
Scott Craner

Reputation: 152660

with MakeArray:

=LET(
    rng,A1:A8,
    u,UNIQUE(rng),
    MAKEARRAY(
        ROWS(u),
        MAX(COUNTIF(A1:A8,u)),
        LAMBDA(a,b,
            IF(COUNTIF(A1:A8,INDEX(SORTBY(u,COUNTIF(A1:A8,u),-1),a))>=b,
                INDEX(SORTBY(u,COUNTIF(A1:A8,u),-1),a),
                ""))))

enter image description here

This will expand automatically to any number. It will also put the ones with the most duplicates at the top so it is an inverted pyramid:

enter image description here

Upvotes: 4

Related Questions