Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Data Validation in Excel 2000 - Can 2 items from a list be combine | Excel Discussion (Misc queries) | |||
Combine Formula and Data Validation List in a Single Cell ?? | Excel Worksheet Functions | |||
How can I combine data from a list? | Excel Discussion (Misc queries) | |||
How do I combine tabulated data into a single column and list alphabetically? | Excel Discussion (Misc queries) | |||
How can I combine 3 separate mailing list? | New Users to Excel |