Extract Unique Value List from a range - Multiple columns to 1 list

[willow1985]

I am hoping someone could help me with a Unique formula that makes a unique list from a range of cells (multiple columns)

Below is an example of what I am looking for however the actual data may be 30 columns and roughly 200 rows (and the columns may not be all beside each other)

Thank you very much to anyone who can help!

Change Request.xlsm
	A	B	C	D	E	F
1	1	2	3	4		Result
2	Blue	Yellow	Red	Pink		Blue
3	Red	Yellow	Purple	Blue		Green
4	Yellow	Green	Red	Blue		Pink
5						Purple
6						Red
7						Yellow
Sheet2

 

[lrobbo314]

ATG.xlsx
	A	B	C	D	E	F	G
1	1	2	3	4		Result	Result
2	Blue	Yellow	Red	Pink		Blue	Blue
3	Red	Yellow	Purple	Blue		Green	Green
4	Yellow	Green	Red	Blue		Pink	Pink
5						Purple	Purple
6						Red	Red
7						Yellow	Yellow
Sheet1

Cell Formulas
Range	Formula
G2:G7	G2	=LET(tbl,A2:D4,c,COLUMNS(tbl),seq,SEQUENCE(ROWS(tbl)*c,,0),SORT(UNIQUE(INDEX(tbl,INT(seq/c)+1,MOD(seq,c)+1))))
Dynamic array formulas.

 

[Eric W]

Check out this thread:

Extract Unique Value from List More Than 1 Million

Hi, I have a few sheets with a long list of duplicate values and the total list is more than 2.5 million. Example is 5 sheets and each sheets have about 500,000 of values. I need to produce a final list of all 5 sheets unique values , meaning there should not be any duplicates with the total...

www.mrexcel.com

 

[willow1985]

ATG.xlsx
	A	B	C	D	E	F	G
1	1	2	3	4		Result	Result
2	Blue	Yellow	Red	Pink		Blue	Blue
3	Red	Yellow	Purple	Blue		Green	Green
4	Yellow	Green	Red	Blue		Pink	Pink
5						Purple	Purple
6						Red	Red
7						Yellow	Yellow
Sheet1

Cell Formulas
Range	Formula
G2:G7	G2	=LET(tbl,A2:D4,c,COLUMNS(tbl),seq,SEQUENCE(ROWS(tbl)*c,,0),SORT(UNIQUE(INDEX(tbl,INT(seq/c)+1,MOD(seq,c)+1))))
Dynamic array formulas.

Thank you very much! Works perfectly ? ? ?

 

[Eric W]

Just FYI, lrobbo314's formula assumes all the columns are adjacent, and have the same number of rows. The link I posted has columns on different sheets.

 

[willow1985]

Just FYI, lrobbo314's formula assumes all the columns are adjacent, and have the same number of rows. The link I posted has columns on different sheets.

Thank you very much, I made it work with everything adjacent but made a note of the other formula.