Unique in column

[mduntley]

I am trying to find a solution to my problem. I just got Excel 365 at work and trying to use the unique formula. I found a post to solve this, but I want to know what I need to do to add more columns in.

Unique Formula for 2 columns


This is my table. I was putting into notepad to remove the " so I will have it line by line.

LPR.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
1	Field 1	Field 2	Field 3	Field 4	Field 5	Field 6	Field 7	Field 8	Field 9	Field 10	Field 11	Field 12	Field 13	Field 14	Field 15	Field 16	Field 17	Field 18	Field 19	Field 20	Field 21	Field 22	LPR
2	100100210015 1640						100100210015 1258																100100210015 1640 100100210015 1258
3
4
5	Paste From notepad
6	100100210015 1640
7	100100210015 1258
8
9	Using the unique formula
10	Field 1	Field 2	Field 3	Field 4	Field 5	Field 6	Field 7	Field 8	Field 9	Field 10	Field 11	Field 12	Field 13	Field 14	Field 15	Field 16	Field 17	Field 18	Field 19	Field 20	Field 21	Field 22
11	100100210015 1640						100100210015 1258
12
13	What I want
14	100100210015 1640
15	100100210015 1258
Sheet3

Cell Formulas
Range	Formula
W2	W2	=SUBSTITUTE(SUBSTITUTE(TRIM(SUBSTITUTE(A2," ","|")&" "&SUBSTITUTE(B2," ","|")&" "&SUBSTITUTE(C2," ","|")&" "&SUBSTITUTE(D2," ","|")&" "&SUBSTITUTE(E2," ","|")&" "&SUBSTITUTE(F2," ","|")&" "&SUBSTITUTE(G2," ","|")&" "&SUBSTITUTE(H2," ","|")&" "&SUBSTITUTE(I2," ","|")&" "&SUBSTITUTE(J2," ","|")&" "&SUBSTITUTE(K2," ","|")&" "&SUBSTITUTE(L2," ","|")&" "&SUBSTITUTE(M2," ","|")&" "&SUBSTITUTE(N2," ","|")&" "&SUBSTITUTE(O2," ","|")&" "&SUBSTITUTE(P2," ","|")&" "&SUBSTITUTE(Q2," ","|")&" "&SUBSTITUTE(R2," ","|")&" "&SUBSTITUTE(S2," ","|")&" "&SUBSTITUTE(T2," ","|")&" "&SUBSTITUTE(U2," ","|")&" "&SUBSTITUTE(V2," ","|"))," ",CHAR(10)),"|"," ")
A10:V11	A10	=UNIQUE(A1:V2)
Dynamic array formulas.

 

[Fluff]

It's not entirely clear to me, what you are trying to do, as both parts of your sheet seem to be the same.

 

[mduntley]

Row 1 and 2 are is the real sheet.

Row 9 and 10 are the one when i try to use the unique formula

Row 13-15 is what i want.

 

[Fluff]

Ok, how about

=TRANSPOSE(UNIQUE(FILTER(A2:V2, A2:V2<>""),1))

 

[mduntley]

That is good! But i want A2:V2 to be A:V

 

[Fluff]

You mean the entire columns?

 

[mduntley]

Yes, the table i have is more then 1000 lines

 

[Fluff]

In that case try

=LET(Rng,A2:V1000,Rws,ROWS(Rng),Seq,SEQUENCE(Rws*COLUMNS(Rng),,0),Ary,INDEX(Rng,MOD(Seq,Rws)+1,INT(Seq/Rws)+1),UNIQUE(FILTER(Ary,Ary<>"")))

 

[mduntley]

Thank you!

 

[Fluff]

You're welcome & thanks for the feedback.