Combine multiple columns into one Unique List - Excel

Amazing - learned three new functions in one short video. Cheers!

Brother you did well, but try this with TOCOL function, believe me you'll be amazed. This is way to complex and is not a dynamic formula but TOCOL is a dynamic one. May you be blessed.

forgot to mention, JOBS represents the range in my case, Omisile I just saw you got the same one

I just found this one, I don't know what it does but it works =UNIQUE(FILTERXML("<a><b>"&TEXTJOIN("</b><b>",TRUE,JOBS)&"</b></a>","//b")), Mani I tried VSTACK, but it left some empty cells in my list, is there any formula to remove the empty cell in this list that I need to add. Thank you all 😀

Thank you very much, it works perfect! but, in my range, I have 7 columns which represent the seven days of one week, and 30 rows, what if there are empty cells in the columns, and one column (Sunday) is usually entirely empty, I selected my range but the results are incomplete and with empty rows, I know it may be too much to ask but it would be grat to find a way to solve it. Thank you, again

You, sir, have just saved me a ton of work!! Thank you, this is awesome

Thank you very much. I've been having trouble with this.

what if the colums are on different sheets? can i still use this? tia

Thanks!

This is exactly what I’ve been looking for, however I need to do it on Google sheets and i can only get this to work on Excel. Do you know how I can do this in Google sheets?

I found this on google sheet: =unique({C5:C14,D5:D14,E5:E14,F5:F14})
I suppose it works on Excel too.

An even better solution than VSTACK:
=UNIQUE(TOCOL(C5:F14)
😎😎😎

This is something I've wanted to be able to do for years so thanks so much for posting. Took me a bit of thought to realise that the divisor for each of the functions should always be the number of rows in your table - would have been worth mentioning that to make it a completely generic solution.

with the new VSTACK function everything is much simpler:
you can achieve the same result thus:
=VSTACK(C5:C14,D5:D14,E5:E14,F5:F14)

Hi Victor
Great Video can you please attach the files so that we can follow along
regards
😃

Hello, i am having a problem with FILTERXML formula. It works for small data, but seems to give a calc error on big data.

Apparently TEXTJOIN is too long. Do you know a way this would work with bigger datasets? Maybe an alternative for TEXTJOIN?

Exactly what I needed to do! Thanks a lot!

wow ! this is amazing , you are incredible 👍 👍

Hi Victor. An awesome way to start the new year! Thanks for sharing this technique. Thumbs up!!
Note - for anyone interested in a solution with a VBA user defined function, this will work
Function StackRangeByColumn(rng As Range) As Variant
Dim r As Long, c As Long, n As Long
r = rng.Rows.Count
c = rng.Columns.Count
n = rng.Cells.Count
Dim arr As Variant
ReDim arr(1 To n, 1 To 1)
Dim i As Long, j As Long
n = 1
For i = 1 To c
For j = 1 To r
arr(n, 1) = rng.Cells(j, i).Value
n = n + 1
Next j
Next i
StackRangeByColumn = arr
End Function

This formula would do same using the FILTERXML() function:

=UNIQUE(FILTERXML("<z><m>"&TEXTJOIN("</m><m>",,C5:F14)&"</m></z>","//m"))