concatenate date for specific rows

[Catalin]

Hi guys,

I have a table reading like:

Col A Col B
1 abc
1 def
2 ryu
3 deff
3 dell
4 nodata
5 exp
5 part
5 file
-----------------------------------
The result I am looking for is like this
Col A Col B
1 abcdef
3 deffdell
5 exppartfile

Basically I need to have a concatenation of the the column B for those values that are to be found in the column A more than once. I am looking for a solution based on excel with no VBA. Thanks a lot guys.

 

[Mike H..]

Hi guys,

I have a table reading like:

Col A Col B
1 abc
1 def
2 ryu
3 deff
3 dell
4 nodata
5 exp
5 part
5 file
-----------------------------------
The result I am looking for is like this
Col A Col B
1 abcdef
3 deffdell
5 exppartfile

Basically I need to have a concatenation of the the column B for those values that are to be found in the column A more than once. I am looking for a solution based on excel with no VBA. Thanks a lot guys.

Hi,

If you're not prepared to use VBA then another solution is to download the addin MOREFUNC. This is a library of function you can use in Excel and one of those function is MCONCAT and this would do what you want.

=SUBSTITUTE(TRIM(MCONCAT(IF($A$1:$A$10=1,$B$1:$B$10,"")," "))," ","")


The formula above is an array formula so enter it with CTRL+Shift+Enter.

Link to MOREFUNC
http://download.cnet.com/Morefunc/3000-2077_4-10423159.html

 

[Hui]

Morefunc is an addin and as such uses VBA or something else
You simply don't have access to the code

 

[Asheesh]

Here is one more solution for you...formula driven...and no vba

 

[Nebu]

Another solution without helper columns and add-in function.....