Transpose single column to array of unique rows-CodePudding

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.

CodePudding user response：

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),
                ""))))

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:

CodePudding user response：

Alternatively, you can try:

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)