unique list from range

[uk747]

Hi
Trying to get a unique list from a range of cells in Columns A:D below

Normally I use =UNIQUE(A2:A10) but not sue how to use for a2:D20 especially if columns have different numbers of names

alan	amy	tom	bob
john	mary	jeff	mary
bob	keith	keith	alan
tom	bill	tom	mary
	bob	fred	ian
	mavis	jack	john
	keith		mary
	fred

End result should be

alan

amy

bill

bob

fred

ian

jack

jeff

john

keith

mary

mavis

tom

 

[Fluff]

How about

+Fluff 1.xlsm
	A	B	C	D
1
2	alan	amy	tom	bob
3	john	mary	jeff	mary
4	bob	keith	keith	alan
5	tom	bill	tom	mary
6		bob	fred	ian
7		mavis	jack	john
8		keith		mary
9		fred
10
11
12
13
14	alan
15	amy
16	bill
17	bob
18	fred
19	ian
20	jack
21	jeff
22	john
23	keith
24	mary
25	mavis
26	tom
27
Data

Cell Formulas
Range	Formula
A14:A26	A14	=LET(Data,A2:D10,r,ROWS(Data),s,SEQUENCE(r*COLUMNS(Data),,0),I,INDEX(Data,MOD(s,r)+1,INT(s/r)+1),SORT(UNIQUE(FILTER(I,I<>""))))
Dynamic array formulas.

 

[uk747]

thanks, but unfortunately don't have LET formula yet

 

[Fluff]

Not a problem, it just makes the formula a bit longer.

=SORT(UNIQUE(FILTER(INDEX(A2:D10,MOD(SEQUENCE(ROWS(A2:D10)*COLUMNS(A2:D10),,0),ROWS(A2:D10))+1,INT(SEQUENCE(ROWS(A2:D10)*COLUMNS(A2:D10),,0)/ROWS(A2:D10))+1),INDEX(A2:D10,MOD(SEQUENCE(ROWS(A2:D10)*COLUMNS(A2:D10),,0),ROWS(A2:D10))+1,INT(SEQUENCE(ROWS(A2:D10)*COLUMNS(A2:D10),,0)/ROWS(A2:D10))+1)<>"")))

 

[uk747]

Thanks

 

[Fluff]

You're welcome & thanks for the feedback.