Creating comma separated list from multiple columns

[CJW_14]

Hi All,

I was wondering if someone could help me out.

What I'm trying to achieve is to combine data from 2 columns, remove duplicates and create a comma separated text list in a single cell. The number of rows in these columns will vary.

I did some googling and created this as a starting point.

Sub Macro1()
' Copy position IDs from column J and S into a combined list
rcB = Sheets("sheet1").Cells(Rows.Count, "J").End(xlUp).Row
rcC = Sheets("sheet1").Cells(Rows.Count, "S").End(xlUp).Row

    
    Range("J4", "J" & rcB).Copy
    Range("V4").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("S4", "S" & rcC).Copy
    Range("V" & ActiveSheet.Cells(Rows.Count, "V").End(xlUp).Row).Offset(1, 0).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

' Remove duplicates from list
    
    Sheets("sheet1").Columns("V:V").Select
    ActiveSheet.Range("$V$1:$V$49").RemoveDuplicates Columns:=1, Header:=xlNo
       
' Create comma separated list in single cell

'????????????
    
End Sub

I then got stuck on creating the comma separated list. I found the following code, but was unable to make it work without creating the combined list in column A.

Sub Commaseparatedlist()
Dim combined As String
lr = Cells(Rows.Count, 1).End(xlUp).Row
Range(Cells(lr, 1), Cells(lr, 1)).Select
i = lr
While i > 1
    If i = lr Then
    combined = Cells(i, 1).Value
    Else
      combined = Cells(i, 1).Value & "," & combined
    End If
i = i - 1
Wend
Cells(1, 2).Value = combined
End Sub

Ultimately I would just like to output the comma separated list in a single cell but it got more difficult than I expected with my obvious lack experience but i'm learning Any help would be greatly appreciated

[Fluff]

How about

Sub GetUnique()
   Dim cl As Range
   
   With CreateObject("scripting.dictionary")
      For Each cl In Range("J4", Range("J" & Rows.Count).End(xlUp))
         .Item(cl.Value) = Empty
      Next cl
      For Each cl In Range("S4", Range("S" & Rows.Count).End(xlUp))
         .Item(cl.Value) = Empty
      Next cl
      Range("A1").Value = Join(.keys, ", ")
   End With
End Sub

This replaces all your code

[CJW_14]

Thanks alot mate, works great!

The code looks so simple when someone gives it to you LOL

[Fluff]

Glad to help & thanks for the feedback