Help with getting all unique values from 4 columns

[Styx]

I have 4 columns with all kinds of values with a lot of duplicates.
With help of a matrix formula I want to have 1 column with all unique values.

One column is not a problem:
={ALS.FOUT(INDEX(B$7:B$19;VERGELIJKEN(ONWAAR;ISGETAL(VERGELIJKEN(B$7:B$19;$H$6:H6;0));0));"")}
How to do this with 4 columns (and yes, column D is always empty)

Thanks in advance!

 

[etaf]

are you still using version 2007 excel
if using a later version 365
=UNIQUE(VSTACK(B3:B11,C3:C11,E3:E11,F3:F11))

Book8
	A	B	C	D	E	F	G	H
1
2
3		1	3		101	99		1
4		1	4		102	98		2
5		2	5		103	99		4
6		2	2		104	99		7
7		1	4		101	98		9
8		4	5		102	99		3
9		2	2		103	99		5
10		7	4		104	98		101
11		9	5		22	99		102
12								103
13								104
14								22
15								99
16								98
Sheet1

Cell Formulas
Range	Formula
F3,F11,F8:F9,F5:F6	F3	=99
H3:H16	H3	=UNIQUE(VSTACK(B3:B11,C3:C11,E3:E11,F3:F11))
Dynamic array formulas.

OR
=SORT(UNIQUE(VSTACK(B3:B11,C3:C11,E3:E11,F3:F11)))
to sort in order

 

[Styx]

are you still using version 2007 excel
if using a later version 365
=UNIQUE(VSTACK(B3:B11,C3:C11,E3:E11,F3:F11))

Book8
	A	B	C	D	E	F	G	H
1
2
3		1	3		101	99		1
4		1	4		102	98		2
5		2	5		103	99		4
6		2	2		104	99		7
7		1	4		101	98		9
8		4	5		102	99		3
9		2	2		103	99		5
10		7	4		104	98		101
11		9	5		22	99		102
12								103
13								104
14								22
15								99
16								98
Sheet1

Cell Formulas
Range	Formula
F3,F11,F8:F9,F5:F6	F3	=99
H3:H16	H3	=UNIQUE(VSTACK(B3:B11,C3:C11,E3:E11,F3:F11))
Dynamic array formulas.

Hi etaf,

Thans for your answer!
I indeed use an older version of Excel, so that formula will not work for that (older) computer.
Nevertheless I'll take your solution in mind, but still need the 2007 solution

 

[etaf]

ok, sorry cant help with that , been a long while since i used 2007 ....

 

[AhoyNC]

See if the attached link helps. You may want to look at this option:

Extract unique values from multiple columns with Pivot Table​

How to extract unique values from multiple columns in Excel?

ExtendOffice provides a comprehensive range of professional software solutions, enhancing productivity and efficiency for businesses worldwide.

www.extendoffice.com