Create a unique list based on data in 150 rows and 50 columens

[Excelnoob77]

Hi

I have a area of 150 rows and 50 columens with same type of data in one table. this data a dynamic based on input in the first columens.

becaus i like to have one unique list of all the values in this 150x50 cells i need help.

Manuelly i can copy this collums and place them on top of one and other and then use remov dublicats, it if was just one colume or row the formula =unique can work but if i make it accross my area it just created 50 new columens...

i have created =IFERROR(LOOKUP(2, 1/(COUNTIF($F$2:F2,$B$3:$B$21)=0), $B$3:$B$21), LOOKUP(2, 1/(COUNTIF($F$2:F2, $D$3:$D$8)=0), $D$3:$D$8)) but it will get very long when i have 50 colums.

can i use pivot or power pivet or a formula..

I am not the great makro person, but if it is a simple makro i am open for that also

Can any one help

 

[Alex Blakenburg]

This is pretty easy to do using Power Query if that is of interest to you.

Do you have empty columns between the names columns ?
Do you know how to use Tables and give a table a name ?

 

[Peter_SSs]

Welcome to the MrExcel board!

the formula =unique can work

This indicates that you might have Microsoft 365 but your profile shows otherwise. Can you clarify that and update your profile if required?

 

[Excelnoob77]

Welcome to the MrExcel board!


This indicates that you might have Microsoft 365 but your profile shows otherwise. Can you clarify that and update your profile if required?

Yes you maybe rigth

Microsoft® Excel® til Microsoft 365 MSO (16.0.13929.20206) 32-bit

 

[Excelnoob77]

This is pretty easy to do using Power Query if that is of interest to you.

Do you have empty columns between the names columns ?
Do you know how to use Tables and give a table a name ?

Hi

it is allready part of a table no empty collums between

 

[Alex Blakenburg]

It sounds like you may already have set it up as a table.

• If not and you have blank columns select the whole area. If you don't have any blank rows or columns you only need to select 1 cell.
• Hit Ctrl+T or Insert > Table
  Tick the box that My Table has headers
• It will be easier if you give it the Table Name tblNames
  (under Table Design > Table Name - far left names box)
• Click anywhere in the table and then go Data > From Table Range (far left)
• You will now be in the Power Query editor
• Under Home > Advanced Editor
  replace all the code you see there with the code below
• If you didn't use the Table name tblNames then replace where it says tblNames in the Line "Source" with your table name
  and hit OK
• On the right side change the query name to something that you like. It will create a table in Excel with the same name.
• Now Home > Close & Load
  

let
    Source = Excel.CurrentWorkbook(){[Name="tblNames"]}[Content],
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 1, 1, Int64.Type),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Added Index", {"Index"}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Other Columns",{"Index", "Attribute"}),
    #"Removed Duplicates" = Table.Distinct(#"Removed Columns"),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Duplicates",{{"Value", type text}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Value", "Names"}}),
    #"Sorted Rows" = Table.Sort(#"Renamed Columns",{{"Names", Order.Ascending}})
in
    #"Sorted Rows"

 

[Peter_SSs]

Microsoft 365

it is allready part of a table

Then might this small example (just a single formula in a single cell) be of any use?

Excelnoob77.xlsm
	C	D	E	F	G	H	I	J
1	Hdr1	Hdr2	Hdr3	Hdr4	Hdr5			Unique List
2	7	23	20	21	15			7
3	14	20	17	7	23			23
4	29	8	9	7	9			20
5	15	20	30	7	5			21
6	28	20	7	24	20			15
7								14
8								17
9								29
10								8
11								9
12								30
13								5
14								28
15								24
16
Unique

Cell Formulas
Range	Formula
J2:J15	J2	=UNIQUE(FILTERXML("<p><c>"&TEXTJOIN("</c><c>",1,Table1)&"</c></p>","//c"))
Dynamic array formulas.

 

[Excelnoob77]

Then might this small example (just a single formula in a single cell) be of any use?

Excelnoob77.xlsm
	C	D	E	F	G	H	I	J
1	Hdr1	Hdr2	Hdr3	Hdr4	Hdr5			Unique List
2	7	23	20	21	15			7
3	14	20	17	7	23			23
4	29	8	9	7	9			20
5	15	20	30	7	5			21
6	28	20	7	24	20			15
7								14
8								17
9								29
10								8
11								9
12								30
13								5
14								28
15								24
16
Unique

Cell Formulas
Range	Formula
J2:J15	J2	=UNIQUE(FILTERXML("<p><c>"&TEXTJOIN("</c><c>",1,Table1)&"</c></p>","//c"))
Dynamic array formulas.

Thanks it looks like it works but when i take alle 50 colums it comes with a error #value

i can get 10 colums then it fails, can it be there is a max?

 

[Peter_SSs]

when i take alle 50 colums it comes with a error #value

..., can it be there is a max

Ahh, yes. TEXTJOIN has a limit and that will be breached with that size range.

Perhaps this macro which will write the list to the right of the table.

Sub Unique_List()
  Dim d As Object
  Dim a As Variant
  Dim i As Long, j As Long, uba2 As Long
  
  Set d = CreateObject("Scripting.Dictionary")
  With ActiveSheet.ListObjects(1).DataBodyRange
    a = .Value2
    uba2 = UBound(a, 2)
    For i = 1 To UBound(a)
      For j = i To uba2
        If Not IsEmpty(a(i, j)) Then d(a(i, j)) = Empty
      Next j
    Next i
    .Offset(, .Columns.Count + 2).Resize(d.Count, 1).Value = Application.Transpose(d.Keys())
  End With
End Sub