☰

Excel 列表查找，返回以“|”分隔的多个值

tag-icon • tag-icon microsoft-excel mac microsoft-office-2008
Excel 列表查找，返回以“|”分隔的多个值

好的...

所以我有一个产品列表，其中产品名称如下：“CC973”在 A 列中。即：

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

我在另一张表“图像名称！$A$2:$A$617”的 A 列中也有这样的产品图像列表（所有数据都在那一列中）：

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

我想要做的是在第一个列表中检查产品并返回所有包含该产品名称并以“|”分隔的图像。

我希望首先返回不带额外文本的文件名，即在这种情况下只返回“CC972 CC973.jpg”。
因此，在此示例中，我希望返回以下内容：

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

我确信这一定是可能的，有人可以建议一种方法来做到这一点吗？

编辑 我尝试过这个：

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

但它返回#name?

我认为要完成这项工作我必须使用 VBA 和以下代码：

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

但是我不认为 Excel 2008 有 VBA 编辑器！

自 2003 年以来我就没有正确地做过电子表格了！

答案1

在您的评论中，您提到您现在可以访问可以运行 VBa 的 Excel 版本。

这是 VBa，我认为它能满足您的要求。我已附上屏幕截图。

我在代码中留下了一些注释，第一部分你可能需要更新，但这些注释应该会对你有所帮助。

请记住，请先备份您的文件，因为没有撤消功能！

根据您的评论，我使用实际的工作表名称！

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

我的工作表 1（在运行 VBa 之前）

在此处输入图片描述

我的 Worksheet2

在此处输入图片描述

结果是

在此处输入图片描述

如何在 MS Office 中添加 VBA？

相关内容