Merge base on same code number

[Pran]

Hi Groups,

I have such data which is one column (ex:B) has various same code number,
but di other column (ex : A) has single different area. So, how can I make
within one code number consist of many area in one cell. If I use "&"
function, it takes longer time, how can i make this more quicker, data is
very big.

Regards,

 

[Pran]

Stan,

As below is sample work book....

AREA CODE
SPPT No. 0130 SHGB No. 72/Kanci Kulon
SPPT No. 0132 SHGB No. 72/Kanci Kulon
SPPT No. 0131 SHGB No. 72/Kanci Kulon
SPPT No. 0153 SHGB No. 75/Kanci Kulon
SPPT No. 0154 SHGB No. 75/Kanci Kulon
etc etc

So, i wish that :

CODE AREA
SHGB No. 72/Kanci Kulon SPTT No. 0130, SPPT No. 0132, SPPT No. 0131
And So on...


Please help

 

[Ashish Mathur]

Hi,

Please download and install the following addin
(http://download.cnet.com/Morefunc/3000-2077_4-10423159.html) and then use
the following array formula (Ctrl+Shift+Enter)

=SUBSTITUTE(TRIM(MCONCAT(IF($C$6:$C$10=B14,$B$6:$B$10,"")," ")),"
SPPT",",SPPT")

B6:B10 has the area and C6:C10 has the code. B14 holds the code

Hope this helps.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[Pran]

Hi there,

I already downloaded for addin and simply copy the formula, so far the
result is #NAME?, I checked and found that could be from MCONCAT syntax which
is not listed in my excel function.

Could give me more advice for this?

Kind Regards,
Pran