Excel UNIQUE Across Columns
Excel UNIQUE Across Columns
Excel UNIQUE Across Columns
Re: Excel UNIQUE Across Columns
**New Answer:**
Ms365's new array shaping functions will be useful:
```
=UNIQUE(TOCOL(A2:C7,3,1))
```
[`TOCOL()`](https://support.microsoft.com/en-us/office/tocol-function-22839d9b-0b55-4fc1-b4e6-2761f8f122ed) 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:
[](https://i.sstatic.net/ZD4OM.png)
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:
[](https://i.sstatic.net/nHCwi.png)
```
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(#"Sor
*(Réponse tronquée)*
Ms365's new array shaping functions will be useful:
```
=UNIQUE(TOCOL(A2:C7,3,1))
```
[`TOCOL()`](https://support.microsoft.com/en-us/office/tocol-function-22839d9b-0b55-4fc1-b4e6-2761f8f122ed) 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:
[](https://i.sstatic.net/ZD4OM.png)
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:
[](https://i.sstatic.net/nHCwi.png)
```
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(#"Sor
*(Réponse tronquée)*