问使用通配符和多个条件查找匹配的公式
EN

Stack Overflow用户

提问于 2020-06-08 14:09:20

回答 1查看 193关注 0票数 0

我正在尝试创建一个具有多个条件和通配符的公式，如果找到，将返回匹配的类别。我认为这将通过一个强大的索引匹配公式来完成，但我正在努力将所有标准纳入一个有效的公式中。我会用例子更好地解释...

我有两个工作表，“导入数据”和“发票编码”。公式将被输入到工作表“导入数据”中，在边上的任何列中，例如公式进入列AD。

这是“导入数据”表：

这是“发票编码”表(超过500行并且还在增长，所以这是一个小屏幕截图)：

第一步是从"Import Sheet“中查找"ACCT”编号(列O)，对照"Invoicing Coding“表单，它通常有多个匹配项。然后，第二步将检查"Import Data“表上相应的"INV”，并查看"Invoice Coding“表上的通配符发票是否匹配。返回的是“发票编码”表中的“类别”(D列)。

为了更好地解释，我将提供一个具体的示例：

在这一行中，我希望使用O列数据，它是数字50000。

在“发票编码”表上查找50000，它有很多结果。

然后在前一个屏幕截图上使用列D中的INV，这是...

查看它是否与上面“发票编码”页面截图的C列中列出的通配符部分匹配。

因此，在这个特定的例子中，来自“导入数据”表的ACCT和INV与“发票编码”表上的行178相匹配。公式的预期结果将是列D中的Category；Third Party。

以下是我到目前为止拥有的一个公式，它没有使用通配符合并部分发票匹配：

=INDEX('Invoice Coding'!A2:E514,MATCH('Import Data'!O2,'Invoice Coding'!A2:A514,0),4)

这从技术上返回一个Category，但是它没有使用INV #和带有通配符的部分INV，所以返回可能是不正确的。

我希望我的解释有道理。关于是否可以增强索引匹配公式以包含所有必需的条件查找，有什么建议吗？

顺便说一句，我会重复这个公式，稍微调整一下，也会从“发票编码”表的E栏中返回“子类别”。

提前感谢你们所有人！

excel

excel-formula

回答 1

Stack Overflow用户

回答已采纳

发布于 2020-06-23 14:33:35

我要回答我自己的问题。我们通过VBA找到了一个适合我们的解决方案。最初，我在使用VBA时犹豫不决，因为我认为与公式相比，它的运行时间要长得多，但我们的解决方案比预期的要快，大约需要30秒来遍历60,000行。

因此，我们的VBA解决方案：

'***** Declare variables to be used *****
Dim ImportWS As Worksheet, ilastrow As Long, ilooper As Long, FindArray As Variant, CodingWS As Worksheet, clastrow As Long, CodeArray As Variant, clooper As Long, acct As Long, Inv As String, matchfound As Boolean

Application.ScreenUpdating = False
Application.DisplayAlerts = False
    
    'Import Data sheet prep and storing in FindArray
    Set ImportWS = ThisWorkbook.Sheets("Import Data")
    ilastrow = ImportWS.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    ImportWS.Range("AC1") = "Category"
    ImportWS.Range("AD1") = "Sub Category"
    ImportWS.Range("AE1") = "Billing Name"
    ImportWS.Range("AC2:AE" & ilastrow).ClearContents
    FindArray = ImportWS.Range("AC1:AE" & ilastrow)

    'Storing Coding sheet data into array
    Set CodingWS = ThisWorkbook.Sheets("Invoice Coding")
    clastrow = CodingWS.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    CodeArray = CodingWS.Range("A1:F" & clastrow)
    
    With ImportWS
        
        'Looping through all rows on Import Data sheet...
        For ilooper = 2 To ilastrow
            
            'Storing GL Acct# and Invoice# for comparison against Coding sheet
            acct = Val(.Range("O" & ilooper))
            Inv = .Range("D" & ilooper)
            
            'matchfound is a boolean that is reset on each row.
            'if a match is found against the Coding sheet it is flipped to true...
            'we stop looking in the following loop...
            'and we commit the cat and subcat to the FindArray variant.
            matchfound = False
            
            'This is our looper for going through the coding table in the following loop.
            clooper = 2
            
            'While we haven't found a match for GL# and Inv...
            'and we haven't reached the end of the table...
            While matchfound = False And clooper <= clastrow
    
                'If acct# matches coding table we perform another check...
                If Val(CodeArray(clooper, 1)) = acct Then
                        
                    '...for partial match on Inv - which accounts for wildcard placement as on the table...

                    If Inv Like CodeArray(clooper, 3) Then
                       'If match is found, matchfound is true, which will stop the next iteration of the loop
                        matchfound = True
                        'and we store the cat and subcat in our FindArray
                        FindArray(ilooper, 1) = CodeArray(clooper, 4)
                        FindArray(ilooper, 2) = CodeArray(clooper, 5)
                        FindArray(ilooper, 3) = CodeArray(clooper, 6)
                    End If
                End If
                
                'If no match, check the next row on the Coding table.
                clooper = clooper + 1
    
            Wend
            
            
        'After we found a match or reached the end of the coding table...
        '...we move on to the next row on the Import Data sheet.
        Next ilooper
        
        'After looping through all rows on the Import Data sheet we commit what we found to columns AC, AD, AE.
        ImportWS.Range("AC1:AE" & ilastrow).Value = FindArray
    
    End With
    
Application.ScreenUpdating = True
Application.DisplayAlerts = True