Lookup records when there are multiple iterations of lookup value

[chrisburns2]

I have a list of items and their respective PO number and I'm trying to consolidate all items on a PO to one row. So this is what i'm starting with:

PO item
777 beans
777 rice
777 popcorn
33 beans
33 rice
22 popcorn

and this is what i want:

777 beans rice popcorn
33 beans rice
22 rice


POs have varying number of items on them and i want to have it flexible to account for as many as are needed.

Please advise.

Thanks,

Chris

 

[konew1]

you could try a pivot table with the PO numbers in the rows and the products in the column headers, and the data being count of the products

 

[mikerickson]

You could use this UDF with the formula =ConcatIf(A:A, A1, B:B, " ") dragged down.

Function ConcatIf(ByVal compareRange As Range, ByVal xCriteria As Variant, Optional ByVal stringsRange As Range, _
    Optional Delimiter As String, Optional NoDuplicates As Boolean) As String
    Dim i As Long, j As Long
    With compareRange.Parent
        Set compareRange = Application.Intersect(compareRange, Range(.UsedRange, .Range("a1")))
    End With
    If compareRange Is Nothing Then Exit Function
    If stringsRange Is Nothing Then Set stringsRange = compareRange
    Set stringsRange = compareRange.Offset(stringsRange.Row - compareRange.Row, _
    stringsRange.Column - compareRange.Column)
     
    For i = 1 To compareRange.Rows.Count
        For j = 1 To compareRange.Columns.Count
            If (Application.CountIf(compareRange.Cells(i, j), xCriteria) = 1) Then
                If InStr(ConcatIf, Delimiter & CStr(stringsRange.Cells(i, j))) <> 0 Imp Not (NoDuplicates) Then
                    ConcatIf = ConcatIf & Delimiter & CStr(stringsRange.Cells(i, j))
                End If
            End If
        Next j
    Next i
    ConcatIf = Mid(ConcatIf, Len(Delimiter) + 1)
End Function