Vlookup to return multiple results?

I have calculated the number of lines in a data set I want to bring back to a cell. Can't think of how to do it. Example:
Sheet 1
Column A / Column B \ Column C
Location Number / Number of LOBs \ Result for Formula I'm looking for
123 Anywhere St. / 3 \ LOB 1, LOB 2, LOB 3
321 Nowhere St. / 3 \ LOB 2, LOB 6

Sheet 2
Column A \ Column B
Location Number \ LOB #
123 Anywhere St. \ LOB 1
123 Anywhere St. \ LOB 2
123 Anywhere St. \ LOB 3
321 Nowhere St. \ LOB 2
321 Nowhere St. \ LOB 6
321 Nowhere St. \ LOB 2

Hello & Welcome to the Board,

This is the best I can come up with...

Change sheet names to meet your needs...

Right now it is Sheet2 as the data and Sheet3 as the output

Sub Macro1A()

    Dim Cell As Range
    Dim Data As Variant
    Dim Dict As Object
    Dim DstRng As Range
    Dim Key As Variant
    Dim Item As Variant
    Dim R As Long
    Dim Rng As Range
    Dim RngEnd As Range
    Dim Wks As Worksheet
    Dim i As Long

    Set Wks = Worksheets("Sheet2")
    Set Rng = Wks.Range("A2")
    Set RngEnd = Wks.Cells(Rows.Count, Rng.Column).End(xlUp)
    Set Rng = IIf(RngEnd.Row < Rng.Row, Rng, Wks.Range(Rng, RngEnd))

    Set DstRng = Worksheets("Sheet3").Range("A1")

    Set Dict = CreateObject("Scripting.Dictionary")
    Dict.CompareMode = vbTextCompare

    For Each Cell In Rng
        Key = Trim(Cell)
        Item = Cell.Offset(0, 1)
        If Not Dict.Exists(Key) Then
            Dict.Add Key, Item
        Else
            Dict(Key) = Dict(Key) & "|" & Item
        End If
    Next Cell

    For Each Key In Dict.Keys
        With DstRng.Offset(R, 0)
            .Value = Key
            Data = Split(Dict(Key), "|")
            .Offset(0, 1).Resize(1, UBound(Data) + 1).Value = Data
            R = R + 1
        End With
    Next Key
    Columns("B:B").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    For i = 1 To Range("A" & Rows.Count).End(xlUp).Row
        Cells(i, 2).FormulaR1C1 = "=COUNTA(RC[1]:RC[100])"
    Next i
End Sub

At this point it would help to see a sample workbook with before and after

http://www.excelforum.com/faq.php?fa...b3_attachments

Sorry haven't been around all day. A day filled with celebrating my grandson's third birthday...

Anyway, not sure I've got what it takes to make this one happen so I'll ask those much more handier with VBA than myself...

String Concatenation is a fun problem, but I say never reinvent the wheel. Mike Erickson published the best UDF for this long ago and I've been using it faithfully ever since.

CONCATIF() Explanation

I've added this UDF to the attached sheet. Now you have a new function you can put in AO2 and copy down to do this concatenation for you.