Concatenate the cell strings

[somnath_it2006]

Hi All,

I have below input data in my Excel Sheet:

Excel Workbook
	A	B
1	A	Red
2	A	Blue
3	A	Yellow
4	B	Pink
5	B	Black
Sheet1

And I want my Output like this:

Excel Workbook
	D	E
1	A	Red - Blue - Yellow
2	B	Pink - Black
Sheet1

So what Excel function I can use in cell - E1 to achieve the output.

Appreciate all your help.

 

[diddi]

som i think this might be a vba problem

 

[WinteE]

On my site you'll find the user defined function =ARLOOKUP(). Excel, Lookup, Lookup several values.

Success,
Erik

 

[Aladin Akyurek]

Add the following function to your workbook:
----------------------------
Function aconcat(a As Variant, Optional sep As String = "") As String
' Harlan Grove, Mar 2002
Dim y As Variant

If TypeOf a Is Range Then
For Each y In a.Cells
aconcat = aconcat & y.Value & sep
Next y
ElseIf IsArray(a) Then
For Each y In a
aconcat = aconcat & y & sep
Next y
Else
aconcat = aconcat & a & sep
End If

aconcat = Left(aconcat, Len(aconcat) - Len(sep))
End Function
----------------------------

Now you can invoke...

E1, control+shift+enter, not just enter:

=SUBSTITUTE(ACONCAT(IF($A$1:$A$5=D1," - "&$B$1:$B$5,""))," - ","",1)

 

[somnath_it2006]

Thanks lot for your replies...