Concatenation based on values in a different column

klyoldham

New Member
Joined
Dec 13, 2005
Messages
5
Hello....I'm hoping someone can help me with this:

I need to concatenate data from several fields in the same column, based on the value in a separate column. The number of fields are not the same. I have a ws that looks like:

Column A Column B

10 Red
10 Yellow
12 Car
12 Truck
12 Boat
3 Apple
3 Lemon
3 Banana
3 Peach


After the concatenation I need the following:

Column A Column B

10 Red,Yellow
12 Car,Truck,Boat
3 Apple,Lemon,Banana,Peach

Is this possible? Any help would be greatly appreciated.

Thanks,
Klyoldham
 

Some videos you may like

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
hi - welcome to the board!
Book1
ABCDE
110Red10Red,Yellow
210Yellow12Car,Truck,Boat
312Car3Apple,Lemon,Banana,Peach
412Truck
512Boat
63Apple
73Lemon
83Banana
93Peach
Sheet3


formula is:

=SUBSTITUTE(TRIM(MCONCAT(IF($A$1:$A$9=D1,$B$1:$B$9,"")," "))," ",",")

..which needs to be entered with control + shift + enter, not just enter.

mconcat() is from the morefunc addin, so you'll need to download & instll it - see the recommended addins thread at the top of the board.
 

klyoldham

New Member
Joined
Dec 13, 2005
Messages
5
getting an error

Thank you so much for your reply.

When I press ctrl+shift+enter I get an error.

"The formula you typed contains an error"

Click ok and the section that is highlighted is: D1,$B$1

Do you have any ideas what may be causing this????

Thanks again!
Klyoldham
 

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
1) Did you download & install the html maker addin? (It should show up & be selected in tools | addins)

2) Post the exact formula you're trying to enter.
 

klyoldham

New Member
Joined
Dec 13, 2005
Messages
5

ADVERTISEMENT

Yes, I installed HTML Maker.

I'm using the formula you provided:
ConcatTest.xls
ABCDE
110Red=SUBSTITUTE(TRIM(MCONCAT(IF($A$1:$A$9=D1,$B$1:$B$9,"")," "))," ",",")
210Yellow
312Car
412Truck
512Boat
63Apple
73Lemon
83Banana
93Peach
10
Sheet1
 

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
odd - formula is syntactically correct, so you shouldn't get errors when entering ?!?
 

XLGibbs

Well-known Member
Joined
Feb 25, 2005
Messages
2,446

ADVERTISEMENT

I don't see anything in D1 on your sheet
could that be causing the error?
 

klyoldham

New Member
Joined
Dec 13, 2005
Messages
5
So now, after I click CTRL+SHIFT+ENTER I get the error and I click Yes. I then select Tools->Error Checking. Goes through and says that Error Checking is complete. Now I have the {} around the formula, but no values appear in the cell. Just the formula. I'm stumped!
ConcatTest.xls
ABCDE
110Red10{=SUBSTITUTE(TRIM(MCONCAT(IF($A$1:$A$9=D1,$B$1:$B$9,"")," "))," ",",")}
210Yellow
312Car12
412Truck
512Boat
63Apple3
73Lemon
83Banana
93Peach
Sheet1
 

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
you can send me you file - I have sent you a PM with my email address
 

Watch MrExcel Video

Forum statistics

Threads
1,118,455
Messages
5,572,235
Members
412,449
Latest member
mdvouf
Top