Create Dynamic Unique List from multiple ranges

[GuyGadois]

I am trying to make one dynamic unique list from multiple ranges (sorted alphabetically).

The code I currently use works fine for one range

=IFERROR(INDEX($B$5:$B$170, MATCH(0,COUNTIF(W$4:$W4, $B$5:$B$170), 0)),"")

The originating range is located at $B$5:$B$170 and the dynamic result starts at W5. The need is to include the values from another range and create one dynamic list at W5. The other range is located at $G$5:$G$170. Between the list at $B$5:$B$170 and $G$5:$G$170 I would like to have one dynamic unique list created.

Does anyone know a solution for this?

Cheers,

GG

 

[Eric W]

Try this in W5 and drag down:

=IFERROR(IFERROR(INDEX($B:$B,AGGREGATE(15,6,ROW($B$5:$B$170)/(COUNTIF($W$4:$W4,$B$5:$B$170)=0)/($B$5:$B$170<>""),1)),INDEX($G:$G,AGGREGATE(15,6,ROW($G$5:$G$170)/(COUNTIF($W$4:$W4,$G$5:$G$170)=0)/($G$5:$G$170<>""),1))),"")

 

[GuyGadois]

Perfect, thanks Eric. That is a complex formula!

 

[Eric W]

Happy to help!

It's really not terribly complicated, it really just contains a medium complicated formula two times, once for each column. But admittedly, you would probably not stumble on that formula by yourself (I didn't!).