Use =UNIQUE formula to pull unique values from several non-adjacent ranges.

[smking204]

I have seven different columns with data that I want the =UNIQUE formula to draw from. So far no combination of formulas or other manipulations that I've tried have allowed me to compile unique values from more than one range. In my "plan B" that I'm currently using, I have seven =UNIQUE formulas in one hidden column, and my final column draws from that hidden column, but I would much rather do it directly in one formula if it's possible. Thanks in advance!

 

[jasonb75]

The UNIQUE function only works with a continuous range. Your current 'Plan B' is the best alternative.

 

[Fluff]

Are your columns next to each other?

 

[Fluff]

Also do you have the LET function in your version of Xl?

 

[smking204]

No, they are non-adjacent columns. I have the =LET formula, yes.

 

[Fluff]

Ok, it's a bit convoluted & I don't fully understand it having pinched it from https://www.myonlinetraininghub.com/excel-forum/excel/unique-values-from-two-sources

+Fluff 1.xlsm
	W	X	Y	Z	AA	AB	AC	AD	AE
1	Broker1		Broker2		Broker3
2	John		John		David				John
3	Jim		Mary		Jill				Jim
4	Jill		Brian		John				Jill
5									Mary
6									Brian
7									David
8
Dynamic

Cell Formulas
Range	Formula
AE2:AE7	AE2	=UNIQUE(LET( Rngs,(W2:W5,Y2:Y5,AA2:AA5), Zones,AREAS(Rngs), Rws,ROWS(W2:W5), TotalRows,SEQUENCE(Zones*Rws), NumArea,CEILING.MATH(TotalRows,Rws)/Rws, Rw,TotalRows-(NumArea-1)*Rws, Ary,INDEX(Rngs,Rw,1,NumArea), FILTER(Ary,Ary<>"")))
Dynamic array formulas.

 

[smking204]

Wow, yeah. That's overwhelming for sure. I will spend some time parsing it, but I will leave this thread unanswered in case someone has something a little simpler. Thanks for this.

 

[Fluff]

Glad to help & thanks for the feedback.