Consulting

Results 1 to 4 of 4

Thread: Creating comma separated list from multiple columns

  1. #1
    VBAX Regular
    Joined
    Jun 2018
    Posts
    33
    Location

    Creating comma separated list from multiple columns

    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
    Attached Files Attached Files

  2. #2
    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

  3. #3
    VBAX Regular
    Joined
    Jun 2018
    Posts
    33
    Location
    Thanks alot mate, works great!

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

  4. #4
    Glad to help & thanks for the feedback

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •