Search code examples
excelexcel-formulaoffice365powerquerydynamic-arrays

Excel UNIQUE Across Columns

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

Solution

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