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.
panadero13Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

rspahitzCommented:
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.)
0
panadero13Author Commented:
Vba / macro solutions are cool. I'm open to suggestions, 15 to 20 lines is no no bother. Thanks.
0
Ken ButtersCommented:
Here is some vba code.

It will re-execute each time you change something in the table.
Book2.xlsm
0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

rspahitzCommented:
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

0
panadero13Author Commented:
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
0
Ken ButtersCommented:
Adapted original solution to include multiple 'Themes' such as Fruit or Vegetables.

Each theme will print on separate line.

also added sorting

1. Sorted 'themes' so the themes would sort alphabetically
2. Sorted Food within themes, so the foods would sort alphabetically within a theme.

See attached file for solution.
Book2.xlsm
0
rspahitzCommented:
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?
0
panadero13Author Commented:
The scope of this work is still being finalised. However, my best guess is no more than 20 themes.
0
rspahitzCommented:
By converting the original sub into a function, and adding a "caller" sub, then with some minor modifications for the original, you get this:
Sub ShowReferences()
    Dim iRowCount As Integer
    iRowCount = GetItemCount()
    
    Cells(iRowCount + 2, 1).Value = IdentifyFruitReferences("Fruit")
    
    Cells(iRowCount + 4, 1).Value = IdentifyFruitReferences("Vegetables")
End Sub

Function IdentifyFruitReferences(ThemeToCheck As String) As String
    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 strTheme As String
    Dim strFood As String
    Dim iSubRow As Integer
    
    Const THEME_COLUMN As Integer = 1
    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
            strTheme = Cells(iRow, THEME_COLUMN).Value
            If strTheme = ThemeToCheck Then
                strFoodToCheck = Cells(iRow, FOOD_COLUMN).Value
                strResult = strResult & strFoodToCheck & ": "
                For iSubRow = iRow To iRowCount + 1
                    strTheme = Cells(iSubRow, THEME_COLUMN).Value
                    strFood = Cells(iSubRow, FOOD_COLUMN).Value
                    If strTheme = ThemeToCheck And 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
        End If
    Next
    
    IdentifyFruitReferences = strResult
End Function

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


As you can see, the ShowReferences Sub handles all of the calls:

    Cells(iRowCount + 2, 1).Value = IdentifyFruitReferences("Fruit")

On the right side, you call the function with the theme, and it returns the results, which I'm simply putting on the next line below the data, but you can put it anywhere you want, like even a message box:
MsgBox IdentifyFruitReferences("Fruit")
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
panadero13Author Commented:
Both solutions worked well. Many thanks for your assistance.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.