This article explains how to concatenate text based on unique ID values.
The input sample data is shown below -
Download Excel Workbook
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 "-".
The input sample data is shown below -
 |
| Combine Rows Based On Condition |
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
- Open an Excel Workbook
- Press Alt+F11 to open VBA Editor
- Go to Insert Menu >> Module
- In the module, write code for the function you want
- Save the file as Macro Enabled Workbook (xlsm) or Excel 97-2003 Workbook (xls)
- 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,"-")
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.
Hi Deepanshu,
ReplyDeleteJust 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
Also to add, Total_val = (Staring_Position + Count of Unique ID's - 1)
DeleteThanks Sourjya for sharing your style. I will try and get back to you.
DeleteHI deepanshu,
ReplyDeletethanks for this excellent stuff.
Instead of separating the values by "&" or "-", I need to have a space using CHAR(10. Is that possible?
Deepak
Hi Deepanush
ReplyDeleteThanks for sharing, when i try it on my PC am getting expected error on delimeter
can you please suggest.
thanks!
ReplyDelete