Excel Formula

[stunt]

Hi,

I have tried combining a number of formulas to give me a combination of text results but am very unsuccessful right now.

Data is as below:
A1 Joe ; B1 A
A2 Peter ; B2 B
A3 Raka ; B3 C
A4 Lew ; B4 A
A5 Ken ; B5 B
A6 Ray ; B6 C
A7 Joe ; B7 A
A8 Peter ; B8 B
A9 Raka ; B9 B
A10 Lew ; B10 C
A11 Ken ; B11 C
A12 Joe ; B12 A
---------------------------------------
D1 Joe ; E1 A,A,A
D2 Peter ; E2 B,B,
D3 Raka ; E3 C,B,
D4 Lew ; E4 A,C
D5 Ken ; E5 B,C
D6 Ray ; E6 C

I want a formula that can give me exact results in E1 to E6 when D1 to D6 are looked up in A1 to A12 & B1 to B12. Note semi colons are just used to separate columns (not part of data). Please help!

Thanks
Stuntz

 

[Domenic]

First, place the code for the following custom function in a regular module....

[font=Verdana][color=darkblue]Function[/color] AConcat(a [color=darkblue]As[/color] [color=darkblue]Variant[/color], [color=darkblue]Optional[/color] Sep [color=darkblue]As[/color] [color=darkblue]String[/color] = "") [color=darkblue]As[/color] [color=darkblue]String[/color]
[color=seagreen]' Harlan Grove, Mar 2002[/color]

    [color=darkblue]Dim[/color] Y [color=darkblue]As[/color] [color=darkblue]Variant[/color]

    [color=darkblue]If[/color] [color=darkblue]TypeOf[/color] a [color=darkblue]Is[/color] Range [color=darkblue]Then[/color]
    
        [color=darkblue]For[/color] [color=darkblue]Each[/color] Y [color=darkblue]In[/color] a.Cells
            AConcat = AConcat & Y.Value & Sep
        [color=darkblue]Next[/color] Y
    
    [color=darkblue]ElseIf[/color] IsArray(a) [color=darkblue]Then[/color]
    
        [color=darkblue]For[/color] [color=darkblue]Each[/color] Y [color=darkblue]In[/color] a
            AConcat = AConcat & Y & Sep
        [color=darkblue]Next[/color] Y
        
    [color=darkblue]Else[/color]
    
        AConcat = AConcat & a & Sep
    
    [color=darkblue]End[/color] [color=darkblue]If[/color]
    
    AConcat = Left(AConcat, Len(AConcat) - Len(Sep))
    
[color=darkblue]End[/color] [color=darkblue]Function[/color]


[/font]

Then try the following worksheet formula...

E1, confirmed with CONTROL+SHIFT+ENTER, and copied down:

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

 

[Armando Montes]

Another one:

Function MyLookup(LookFor, FindCol As Range, GetCol As Range) As String
For Each c In FindCol
    If LookFor = c Then
        If MyLookup = "" Then
            MyLookup = Cells(c.Row, GetCol.Column)
        Else
            MyLookup = MyLookup & ", " & Cells(c.Row, GetCol.Column)
        End If
    End If
Next c
End Function

=MyLookup(C1,$A$1:$A$12,$B$1:$B$12)