Combine two comma separated columns into a single column

[Cwillson]

Hi all,

I am currently using the following VBA script to transform a single column of data, with cells that include comma separated values, into a single column of unique values;

I now want to convert two columns of source data, with cells that include comma separated values, into a single column of unique values, but have had no success in editing the script.

Sample Data:

A (source dataset 1)	B (source dataset 2)	C (result)
4567, 8456, 46	5, 89	5
12, 46, 7865	9999	12
	100, 8956	46
	12	89
	7865	100
		4567
		7865
		8456
		8956
		9999

Can anyone suggest how I can go about doing this please?

Thanks,

Chris

 

[Fluff]

Can you post your actual code, rather than an image. How to Post Your VBA Code

 

[Cwillson]

of course, sorry!

Private Sub CommandButton1_Click()
    Dim a, i As Long, v, v2
    With ActiveSheet
        a = .Range("a1", .Cells(.Rows.Count, 1).End(xlUp)).Value
        With CreateObject("scripting.dictionary")
            For i = 1 To UBound(a, 1)
                v = Split(Replace(a(i, 1), " ", ""), ",")
                For Each v2 In v
                    .Item(v2) = ""
                Next
            Next
            a = .keys
        End With
        .Range("b1").Resize(UBound(a, 1) + 1).Value = Application.Transpose(a)
        .Columns("b").Sort .Columns("b")
    End With
End Sub

 

[Fluff]

Thanks for that, how about

Private Sub CommandButton1_Click()
    Dim a, i As Long, j As Long, v, v2
    With ActiveSheet
        a = .Range("A1:B" & .Range("A" & Rows.Count).End(xlUp).Row).Value
        With CreateObject("scripting.dictionary")
            For i = 1 To UBound(a, 1)
               For j = 1 To UBound(a, 2)
                  For Each v2 In Split(Replace(a(i, j), " ", ""), ",")
                     .Item(v2) = ""
                  Next v2
               Next j
            Next i
            a = .Keys
        End With
        .Range("C1").Resize(UBound(a, 1) + 1).Value = Application.Transpose(a)
        .Columns("C").Sort .Columns("C")
    End With
End Sub

 

[Cwillson]

Thanks for that, how about

Private Sub CommandButton1_Click()
    Dim a, i As Long, j As Long, v, v2
    With ActiveSheet
        a = .Range("A1:B" & .Range("A" & Rows.Count).End(xlUp).Row).Value
        With CreateObject("scripting.dictionary")
            For i = 1 To UBound(a, 1)
               For j = 1 To UBound(a, 2)
                  For Each v2 In Split(Replace(a(i, j), " ", ""), ",")
                     .Item(v2) = ""
                  Next v2
               Next j
            Next i
            a = .Keys
        End With
        .Range("C1").Resize(UBound(a, 1) + 1).Value = Application.Transpose(a)
        .Columns("C").Sort .Columns("C")
    End With
End Sub

Amazing, thank you! Once again, Fluff to the rescue!

 

[Cwillson]

Thanks for that, how about

Private Sub CommandButton1_Click()
    Dim a, i As Long, j As Long, v, v2
    With ActiveSheet
        a = .Range("A1:B" & .Range("A" & Rows.Count).End(xlUp).Row).Value
        With CreateObject("scripting.dictionary")
            For i = 1 To UBound(a, 1)
               For j = 1 To UBound(a, 2)
                  For Each v2 In Split(Replace(a(i, j), " ", ""), ",")
                     .Item(v2) = ""
                  Next v2
               Next j
            Next i
            a = .Keys
        End With
        .Range("C1").Resize(UBound(a, 1) + 1).Value = Application.Transpose(a)
        .Columns("C").Sort .Columns("C")
    End With
End Sub

Actually, a teeny follow-up question...

If I wanted to parse both columns and return a single column of results for each, i.e. basically run the first query twice on the same worksheet from a single button, how would I do that?

I tried creating a second command button and changing the references so they were unique for each, but the double use of CreateObject("scripting.dictionary") seems to be causing a problem in that the 2nd button creates a duplicate of the first list.

(sorry, I'm such a numpty when it comes to VBA!)

Here's what I have - although I recognise that using two separate command buttons is not technicalyl required....as long as one can write VBA....which I can't!

Private Sub CommandButton1_Click()
     Dim a, i As Long, v, v2
    With ActiveSheet
        .Columns("b").Clear
        a = .Range("a1", .Cells(.Rows.Count, 1).End(xlUp)).Value
        With CreateObject("scripting.dictionary")
            For i = 1 To UBound(a, 1)
                v = Split(Replace(a(i, 1), " ", ""), ",")
                For Each v2 In v
                    .Item(v2) = ""
                Next
            Next
            a = .keys
        End With
        .Range("b1").Resize(UBound(a, 1) + 1).Value = Application.Transpose(a)
        .Columns("b").Sort .Columns("b")
    End With


End Sub

Private Sub CommandButton2_Click()
 Dim c, j As Long, w, w2
    With ActiveSheet
        .Columns("g").Clear
        f = .Range("f1", .Cells(.Rows.Count, 1).End(xlUp)).Value
        With CreateObject("scripting.dictionary")
            For j = 1 To UBound(f, 1)
                w = Split(Replace(f(j, 1), " ", ""), ",")
                For Each w2 In w
                    .Item(w2) = ""
                Next
            Next
            f = .keys
        End With
        .Range("g1").Resize(UBound(f, 1) + 1).Value = Application.Transpose(f)
        .Columns("g").Sort .Columns("g")
    End With
End Sub

 

[Fluff]

It should be

f = .Range("f1", .Cells(.Rows.Count, 6).End(xlUp)).Value

 

[Cwillson]

It should be

f = .Range("f1", .Cells(.Rows.Count, 6).End(xlUp)).Value

wow, that works, thank you again. I REALLY need to get my **** in gear and get to grips with learning VBA!! Feel so stupid!

 

[Fluff]

You're welcome & thanks for the feedback.