Excel Unique Function

[divyanand]

Hi All,
I am working in an automobile manufacturing company which manufactures thousands of Engines.
For example, let us consider 3 different types of engine (say Engine 1, 2,3).
Each engine is made from different parts (say a,b,c,d,e).
I need the list of all parts used in all Engines in the worksheet in a single column (as shown in Column H).
I tried using the Unique function, it is not working.

looks very simple,
can some on please help ?

 

[ISY]

Hi

=SORT(UNIQUE(FILTERXML("<A><B>"&TEXTJOIN("</B><B>";;A2:C5)&"</B></A>";"//B")))

 

[Fluff]

Roughly how many cells do you need to look at?

 

[Fluff]

If you have thousands of cells, try

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H
1	Engine 1	Engine 2	Engine 3	Engine 4	Engine 5	Engine 6		Output
2	F	M	L	L	Q	X		F
3	K	J	I	B	P			K
4	N		S	E	U			N
5	U		C		V			U
6	S			C	E			S
7								M
8								J
9								L
10								I
11								C
12								B
13								E
14								Q
15								P
16								V
17								X
18
Main

Cell Formulas
Range	Formula
H2:H17	H2	=LET(Ary,A2:F6,Rws,ROWS(Ary),Seq,SEQUENCE(Rws*COLUMNS(Ary),,0),Idx,INDEX(Ary,MOD(Seq,Rws)+1,INT(Seq/Rws)+1),UNIQUE(FILTER(Idx,Idx<>"")))
Dynamic array formulas.

 

[divyanand]

If you have thousands of cells, try

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H
1	Engine 1	Engine 2	Engine 3	Engine 4	Engine 5	Engine 6		Output
2	F	M	L	L	Q	X		F
3	K	J	I	B	P			K
4	N		S	E	U			N
5	U		C		V			U
6	S			C	E			S
7								M
8								J
9								L
10								I
11								C
12								B
13								E
14								Q
15								P
16								V
17								X
18
Main

Cell Formulas
Range	Formula
H2:H17	H2	=LET(Ary,A2:F6,Rws,ROWS(Ary),Seq,SEQUENCE(Rws*COLUMNS(Ary),,0),Idx,INDEX(Ary,MOD(Seq,Rws)+1,INT(Seq/Rws)+1),UNIQUE(FILTER(Idx,Idx<>"")))
Dynamic array formulas.

Awesome
Thank you

 

[Fluff]

Glad we could help & thanks for the feedback.