asked on

Lookup multiple values, return multiple values

It would be greatly appreciated if someone could help identify a solution the best way to summarise the sample data below by theme, food and reference in the format listed in the Output required. Many thanks

Sample Data:

Col1 Col2 Col3
Theme Food Reference
Fruit Apples 1
Fruit Oranges 4
Fruit Grapes 6
Fruit Apples 2
Fruit Oranges 5
Fruit Plums 7
Fruit Oranges 10

Output required:

Apple: 1, 2. Oranges: 4, 5,10. Grapes: 6. Plums: 7.

rspahitz

There is no simple way to do this without a macro.
To handle it in Excel directly, you'd need to build a set of repeating functions that search for each of the food items, then repeatedly goes through locating all individual items until there are non, then go on to the next. To build a single formula to do that is impractical at best, and doing it across a collection of cells would still leave you with an unwieldy formula.

Would you like a macro-based solution?
(It can be done in about 15-20 lines -- or optimally in just a few lines but then it's much less readable.)

panadero13

ASKER

Vba / macro solutions are cool. I'm open to suggestions, 15 to 20 lines is no no bother. Thanks.

Ken Butters

Here is some vba code.

It will re-execute each time you change something in the table.
Book2.xlsm

rspahitz

To make things more readable, I added a few more lines but this should do it for you:

Sub IdentifyFruitReferences()
    Dim strFoodToCheck As String
    Dim iReference As Integer
    Dim iRow As Integer
    Dim iRowCount As Integer
    Dim strResult As String
    Dim bChecked() As Boolean
    Dim strFood As String
    Dim iSubRow As Integer
    
    Const FOOD_COLUMN As Integer = 2
    Const REFERENCE_COLUMN As Integer = 3
    
    iRowCount = GetItemCount()
    ReDim bChecked(iRowCount)
    strResult = ""
    For iRow = 2 To iRowCount + 1
        If Not bChecked(iRow - 2) Then
            strFoodToCheck = Cells(iRow, FOOD_COLUMN).Value
            strResult = strResult & strFoodToCheck & ": "
            For iSubRow = iRow To iRowCount + 1
                strFood = Cells(iSubRow, FOOD_COLUMN).Value
                If strFood = strFoodToCheck Then
                    bChecked(iSubRow - 2) = True
                    If iSubRow > iRow Then
                        strResult = strResult & ", "
                    End If
                    iReference = Cells(iSubRow, REFERENCE_COLUMN).Value
                    strResult = strResult & iReference
                End If
            Next
            strResult = strResult & ". "
        End If
    Next
    Cells(iRowCount + 2, 1).Value = strResult
End Sub

Private Function GetItemCount() As Integer
    Dim iRow As Integer
    Dim strFood As String
    
    Const FOOD_COLUMN As Integer = 2

    iRow = 2
    Do
        strFood = Cells(iRow, FOOD_COLUMN).Value
        If strFood = "" Then
            Exit Do
        End If
        
        iRow = iRow + 1
    Loop
    
    GetItemCount = iRow - 2
End Function

Open in new window

panadero13

ASKER

Excellent, the solutions work well.

One further question, if the Theme column contained value - Fruit and another value, say Vegetables. Is it possible to get the output for Fruit and Vegetables on different lines:

Apple: 1, 2. Oranges: 4, 5,10. Grapes: 6. Plums: 7.

Carrots: 10,12. Potatoes: 20, 21. Pumpkin: 30, 35, 37

SOLUTION

Ken Butters

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

rspahitz

To create two different lines, you'll probably want to run the procedure twice, once for each Theme. Pass in the theme and have it only check those rows that use the theme.

Is this going to extend to an unlimited number of themes?

panadero13

ASKER

The scope of this work is still being finalised. However, my best guess is no more than 20 themes.

ASKER CERTIFIED SOLUTION

rspahitz

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

panadero13

ASKER

Both solutions worked well. Many thanks for your assistance.