Extract unique values across rows and columns

[dkoucky]

[FONT=&quot]I have a sheet that looks like the below (except 4k rows and between 1 and 79 columns) I would like to extract all the unique values into one row or column so I can see what all of these unique values are. Removing duplicates does not seem to be working.[/FONT]

<thead style="margin-left: 0px; margin-right: 0px; margin-top: 0px;">
[TH="align: left"]HISPANIC HI[/TH]
[TH="align: left"]ALDI[/TH]
[TH="align: left"]AUTO ZONE[/TH]
[TH="align: left"]BARNES & NOBLE[/TH]
[TH="align: left"][/TH]

</thead><tbody style="margin-left: 0px; margin-right: 0px; margin-bottom: 0px;">
[TD="align: left"]HISPANIC LOW[/TD]
[TD="align: left"]FRED`S[/TD]
[TD="align: left"]ALDI[/TD]
[TD="align: left"]AUTO ZONE[/TD]
[TD="align: left"][/TD]

[TD="align: left"]HISPANIC LOW[/TD]
[TD="align: left"]ADVANCE AUTO[/TD]
[TD="align: left"]AUTO ZONE[/TD]
[TD="align: left"]DOLLAR GENERAL[/TD]
[TD="align: left"]HOME DEPOT[/TD]

[TD="align: left"]HISPANIC LOW[/TD]
[TD="align: left"]ALDI[/TD]
[TD="align: left"][/TD]
[TD="align: left"][/TD]
[TD="align: left"][/TD]

[TD="align: left"]FRED`S[/TD]
[TD="align: left"]AUTO ZONE[/TD]
[TD="align: left"]BEST BUY[/TD]
[TD="align: left"][/TD]
[TD="align: left"][/TD]

[TD="align: left"]FRED`S[/TD]
[TD="align: left"]AUTO ZONE[/TD]
[TD="align: left"]BARNES & NOBLE[/TD]
[TD="align: left"][/TD]
[TD="align: left"][/TD]

[TD="align: left"]FRED`S[/TD]
[TD="align: left"][/TD]
[TD="align: left"][/TD]
[TD="align: left"][/TD]
[TD="align: left"][/TD]

[TD="align: left"]ALDI[/TD]
[TD="align: left"]AUTO ZONE[/TD]
[TD="align: left"]DOLLAR GENERAL[/TD]
[TD="align: left"]JCPENNEY[/TD]
[TD="align: left"]LOWES[/TD]

[TD="align: left"]HISPANIC LOW[/TD]
[TD="align: left"]AUTO ZONE[/TD]
[TD="align: left"]DOLLAR GENERAL[/TD]
[TD="align: left"][/TD]
[TD="align: left"][/TD]

[TD="align: left"]FRED`S[/TD]
[TD="align: left"]AUTO ZONE[/TD]
[TD="align: left"]DOLLAR GENERAL[/TD]
[TD="align: left"]HOBBY LOBBY[/TD]
[TD="align: left"]HOME DEPOT[/TD]

[TD="align: left"]AUTO ZONE[/TD]
[TD="align: left"]DOLLAR GENERAL[/TD]
[TD="align: left"]JCPENNEY[/TD]
[TD="align: left"]LOWES[/TD]
[TD="align: left"][/TD]

[TD="align: left"]HISPANIC MED[/TD]
[TD="align: left"]OTHER HISP LOW[/TD]
[TD="align: left"]ALDI[/TD]
[TD="align: left"]AUTO ZONE[/TD]
[TD="align: left"]BEST BUY[/TD]

</tbody>

 

[Fluff]

How about

Sub getUnique()
   Dim Ary As Variant
   Dim Itm As Variant
   
   Ary = Range("A1").CurrentRegion.Value2
   With CreateObject("scripting.dictionary")
      .comparemode = vbTextCompare
      For Each Itm In Ary
         If Not .Exists(Itm) Then .Add Itm, Nothing
      Next Itm
      Range("A" & Rows.count).End(xlUp).Offset(3).Resize(, .count).Value = .keys
   End With
End Sub

This assumes that your data starts in A1 & you won't have more than 16384 unique values

 

[dkoucky]

I lied this worked great! it just put the list at the bottom of my data after a blank row!

Thanks!!!!

 

[Fluff]

Glad to help & thanks for the feedback

 

[mse330]

This is my try which will put the results in a new sheet, though its not as professional as Fluff's code
@Fluff
Is 16384 the limit of the dictionary ? Oh & thank you I leaned a new way to read all data from an array instead of double loop I did

Sub UniqueValue()

Dim dict As Object, Arr() As Variant, Rg As Range, lRow As Double, ws As Worksheet

Set Rg = ActiveSheet.UsedRange
Arr = Rg

Set dict = CreateObject("Scripting.Dictionary")
dict.comparemode = vbTextCompare ' <--- you can comment/delete this line if your data is case sensitive

For x = 1 To Rg.Rows.Count
    For y = 1 To Rg.Columns.Count
        If Arr(x, y) <> "" And Not dict.Exists(Arr(x, y)) Then dict.Add Key:=Arr(x, y), Item:=0
    Next y
Next x

Set ws = Sheets.Add(After:=Worksheets(Worksheets.Count))

ws.Range("A1").Resize(dict.Count, 1) = Application.Transpose(dict.keys)
Range("A1").Sort Range("A1"), xlAscending
Columns(1).AutoFit

End Sub

 

[Fluff]

Is 16384 the limit of the dictionary

Nope, that's the number of columns available. So if there were more values than that you couldn't output them in one row

 

[mse330]

Nope, that's the number of columns available. So if there were more values than that you couldn't output them in one row

Oh ok I didn't notice you have your output in a row. Again, thanks for the clarification