Excel UNIQUE Across Columns

Question

Is it possible for the new function UNIQUE to be used across various columns & have the output spill into a single column?

---

Desired output is UNIQUE values in one single column based on all of the values present in Columns: A, B, & C (duplicates in red)

Tavy

Can you un-accept the accepted answer? I believe there are better answers than FILTERXML, especially with the introduction of TOCOL. – Tsarina

I definitely can, but, should I? Your answer (@BigBen) solved my actual issue in a file being used at work in production (it still is so kudos to you) so that's why I accepted it but was stoked to see other answer come in that others may like better. I definitely liked to TOCOL but I don't have access to that formula so couldn't use it – Tavy

Well, I guess it is up to you. I just consider my answer inferior. – Tsarina

Answer 1

New Answer:

Ms365's new array shaping functions will be useful:

=UNIQUE(TOCOL(A2:C7,3,1))

TOCOL() would return a vector of all values other than error or empty (3) values per column (1).

---

Old Answer:

Using Microsoft365 with access to LET(), you could use:

Formula in E2:

=LET(X,A2:C7,Y,SEQUENCE(ROWS(X)*COLUMNS(X)),Z,INDEX(IF(X="","",X),1+MOD(Y,ROWS(X)),ROUNDUP(Y/ROWS(X),0)),SORT(UNIQUE(FILTER(Z,Z<>""))))

This way, the formula becomes easily re-usable since the only parameter we have to change is the reference to "X".

---

For what it's worth, it could also be done through PowerQuery A.K.A. Get&Transform, available from Excel2013 or a free add-in for Excel 2010.

• Select your data (including headers). Go to Ribbon > Data > "From Table/Range".
• Confirm that your data has headers and PowerQuery should open.
• In PowerQuery select all columns. Go to Transform > "Transpose".
• Select all columns again. Go to Transform > "Unpivot Columns".

The above will take care of empty values too. Now:

• Select the attributes column and remove it.
• Sort the remaining column and remove duplicates (right-click header > "Remove Duplictes").
• Close PowerQuery and save changes.

Resulting table:

M-Code:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"A Values", Int64.Type}, {"B Values", Int64.Type}, {"C Values", Int64.Type}}),
    #"Transposed Table" = Table.Transpose(#"Changed Type"),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Transposed Table", {}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns",{"Attribute"}),
    #"Sorted Rows" = Table.Sort(#"Removed Columns",{{"Value", Order.Ascending}}),
    #"Removed Duplicates" = Table.Distinct(#"Sorted Rows")
in
    #"Removed Duplicates"

Matzo

The introduction of TOCOL means this should be the accepted answer, IMHO. – Tsarina

@BigBen, It would be a pickle to go back to update any old answer. At the time the answer was given, yours would be perfectly fine (and still is for many users of Excel). No need for swapping here. My enthusiasm just let me to update some specific posts. – Matzo

Answer 2

There may be a better approach, but here is one using TEXTJOIN and FILTERXML to create an array that you can call UNIQUE on:

=SORT(UNIQUE(FILTERXML("<t><s>"&TEXTJOIN("</s><s>",TRUE,A1:C6)&"</s></t>","//s")))

Tsarina

I was playing around with TEXTJOIN as well but the FILTERXML did the trick. Was expecting this to be more straightforward but I suppose this is an edge case for UNIQUEs use - awesome solution as always – Tavy

It works ok for small ranges, but if you got a few hundred cells it will break. Thanks for the info. – Shiller

@AntonioLuna - yes, TEXTJOIN has a character limit so you might consider other answers in this thread that don't use it. – Tsarina

Answer 3

TEXTJOIN does have a character limit. We can overcome that with INDEX, SEQUENCE and FILTER:

=SORT(UNIQUE(FILTER(INDEX(A1:C6,MOD(SEQUENCE(COLUMNS(A1:C6)*ROWS(A1:C6),,0),MAX(ROW(A1:C6)))+1,INT(SEQUENCE(COLUMNS(A1:C6)*ROWS(A1:C6),,0)/(MAX(ROW(A1:C6))))+1),INDEX(A1:C6,MOD(SEQUENCE(COLUMNS(A1:C6)*ROWS(A1:C6),,0),MAX(ROW(A1:C6)))+1,INT(SEQUENCE(COLUMNS(A1:C6)*ROWS(A1:C6),,0)/(MAX(ROW(A1:C6))))+1)&""<>"")))

The INDEX creates a vertical array that can be passed first to FILTER to remove the blanks and then to UNIQUE.

Albeit, this is more complicated it does not have a character limit.

Shelbyshelden

@Tsarina yeah, I did not see the need till I just closed one as a dup to this one and realized with text, TEXTJOIN would hit the limit fairly quickly. – Shelbyshelden

Yeah that is a definite downside of the original approach. – Tsarina

LET() could be a nice addition to this answer (if one has got access to the function as yet) =) – Matzo

Answer 4

I am providing answer to this question as it is marked as duplicate to this thread. You can get unique values directly from FILTERXML() formula without having UNIQUE function. So, you can use this function to non O365 excels just having TEXTJOIN() and FILTERXML() function Ex: Excel2019.

FILTERXML() may give your desired result in best way. Try below formula-

=FILTERXML("<t><s>"&TEXTJOIN("</s><s>",TRUE,B4:D10)&"</s></t>","//s[not(preceding::*=.)]")

• Textjoin() with delimeter </s><s> will concatenate all the non empty celles in specified range to construct a valid XML string.
• "<t><s>"&TEXTJOIN("</s><s>",TRUE,B4:D10)&"</s></t>" will construct XML string to process by FILTERXML() formula.
• XPath parameter //s will return all nodes where [not(preceding::*=.)] will filter only unique nodes.
• You can adjust range B4:D10 for future data entry so that whenever you enter any text it will automatically appear in resulting column.

A diagnostic article on FILTERXML() by JvdV here Excel - Extract substring(s) from string using FILTERXML

Firman

Answer 5

You can use the DM_ARRAY function from my repository: VBA-ArrayTools. There is a demo workbook available and you will find a dedicated worksheet for each User Defined Function (UDF). For DM_ARRAY, the first parameter is the number of columns you want as output and then you can pass as many ranges as you need. In your case you could achieve the desired result with =UNIQUE(DM_ARRAY(1,A2:A6,B5:B10,C2:C5)).

Maggee

@Tavy I understand but I think you missed a detail. There is a folder called Code Modules at the link provided where the source code is in text files. The Workbook is simply an optional demo for convenience. Also, I cannot just share the source code for that function only because it uses a lot of utility functions. Hence, I prefer to link to the whole of the source code. There are 2 main modules: one with UDFs and one with pure VBA functionality that will work in any other host Application (like Word). – Maggee