12

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)

enter image description here

urdearboy
  • 14,439
  • 5
  • 28
  • 58
  • Can you un-accept the accepted answer? I believe there are better answers than `FILTERXML`, especially with the introduction of `TOCOL`. – BigBen Mar 23 '22 at 17:27
  • 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 – urdearboy Mar 23 '22 at 19:33
  • Well, I guess it is up to you. I just consider my answer inferior. – BigBen Mar 23 '22 at 19:35

5 Answers5

15

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")))

enter image description here

BigBen
  • 46,229
  • 7
  • 24
  • 40
  • 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 `UNIQUE`s use - awesome solution as always – urdearboy Jun 04 '20 at 22:45
  • It works ok for small ranges, but if you got a few hundred cells it will break. Thanks for the info. – Antonio Luna Aug 29 '21 at 04:17
  • @AntonioLuna - yes, TEXTJOIN has a character limit so you might consider other answers in this thread that don't use it. – BigBen Aug 29 '21 at 12:09
10

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:

enter image description here

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:

enter image description here

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"
JvdV
  • 70,606
  • 8
  • 39
  • 70
  • The introduction of `TOCOL` means this should be the accepted answer, IMHO. – BigBen Mar 23 '22 at 17:26
  • 1
    @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. – JvdV Mar 24 '22 at 09:22
8

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.

enter image description here

Scott Craner
  • 148,073
  • 10
  • 49
  • 81
  • @BigBen 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. – Scott Craner Jul 14 '20 at 20:05
  • Yeah that is a definite downside of the original approach. – BigBen Jul 14 '20 at 20:05
  • 2
    `LET()` could be a nice addition to this answer (if one has got access to the function as yet) =) – JvdV Feb 25 '21 at 12:01
5

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

enter image description here

Harun24hr
  • 30,391
  • 4
  • 21
  • 36
1

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)).

Cristian Buse
  • 4,020
  • 1
  • 13
  • 34
  • @urdearboy 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). – Cristian Buse May 24 '21 at 16:06