Excel VBA : Concatenate Text Based on Criteria

Deepanshu Bhalla 8 Comments , ,
This article explains how to concatenate text based on unique ID values.

The input sample data is shown below -
Combine Rows Based On Condition

Download Excel Workbook

Step I : Extract Unique IDs
Check out this link - How to extract unique values

Step II : User Defined Function - Concatenate Text
Function Combinerows(CriteriaRng As Range, Criteria As Variant, _
ConcatenateRng As Range, Optional Delimeter As String = " , ") As Variant
Dim i As Long
Dim strResult As String
On Error GoTo ErrHandler
If CriteriaRng.Count <> ConcatenateRng.Count Then
Combinerows = CVErr(xlErrRef)
Exit Function
End If
For i = 1 To CriteriaRng.Count
If CriteriaRng.Cells(i).Value = Criteria Then
strResult = strResult & Delimeter & ConcatenateRng.Cells(i).Value
End If
Next i
If strResult <> "" Then
strResult = Mid(strResult, Len(Delimeter) + 1)
End If
Combinerows = strResult
Exit Function
ErrHandler:
Combinerows = CVErr(xlErrValue)
End FunUction
How to use
  1. Open an Excel Workbook
  2. Press Alt+F11 to open VBA Editor
  3. Go to Insert Menu >> Module
  4. In the module, write code for the function you want
  5. Save the file as Macro Enabled Workbook (xlsm) or Excel 97-2003 Workbook (xls) 
  6. Insert user defined function by typing the function i.e. =Combinerows()
Formula
=Combinerows($B$4:$B$9,E4,$C$4:$C$9)
$B$4:$B$9 - Cells in which ID values are placed
E4 - Cell in which unique value exists
$C$4:$C$9 - Values that need to be concatenated

If you want to change default separator from " , " to "-".
=Combinerows($B$4:$B$9,E4,$C$4:$C$9,"-")
Related Posts
Spread the Word!
Share
About Author:
Deepanshu Bhalla

Deepanshu founded ListenData with a simple objective - Make analytics easy to understand and follow. He has over 10 years of experience in data science. During his tenure, he worked with global clients in various domains like Banking, Insurance, Private Equity, Telecom and HR.

8 Responses to "Excel VBA : Concatenate Text Based on Criteria"
  1. Hi Deepanshu,

    Just thought of sharing my way of concatenating the values separated by ","

    Sub conc_str()

    Application.ScreenUpdating = False

    total_count = Range("g4").Value

    For i = 1 To total_count
    new_val_2 = ""
    starting_pos = Range("h" & (4 + i)).Value
    total_val = Range("i" & (4 + i)).Value

    For j = starting_pos To total_val

    new_val = new_val_2
    new_val_1 = Range("B" & j).Value
    If new_val = "" Then

    new_val_2 = new_val_1 & ","

    Else: new_val_2 = new_val & new_val_1 & ","

    End If

    Next j

    new_val_2 = Left(new_val_2, Len(new_val_2) - 1)

    Range("j" & (4 + i)).Value = new_val_2

    Next i


    End Sub


    'FYI, Range("G4") = # Unique ID's
    ' Column "H" contains Starting Position of the ID's
    ' Column "I" contains the Count of Unique ID's
    ' Column "J" contains the Concatenated Values

    ReplyDelete
    Replies
    1. Also to add, Total_val = (Staring_Position + Count of Unique ID's - 1)

      Delete
    2. Thanks Sourjya for sharing your style. I will try and get back to you.

      Delete
  2. HI deepanshu,
    thanks for this excellent stuff.
    Instead of separating the values by "&" or "-", I need to have a space using CHAR(10. Is that possible?

    Deepak

    ReplyDelete
  3. Amazing VBA code.
    Thanks for sharing.

    ReplyDelete
  4. Hi Deepanush
    Thanks for sharing, when i try it on my PC am getting expected error on delimeter

    can you please suggest.

    ReplyDelete
  5. Definitely a good solution if we want to concatenate with criteria. Thanks for sharing the VBA code!

    ReplyDelete
Next → ← Prev