View Full Version : Creating comma separated list from multiple columns

07-30-2018, 06:12 PM
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
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

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
combined = Cells(i, 1).Value & "," & combined
End If
i = i - 1
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 :)

07-31-2018, 07:49 AM
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

08-01-2018, 02:09 PM
Thanks alot mate, works great!

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

08-02-2018, 04:17 AM
Glad to help & thanks for the feedback