hi all!
example
a b c d
id name car
0141 john toyota
0186 chris mercedes
0141 john crysler
0141 john bmw
0112 marry range rover
0116 john(b) wolkvagen
how can i concatenate the all cars belongs one id in cell d?assume that there are thousands of cells like these
Having the results in a single cell requires that you add
the following function code in VBA to your workbook as a module.
In order to add the function to your workbook, run...
ALt+F11
Insert | Module
File | Close and Return to Microsoft Excel.
_________________________________________________________
Function
aconcat(a As Variant, Optional sep As String = "") As String
' Harlan Grove, Mar 2002
Dim y As Variant
If TypeOf a Is Range Then
For Each y In a.Cells
aconcat =
aconcat & y.Value & sep
Next y
ElseIf IsArray(a) Then
For Each y In a
aconcat =
aconcat & y & sep
Next y
Else
aconcat =
aconcat & a & sep
End If
aconcat = Left(
aconcat, Len(
aconcat) - Len(sep))
End Function
________________________________________________________
Now equipped with
ACONCAT...
Assuming that the sample you posted is in A1:C7, the headers included...
E2: 0141
F2, control+shift+enter, not just enter:
=SUBSTITUTE(
aconcat(IF($A$2:$A$7=E2,", "&$C$2:$C$7,"")),", ","",1)
