VBA to return only unique values from a range of comma separated numbers on multiple rows.

[RockandGrohl]

Hello, I have a quandary.

This is my list of Mailer Regions:

Regions
1, 2, 11
12, 11
3, 4
3, 12
27, 20, 30
13, 21, 20, 31
31, 30, 39, 40
38, 40, 41, 50
31, 32, 39
22, 32
32, 41
51, 50

So what I'd like is to write a unique list based on the Regions column to another sheet in column AE from 2 downwards, so it would look like:


(Also, when I add a table in this on the bottom of the line, how do I create new lines under the table (below the bottom row) because when I hit enter it just expands the bottom row of the table and I haven't worked it out)

Inclusions
1
2
3
4
11
12
20
21
And so on

 

[RockandGrohl]

It's probably important to include the reasoning for this in case I'm approaching it in a stupid way.

I'm using Advanced Filter to filter a list of over 100,000 clients based on various criteria. One of those is their mailer region has to match the mailer regions that are input. Each client will have an integer in the mailer region field, say, 1 or 4 or 65 etc. I need to filter out everyone whose mailer region doesn't match, and to do that I'm inserting a formula in the advanced filter criteria which will be similar to this:

ActiveCell.Formula = "=AND(DataFeed!$R2<>"""",ISERROR(MATCH(DataFeed!$R2,$AC$2:AC$" & RegStringEnd & ",0)))"

 

[Fluff]

How about

Sub RockandGrohl()
   Dim Ary As Variant, Sp As Variant
   Dim r As Long, i As Long
   
   Ary = Range("A2", Range("A" & Rows.Count).End(xlUp)).Value2
   
   With CreateObject("scripting.dictionary")
      For r = 1 To UBound(Ary)
         Sp = Split(Ary(r, 1), ",")
         For i = 0 To UBound(Sp)
            .Item(val(Sp(i))) = Empty
         Next i
      Next r
      Range("AE2").Resize(.Count).Value = Application.Transpose(.Keys)
   End With

 

[RockandGrohl]

How about

Sub RockandGrohl()
   Dim Ary As Variant, Sp As Variant
   Dim r As Long, i As Long
  
   Ary = Range("A2", Range("A" & Rows.Count).End(xlUp)).Value2
  
   With CreateObject("scripting.dictionary")
      For r = 1 To UBound(Ary)
         Sp = Split(Ary(r, 1), ",")
         For i = 0 To UBound(Sp)
            .Item(val(Sp(i))) = Empty
         Next i
      Next r
      Range("AE2").Resize(.Count).Value = Application.Transpose(.Keys)
   End With

Yeah this is where I got to before I got errors that I didn't understand

Dim MyArray As Variant

Dim d

Set d = CreateObject("Scripting.Dictionary")

LastRow = Cells(Rows.Count, "A").End(xlUp).Row

Set MyRange = Range("A2:A" & LastRow)
For Each cell In MyRange
    MyArray = Split(cell.Value, ",")
    For i = 0 To UBound(MyArray)
    d.Add MyArray
    Next i
Next cell

Yours worked marvellously and so clever using transpose to take the horizontal string and ram it in vertically. Can't wait to get it implemented thanks

 

[Fluff]

You're welcome & thanks for the feedback.