Concatenation based on values in a different column

[klyoldham]

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

 

[PaddyD]

hi - welcome to the board!

Book1
	A	B	C	D	E
1	10	Red		10	Red,Yellow
2	10	Yellow		12	Car,Truck,Boat
3	12	Car		3	Apple,Lemon,Banana,Peach
4	12	Truck
5	12	Boat
6	3	Apple
7	3	Lemon
8	3	Banana
9	3	Peach
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]

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]

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]

Yes, I installed HTML Maker.

I'm using the formula you provided:

ConcatTest.xls
	A	B	C	D	E
1	10	Red			=SUBSTITUTE(TRIM(MCONCAT(IF($A$1:$A$9=D1,$B$1:$B$9,"")," "))," ",",")
2	10	Yellow
3	12	Car
4	12	Truck
5	12	Boat
6	3	Apple
7	3	Lemon
8	3	Banana
9	3	Peach
10
Sheet1

 

[PaddyD]

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

 

[XLGibbs]

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

 

[PaddyD]

"could that be causing the error?"

Should just return a blank:

Book1
	A	B	C	D	E
1	10	Red
2	10	Yellow		12	Car,Truck,Boat
3	12	Car		3	Apple,Lemon,Banana,Peach
4	12	Truck
5	12	Boat
6	3	Apple
7	3	Lemon
8	3	Banana
9	3	Peach
Sheet3

 

[klyoldham]

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
	A	B	C	D	E
1	10	Red		10	{=SUBSTITUTE(TRIM(MCONCAT(IF($A$1:$A$9=D1,$B$1:$B$9,"")," "))," ",",")}
2	10	Yellow
3	12	Car		12
4	12	Truck
5	12	Boat
6	3	Apple		3
7	3	Lemon
8	3	Banana
9	3	Peach
Sheet1

 

[PaddyD]

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