How to list/combine this data?

[AmyB]

I've got a spreadsheet set up like this:

Col A: Col B:
Bob M10767
Carol C216
Bob Z2196
Jeff D7534
Bob D7535

If I make a list of all of the criteria in Column A, is there a formula that
will concatenate all of Column B's information into one cell?

Something like this:

Bob M10767, Z2196, D7535

 

[Jacob Skaria]

Col A Col B Col C
Bob M10767 M10767,Z2196,D7535
Carol C216 C216
Bob Z2196 M10767,Z2196,D7535
Jeff D7534 D7534
Bob D7535 M10767,Z2196,D7535

In cell C1
=CONCATBY($A$1:$B$5,A1,2)

$A$1:$B$5 - Array
A1 - lookup
2 - Column to be combined..


Try this UDF (User Defined function). From workbook launch VBE using
Alt+F11. From menu Insert a Module and paste the below function.Close and get
back to workbook and try the below formula.

Function CONCATBY(varRange As Range, _
strData As String, intCol As Integer)
Dim lngRow As Long
For lngRow = 1 To varRange.Rows.Count
If varRange(lngRow, 1) = strData Then
CONCATBY = CONCATBY & "," & varRange(lngRow, intCol)
End If
Next
CONCATBY = Mid(CONCATBY, 2)
End Function

 

[Pete_UK]

In your real data, how many Bob's are you likely to have? (i.e. the
maximum number of values that will be in that combined cell).

Pete

 

[AmyB]

Jacob, that worked! Thank you for your quick reply.

Pete, the most is about 15