Transpose single column to array of unique rows

Question

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.

Scott Craner · Accepted Answer · 2023-01-18T23:40:04.933

4

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:

edited Jan 18 '23 at 23:40

answered Jan 18 '23 at 23:25

Scott Craner

148,073
10
49
81

2

Scott Craner is a steely-eyed missile man. – Enigmativity Jan 19 '23 at 00:48
Thank you.. This was precisely what I was looking for. I am digging into every element here.. Great stuff! – sstecht Jan 19 '23 at 20:41

JvdV · Answer 2 · 2023-01-19T08:32:24.400

3

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)

edited Jan 19 '23 at 08:32

answered Jan 19 '23 at 08:21

JvdV

70,606
8
39
70

3

JvdV is a steely-eyed missile man. – Enigmativity Jan 19 '23 at 11:36

Transpose single column to array of unique rows

2 Answers2

Linked