Excel 2007 : vlookup combined with concatenation?

2ASH Ashfield ASHF 2ALB 3831 1 14750 Leeton (A) Berrigan (A) Alpine (S) Indigo (S) Berrigan (A) Moira (S)
2ASH Ashfield ASHF 2ALB 1605 1 10650 Berrigan (A) Alpine (S) Indigo (S) Berrigan (A) Moira (S)
2ASH Ashfield ASHF 2ALB 1964 2 20110 Alpine (S) Indigo (S) Berrigan (A) Moira (S)
2ASH Ashfield ASHF 2ALB 1565 2 23350 Indigo (S) Berrigan (A) Moira (S)
2ASH Ashfield ASHF 2ALB 3708 1 10650 Berrigan (A) Moira (S)
2ASH Ashfield ASHF 2ALB 3708 2 24900 Moira (S)

From the above list, collumn 8 shows the australian councils that are associated with 2ASH.
I would like to be able to do a vlookup for 2ash and get all the councils associated with 2 ASH in one cell on another worksheet.

I have lined up horizontally using a simple formula and then tried to concatenate several vlookups in one cell:
vlookup(2ash, data, row1) & vlookup(2ash, data, row2), & vlookup(2ash, data, row3), etc., but to no avail.

There must be a better solution?

Many thanks in advance,

Erik

here's an example source and desired output page, many thanks in advance

Hi Vulpen,

I can't take much credit for this, its something another user produced for a different problem and I adjusted slightly for yours:

Create a macro and put this in the code:

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 on your Output sheet:
Put next to each entry (i.e. 6CAN)
=aconcat(IF(Source1!B1:Source1!B99="6CAN",Source1!I1:Source1!I99,""))
CTRL+SHIFT+ENTER (don't just ENTER)

You can further automate it by say if Output!B6 is 6CAN, put formula in Output!C6 as
=aconcat(IF(Source1!B1:Source1!B99=C6,Source1!I1:Source1!I99,""))
CTRL+SHIFT+ENTER (don't just ENTER)

That way you can copy up/down next to your code column and get all the desired outcomes.

Hope that helps!

-
Remember to mark the thread SOLVED if your problem has been taken care of and to award rep points via the star-like icon