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
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,003
Messages
5,834,828
Members
430,324
Latest member
bosphoruskid

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top