Concatenate based on Category

Hi,
I'm trying to concatenate cells based on category. For example, if the category is 1, then the formula would look through the dataset for text values associated w/ the category 1 and concatenate them.

For example...in the attached, the dataset would be in Cols A&B and the formula would be in Col E.

Thank you so much!

concatenate.xlsx

Are you open/willing to use a VBA function?

OK, that makes things very difficult.

How much data do you have? Is there a maximum number of times a category can appear?

No good way to do this with just formulas.

Could there be dozens or 100's of entries per category?

That would mean you'd probably need 100's or even 1000's of formulas to do what you want.

Here's a visual example of what would be needed...

Data Range

	A	B	C	D	E	F	G	H	I	J
1	Category	Value	------	Category	Values	------	------	------	------	Concatenate
2	1	AA		1	AA	AB	AD	EE		AA, AB, AD, EE
3	1	AB		2	EB	B				EB, B
4	1	AD		3	A					A
5	2	EB		4	B	C				B, C
6	2	B
7	3	A
8	4	B
9	1	EE
10	4	C

You'd have to use helper cells to group the values according to category then do the concatenation from there.

With the amount of data you have this would be very inefficient using just worksheet formulas.

I strongly recommend using the VBA function to do this. No helper cells needed.

Hi,
Try this

Formula:

Please Login or Register  to view this content.

I have used a UDF named JoinR to make it much simpler..

Please Login or Register  to view this content.

Check the attached file..

I would use this UDF...

http://www.excelforum.com/showthread.php?p=3096647

Hi Tony Sir,
whats wrong with mine..?
Do you thing its slower than Sir Tiger's one...?


Regards,

Haven't tried yours.

The one I linked to can also return just the unique items which is a nice option to have.

Vikas, I dont think that was Tony's intent (to say anything was wrong with yours), if you look at the times, he posted 6 minutes after you, so it was probably just a timing thing

Also, (to both of you) it seemed to me that the OP said they did not want VBA, UDF is VBA (kinda), isnt it?

Originally Posted by FDibbins

it seemed to me that the OP said they did not want VBA, UDF is VBA (kinda), isnt it?

Yes, that's correct but using just worksheet functions and formulas is not practical in this application (too much data and too many corresponding items). (IMHO)

Sorry Fdibbins and Tony..
My intention wasn't wrong..
I supposed that if Tony had done any testing ( which he often does ).. then wanted to know which one is faster...
so that I can correct mine...

For the second part.. I agree with Tony..

Is add-in acceptable???
Mconcat ???

I inputed "1" in row 7. didn't work.

Good deal. Thanks for the feedback!