How to list/combine this data?
 

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

How to list/combine this data?
 

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

--
If this post helps click Yes
---------------
Jacob Skaria


"AmyB" wrote:

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

How to list/combine this data?
 

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

On Aug 31, 6:51*pm, AmyB wrote:
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

How to list/combine this data?
 

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

Pete, the most is about 15

"Pete_UK" wrote:

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

On Aug 31, 6:51 pm, AmyB wrote:
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