Combining cells based on value of adjacent cell.

Hey there.... I'm new to the forums so please be gentle with me

I have been reading through trying to find a similar post but the nearest i could find was one dealing with numbers rather than text.....

This is how the workbook looks...

Column A contains a list of numbers based on vehicle types

Column B contains a list of vehicle names

A B
1 Car,
1 Taxi,
2 Bus,
2 Lorry,
3 Skateboard,
3 Bike,
1 Motorcycle,


What I am looking for is the best way to combine the text from column B depending on the value of column a.

the finished results would then end up as: "Car,Taxi,Motorcycle,", or "Bus,Lorry," etc.......

This is just a simple list.. The one I am working with has around 2500 entries. I have been trying to do this using the CONCATENATE function and IF statement but it's just not working..... My head is ready to explode

Can someone point me in the right direction?

TIA

Gav.

If you want all the matches to appear in one cell, then Excel has no easy way to do it.., but there is a free addin found here: http://xcell05.free.fr/morefunc/english/ called Morefunc.xll.

If you download it you can use the MCONCAT() function.

=MCONCAT(IF($A$1:$A$7=A1,$B$1:$B$7,""))

Which, after you adjust the ranges to suit your actual data, must be confirmed with CTRL+SHIFT+ENTER not just ENTER. You will see { } brackets appear around the formula.

Then copy down.

This will repeat the result when the same number occurs in column A.

If you don't want repeats and you simply want result for 1, 2, 3, etc

then try:

e.g. =MCONCAT(IF($A$1:$A$7=ROWS($A$1:$A1),$B$1:$B$7,""))

again, confirmed with CSE keys and copied down.

kcgatchi,

Welcome to the Forum, unfortunately:

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.

Go to the relevant subforum on the main page like Excel Programming or Excel General or Excel functions. Click on it, you will see a button "Post New Thread". Click on that and proceed.

If you are only looking for a place to download the Morefunc addin, try here:

http://download.cnet.com/Morefunc/30...-10423159.html

If you have actual questions, then start new thread.