Concatenate

[Sharp]

Hi,

I'm not even sure if this can be done...but...

In column 'A' i have a list of numbers and in column 'C' i also have a list of numbers. However the same number may appear in column 'A' numerous times (they will appear 1 above the other as i have sorted them) whilst the number in 'C' will vary even if 'A' is the same. What i basically need to do is some form of concatenate...where in column 'g', on the same row as a number in 'A' a concatenate formula is entered. This will concatenate all the variable in 'C' of the duplicated SKU in 'A'....but it will have to be in column 'g' where the number first appears in 'A'......

yes complicated....i have attached an example sheet....

[NBVC]

If you download and install Morefunc.xll utility from this site (it's free)...

http://xcell05.free.fr/morefunc/english/

then you can use the MCONCAT() function to do what you want.

First insert a row above your data... then in G2 enter formula:

=IF(E2=E1,"",MCONCAT(IF($E$2:$E$20=E2,$C$2:$C$20,"")))

This is an array formula and must be confirmed with CTRL+SHIFT+ENTER not just ENTER...you will see { } brackets appear around it.

Then copy it down.

Note: If you adjust ranges, etc in the formula you have to reconfirm it with the CSE keys, before copying it down again.