Concatenation based on values in a different column

klyoldham

New Member
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.

MrExcel MVP
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
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

MrExcel MVP
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

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

MrExcel MVP
odd - formula is syntactically correct, so you shouldn't get errors when entering ?!?

XLGibbs

Well-known Member

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

klyoldham

New Member
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

MrExcel MVP
you can send me you file - I have sent you a PM with my email address

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.

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?

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

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