CJW_14
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
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 :)
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 :)