combine B results for multiple matches in A?

Not sure if this makes sense or not but here goes

I have 2 columns of information, the first one is a sku and the second is a description. The sku column has multiples of the same sku, each having a different description. What i need to do is combine the descriptions into one cell seperated by a character such as a comma or posibly html page breaks

SKU Description

1 a
2 a
1 b
2 b
2 c
2 d
1 c


so i need the results to look like

1 a,b,c
2 a,b,c,d

I only know some basic formulas and some random vba stuff but im just trying to figure out how to get this accomplished with a formula

Any help would be awesome Thanks

-Dan

If you have more than a few rows of data then this is almost impossible to do with a formula in Excel just using Excels native functions (because you need to concatenate an array, for which there is no inbuilt function).

However you can do it if you download the MOREFUNC addin and use the MCONCAT function, for your problem, with a specific SKU in D1

=SUBSTITUTE(MCONCAT(IF(A$1:A$100=D1,","&B$1:B$100,"")),",","",1)

confirmed with CTRL+SHIFT+ENTER

Download Morefunc from here

http://xcell05.free.fr/

Alternatively you could look for a VBA solution - try Googling

Harlan Grove ACONCAT

Originally Posted by daddylonglegs

If you have more than a few rows of data then this is almost impossible to do with a formula in Excel just using Excels native functions (because you need to concatenate an array, for which there is no inbuilt function).

However you can do it if you download the MOREFUNC addin and use the MCONCAT function, for your problem, with a specific SKU in D1

=SUBSTITUTE(MCONCAT(IF(A$1:A$100=D1,","&B$1:B$100,"")),",","",1)

confirmed with CTRL+SHIFT+ENTER

Download Morefunc from here

http://xcell05.free.fr/

Alternatively you could look for a VBA solution - try Googling

Harlan Grove ACONCAT

This works perfect, the only problem i have though is that the work sheet will be 10,000-18,000 lines and if i put anything over 100 in that formula i get #value error or it will only return one result instead of combining the multiples

Mmmmm...

One thing I forgot, MCONCAT is limited to 255 characters, I believe, so if each SKU has multiple descriptions (which add up to more than 255 characters, including the commas) then this won't work, sorry.

The only workaround I can think of is concatenating the results of several of the above MCONCAT formulas covering different ranges but while that might work for 1000 rows or fewer, 18000 might be too much.

..or look into the VBA alternatives

thats exactly the problem, i got it down to the exact line that it errors on which is the 5th or so matching result. This would work for some sku's that dont have long descriptions but it'd be to speratic. Theres no other ways around this with excel though huh?

Unless I take on learning access to see if I can do it with that and then the last option is geting a programer to do somthing for us.

Thanks for the help so far though


-Dan