CODEWITHSUNDEEP
vbaexcelconcatenation
CoderScissorhands
CoderScissorhands

Reputation: 495

Concatenating 2 columns, by-element, in Excel - values and text, omitting blank cells (with vba)

series_a (note there are blank rows)
The
99

quick
199
brown

299
299

The vba module "ConcatUniq" concatenates values and text from one column, omitting any blank cells and duplicates.Here is "ConcatUniq".

Function ConcatUniq(ByRef rng As Range, ByVal myJoin As String) As String
Dim r As Range
Static dic As Object
If dic Is Nothing Then Set dic = CreateObject("Scripting.Dictionary")
For Each r In rng
    dic(r.Value) = Empty
Next
ConcatUniq = Join$(dic.keys, myJoin)
dic.RemoveAll
End Function

So ConcatUniq does this to series_a [ConcatUniq(series_a, ",")]:


The, 99, quick, 199, brown, 299


(note that the last item was ignored)
I am trying to modify ConcatUniq so that it merges two columns element-by-element. I want the user to manually select each range (either with text or by specifying it, because they will not be adjacent). I would like two additional features: 1) I would like it to ignore blank cells, and 2) I would like to retain duplicate values. To show what I'd like the output to be , we'll need a second column of data:

series_b
105
205

305
405
505

605
605


This is the way I'd like this new function Concat2Series(series_a, series_b, ",") to output:


The, 105, 99, 205, quick, 305, 199, 405, brown, 505, 299, 605, 299, 605


Note blanks are ignore, and duplicates are retained.
Can someone help me with this?

Upvotes: 0

Views: 137

Answers (2)

CoderScissorhands
CoderScissorhands

Reputation: 495

I modified Alex P's code to get this to skip blanks. I did this by adding if/then statements. That may not be the most elegant solution. Nonetheless, it works! Thanks Alex P! :

Function ConCatTwoColumnsSkipBlanks(colA As Range, colB As Range) As String
Dim rw As Integer, res As String

For rw = 1 To colA.Rows.Count

    If IsEmpty(colA(rw)) = True Then
    If IsEmpty(colB(rw)) = True Then 'if both are empty
     res = res
    Else   'if only B has data
     res = res & colB(rw) & IIf(rw = colA.Rows.Count, vbNullString, ", ")
    End If
    End If


    If IsEmpty(colA(rw)) = False Then
    If IsEmpty(colB(rw)) = True Then  'if only A has data
      res = res & colA(rw) & IIf(rw = colA.Rows.Count, vbNullString, ", ")
    Else  ' if both have data
      res = res & colA(rw) & ", " & colB(rw) & IIf(rw = colA.Rows.Count, vbNullString, ", ")
    End If
    End If

Next rw

ConCatTwoColumnsSkipBlanks = res
End Function

Upvotes: 0

Alex P
Alex P

Reputation: 12487

This might help you get started:

Sub ConCatTwoColumns()
    Dim colA As Range, colB As Range, rw As Integer, res As String

    Set colA = Range("A1:A6")
    Set colB = Range("B1:B6")

    For rw = 1 To colA.Rows.Count
        res = res & colA(rw) & ", " & colB(rw) & IIf(rw = colA.Rows.Count, vbNullString, ", ")
    Next rw

    Debug.Print res '~~> The, 105, 99, 205, quick, 305, 199 brown, 405, brown, 505, 199, 605
End Sub

As a function it is simply:

Function ConCatTwoColumns(colA As Range, colB As Range) as String
    Dim rw As Integer, res As String

    For rw = 1 To colA.Rows.Count
        res = res & colA(rw) & ", " & colB(rw) & IIf(rw = colA.Rows.Count, vbNullString, ", ")
    Next rw

    ConCatTwoColumns = res
End Function

Upvotes: 1

Related Questions