Excel VBA : Concatenate Text Based on Criteria

Best Online Course : SAS Programming with 50+ Case Studies

- Explain Programming Concepts in Simple English
- Live Projects & Case Studies
- Job Placement Assistance
- Get 20% off till July 14, 2017

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

About Author:

Deepanshu founded ListenData with a simple objective - Make analytics easy to understand and follow. He has close to 7 years of experience in data science and predictive modeling. During his tenure, he has worked with global clients in various domains like retail and commercial banking, Telecom, HR and Automotive.


While I love having friends who agree, I only learn from those who don't.

Let's Get Connected: Email | LinkedIn

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

Related Posts:

5 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

Next → ← Prev