Excel list lookup, returning multiple values separated by a "|"

Question

OK...

So I have a list of products, with the product name like this: "CC973" in column A. i.e.:

A
CC969
CC972
CC973
CC975
CC976
CC977
CC978
CC996
CC997
CC998
CC999
DS009
DS022
DS046
DS088
DS096

I also have a list of product images like this in column A of another sheet "Image names'!$A$2:$A$617" (all the data is in that one column).:

A
CC967 CC968 CC969 (Packaging).jpg
CC967 CC968 CC969.jpg
CC972 CC973 (Packaging).jpg
CC972 CC973 (Rear).jpg
CC972 CC973.jpg
CC975 CC976.jpg
CC977 CC978 CC979 (Packaging).jpg
CC977 CC978 CC979.jpg
CC980 CC981 CC982 (Packaging).jpg
CC980 CC981 CC982 (Rear).jpg
CC980 CC981 CC982 (Side).jpg

What I would like to do is check for the product in the first list and return all the images that contain that product name separated by a "|".

I would like the file name with no extra text i.e. in the case just "CC972 CC973.jpg" to be returned first.
So in this example I would like the following to be returned:

CC972 CC973.jpg|CC972 CC973 (Packaging).jpg|CC972 CC973 (Rear).jpg

I'm sure this must be possible, can anyone advise a way to do that?

EDIT I have tried this:

=Lookup_concat(A2,'Image names'!$A$1:$A$617, 'Image names'!$A$1:$A$617)

But it returns #name?

I think to make this work I would have to use VBA with the following code:

Function Lookup_concat(Search_string As String, _
Search_in_col As Range, Return_val_col As Range)
Dim i As Long
Dim result As String
For i = 1 To Search_in_col.Count
If Search_in_col.Cells(i, 1) = Search_string Then
result = result & " " & Return_val_col.Cells(i, 1).Value
End If
Next
Lookup_concat = Trim(result)
End Function

however I don't think excel 2008 has a VBA editor!

I've not done spreadsheets properly since 2003!!!!

Answer 1

In your comments, you mentioned you now have access to a version of Excel which can run VBa.

This is VBa, and does what I think you want. I have included screen shots.

I have left some comments in the code, the first section you may need to update but the comments should help you out.

Remember, take a back up of your file first as there is no undo feature!

Based upon your comments, I'm using the actual worksheet names!

Option Explicit
Sub WalkThePlank()

'hear ye, only edit this top past of walk the plank
'Remember scurvy sea dog, there is no UNDO so take a copy of the file first as a back up

Dim worksheet1 As String
worksheet1 = "Image names"        'The name of the work sheet which has only codes

Dim worksheet1Column As String
worksheet1Column = "A"       'Argghh, the name of the column you use in worksheet1

Dim worksheet2 As String
worksheet2 = "LMFD products"        'The name of the worksheet with things like CC972 CC973 (Rear).jpg

Dim worksheet2Column As String
worksheet2Column = "A"       'Argghh, the name of the column you use in worksheet2

Dim resultsWorksheet As String
resultsWorksheet = "LMFD products"    'C'pan, this is where you put the results

Dim resultsWorksheetColumn As String
resultsWorksheetColumn = "C"       'Argghh, the name of the column you use in worksheet2



'hear ye, walk below and I'll feed ye to the sharks

Application.ScreenUpdating = False
Dim row As Integer
row = 2                        'The starting row with values to be looked up

Do While (Worksheets(worksheet1).Range(worksheet1Column & row).Value <> "")
    Dim result As String
    result = ""
    Dim lookupValue As String
    lookupValue = Worksheets(worksheet1).Range(worksheet1Column & row).Value

    Dim otherRow As Integer
    otherRow = 2                   'The starting row of the .jpg colum

    Dim startString As String
    Dim endString As String
    startString = ""
        endString = ""
    Do While (Worksheets(worksheet2).Range(worksheet2Column & otherRow).Value <> "")

        Dim repoValue As String
        repoValue = Worksheets(worksheet2).Range(worksheet2Column & otherRow).Value

        If (InStr(repoValue, lookupValue)) Then
        'we got treasure cap'ain
            If (InStr(repoValue, "(")) Then
                endString = Trim(endString) & Trim(repoValue) & "|"
            Else
                startString = Trim(startString) & Trim(repoValue) & "|"
            End If
        End If

        otherRow = otherRow + 1
    Loop

'check on the treasure, will we fine riches
    If (startString <> "" And endString <> "") Then
        result = Trim(startString & Left(endString, Len(endString) - 1))
        Else
        If (startString = "" And endString <> "") Then
            result = Trim(Left(endString, Len(endString) - 1))
        End If
        If (endString = "" And startString <> "") Then
            result = Trim(Left(startString, Len(startString) - 1))
        End If
    End If

    Worksheets(resultsWorksheet).Range(resultsWorksheetColumn & row).Value = result ' X Marks the spot
    row = row + 1
Loop

End Sub

My Worksheet1 (before the VBa is run)

And my Worksheet2

And the result is

How do I add VBA in MS Office?