Concatenate across same products

[K.J]

Hi All,

I have the following table (starting in cell A1)

Product Order Number
A 5551
B 2263
B 5561
B 5555
C 5587
C 1204
C 1203
C 6430
D 6430
D 2240
D 5548
D 5549
D 5575
E 2268
E 2269
E 5539
E 5565
E 5535
E 5536
F 2254
F 2279
F 2255
F 2256
F 2257
F 5537
F 5538
F 2252
F 2253

I am trying to concatenate all the order numbers for the same product (with a space).

I have attempted with the following formula but can only seem to get a maximum of 2 order numbers:

(In cell C4)

=IF(A4=A3,CONCATENATE(B4&" "&B3),B4)

(this returns - '5561 2263')

Is there a way to get all the order numbers together (in a general formula, as this is only small portion of the data-set)? So for example, for product C, I want the formula to return '5587 1204 1203 6430'.

Any help is appreciated.

Thanks!

[Glenn Kennedy]

Here's a UDF (User Defined Function) to do this for you. Enable macros on opening.

the formula takes the form =Concatall(if(range=criterion,range,"delimiter")

Array enter this formula. Array Formulae are a little different from ordinary formulas in that they MUST be confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER.

You will know the array is active when you see curly braces { } appear around the outside of your formula. If you do not CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly braces yourself - it won't work...

=concatall(IF($A$2:$A$29=D9,$B$2:$B$29,""),", ")

where D9 contains the first of your products.

[K.J]

Thank you very much Glenn! This is perfect.

Not sure why the 'ConcatAll' function did not come up in my version of Excel but I copied all my data into your spreadsheet and it works fine...

Thanks again for the swift response and the handy lesson.

[Glenn Kennedy]

I was in a bit of a hurry yesterday. A UDF is a specially written routine that requires some code to make it go. You can see it by right clicking on the Tab snd select View Code. The relevant code is in module 1.

Thanks for the Rep