Combine multiple columns into one Unique List - Excel

In this video, demonstrate how to combine or append multiple columns into one column using formulas and the Unique function. Unique function easily works with single rows or columns, but with multiple columns requires some creativity

Workbook can be found here:

[AOC]

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

[glenmitchell]

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.

[meniporat]

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)

[wayneedmondson]

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

[nw]

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

[an.cr.]

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

[JahanzaibChaudhary-yh]

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.

[heishoi]

wow ! this is amazing, you are incredible 👍 👍

[hasibahsan]

Greetings from my heartcore. Nice to have such video. Please let me know how I can do the same in excel file. Looking forward to hearing from you. Thanks in advance.

[farb]

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

[maiab]

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?

[maku]

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

[KietNguyen-gqml]

Exactly what I needed to do! Thanks a lot!

[farb]

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

[TheKyaiMusic]

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?

[OmisileKehindeOlugbenga]

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

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

[willm]

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

[dandeman]

If Range is over 20 i.e. C5:F24, Last Value is #Ref

[meniporat]

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

[olliracc]

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