Excel VBA : Concatenate Text Based on Criteria

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,"-")

Excel Tutorials : 100 Excel Tutorials

Get Free Email Updates :
*Please confirm your email address by clicking on the link sent to your Email*

Related Posts:

4 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

Next → ← Prev