VBA for multiple matches with multiple criteria from different worksheet

O

ouvay

Board Regular
Joined
Jun 9, 2022
Messages
131
Office Version
  1. 2019
Platform
  1. Windows
I'm working on this bit of code which returns the first match as the result.

Looking for some guidance or help to rewrite this to account for multiple matches - Criteria 2 is quite important as it changes later on in the data and needs to be accounted for (I feel I had to mention this as the data snippets I've provided only show a single possibility)

Here is the code
VBA Code: 
Sub Tester2()

    Dim v, shtDest, shtSrc, a1, a2, i

    Set shtDest = ThisWorkbook.Sheets("Sheet1")
    Set shtSrc = ThisWorkbook.Sheets("Sheet2")
    
    With ActiveSheet.ListObjects(1).DataBodyRange
    For i = 2 To .Rows.Count

    a1 = "'" & shtDest.Name & "'!" & shtDest.Cells(i, 7).Address(False, False)
    a2 = "'" & shtDest.Name & "'!" & shtDest.Cells(i, 9).Address(False, False)

    Debug.Print a1, a2

    v = shtSrc.Evaluate("MATCH(" & a1 & "&" & a2 & ",D2:D29&H2:H29,0)")

    If Not IsError(v) Then
        shtDest.Cells(i, 8).Value = shtSrc.Range("J2:J29").Cells(v).Value
    End If
    Next i
    End With
End Sub


Here are some image references to assist in understanding the problem.

WRONG RESULT (Result is first match over all columns)
1655088282369.png



EXPECTED RESULT (Multiple matches returned)
1655088365524.png


Here is a snippet of the data sheet (sheet2)
1655088501005.png


Would appreciate any help I could get on the matter.

TIA
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
Sort by date Sort by votes
Check if the following is what you need.

VBA Code: 
Sub MultipleMatches()
  Dim c As Range
  Dim dic As Object
  Dim n As Long
  
  Set dic = CreateObject("Scripting.Dictionary")
  With Sheets("Sheet2")
    For Each c In .Range("D2", .Range("D" & Rows.Count).End(3))
      n = WorksheetFunction.CountIf(.Range("D2:D" & c.Row), c.Value)
      dic(n & "|" & c.Value) = .Range("J" & c.Row).Value
    Next
  End With
  
  With Sheets("Sheet1")
    For Each c In .Range("G2", .Range("G" & Rows.Count).End(3))
      n = WorksheetFunction.CountIf(.Range("G2:G" & c.Row), c.Value)
      c.Offset(, 1).Value = dic(n & "|" & c.Value)
    Next
  End With
End Sub

NOTE XL2BB:
For the future, it would help greatly if you could give us the sample data in a form that we can copy to test with, rather that a picture.
MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in
Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.
 
Upvote 0
DanteAmor said:
Check if the following is what you need.

VBA Code: 
Sub MultipleMatches()
  Dim c As Range
  Dim dic As Object
  Dim n As Long
 
  Set dic = CreateObject("Scripting.Dictionary")
  With Sheets("Sheet2")
    For Each c In .Range("D2", .Range("D" & Rows.Count).End(3))
      n = WorksheetFunction.CountIf(.Range("D2:D" & c.Row), c.Value)
      dic(n & "|" & c.Value) = .Range("J" & c.Row).Value
    Next
  End With
 
  With Sheets("Sheet1")
    For Each c In .Range("G2", .Range("G" & Rows.Count).End(3))
      n = WorksheetFunction.CountIf(.Range("G2:G" & c.Row), c.Value)
      c.Offset(, 1).Value = dic(n & "|" & c.Value)
    Next
  End With
End Sub

NOTE XL2BB:
For the future, it would help greatly if you could give us the sample data in a form that we can copy to test with, rather that a picture.
MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in
Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.
Click to expand...
Hi Dante,

Thanks for the response!

Oh! Thats very useful! The info about XL2BB - I'll be sure to use it for the next time.. its surely better than using images.

I ran the code snippet several times while troubleshooting and testing but so far I haven't had any results.. It returns blank cells unfortunately
 
Upvote 0
ouvay said:
I ran the code snippet several times while troubleshooting and testing but so far I haven't had any results.. It returns blank cells unfortunately
Click to expand...
You could give the examples you are working with, use the XL2BB tool minisheet.
 
Upvote 0
hi,

I hope this works!

Here is the sheet I am running the code on

Test Assistant.xlsm
L
102528811
Sheet1
Cell Formulas
RangeFormula
L10L10=IF(OR([@[Criteria 1]]="",[@[Buyer ]]="DELAY",[@[Buyer ]]="Tokurei",[@[Buyer ]]="Cancelled",[@[Buyer ]]="NO PAYMENT",ISNUMBER([@[Buyer ]])),"",IFERROR(INDEX(Chart[SponsorID],MATCH(0,IF(Chart[BuyerID]=[@[Criteria 1]],COUNTIFS(Sheet1!$L$6:Sheet1!L9,Chart[SponsorID],Sheet1!$K$6:Sheet1!K9,Chart[BuyerID],Sheet1!$I$6:Sheet1!I9,Chart[1:First/2:Payoff]),""),0)),""))
Press CTRL+SHIFT+ENTER to enter array formulas.



And here is the data table - I've reduced the data sample and renamed the relevant headers to make it easier to understand



I apologize in advance if this isn't how you were expecting this data.. also I appreciate the effort you are putting into this :)
 
Upvote 0
DanteAmor said:
You could give the examples you are working with, use the XL2BB tool minisheet.
Click to expand...

Here is the sheet I am running the code on

List PasteCriteria 2StatusCriteria 1ResultCommissionNet Paid
2530580125305802528820
2530874125305802528817
125305802528814
125305802528811
125305802528810
125305802528803
125305802513722
125308742530730
125308742528814
125308742528811
125308742528810



And here is the data table - I've reduced the data sample and renamed the relevant headers to make it easier to understand

SalesDateSoldAtSlip#1st Criteria RangeBuyerProductIDProduct2nd Criteria RangeCommisionType(1:8P,2:CS,3:6AEdu)Result RangeSponsor
253073012528814
253073012528811
253073012528810
253073012528803
253073012513722
253058012528820
253058012528817
253058012528814
253058012528811
253058012528810
253058012528803
253058012513722
253087412530730
253087412528814
253087412528811
253087412528810
253087412528803
253087412513722
253088712530654


Sorry for my blunder on the previous post.
 
Upvote 0
What I need to see are the columns, in your examples I don't see the excel columns, I can't determine which column each data is in.
You can try again to put the examples correctly.

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.
 
Upvote 0
DanteAmor said:
What I need to see are the columns, in your examples I don't see the excel columns, I can't determine which column each data is in.
You can try again to put the examples correctly.

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.
Click to expand...
Here are the ranges

Sheet1 (VBA result)

Test Assistant.xlsm
AIJKLNO
5
6List PasteCriteria 2StatusCriteria 1Result ColumnCommissionNet Paid
725305801OK25305802528820 
825308741OK25305802528817 
91OK25305802528814 
101OK25305802528811 
111OK25305802528810 
121OK25305802528803 
131OK25305802513722 
141OK25308742530730 
151OK25308742528814 
161OK25308742528811 
171OK25308742528810 
181OK25308742528803 
191OK25308742513722 
20  
21  
22  
23  
24  
25  
26  
27  
28  
29  
30  
31  
32  
33  
34  
35  
36  
37
Sheet1
Cell Formulas
RangeFormula
L7:L36L7=IF(OR([@[Criteria 1]]="",[@[Buyer ]]="DELAY",[@[Buyer ]]="Tokurei",[@[Buyer ]]="Cancelled",[@[Buyer ]]="NO PAYMENT",ISNUMBER([@[Buyer ]])),"",IFERROR(INDEX(Chart[Result Range],MATCH(0,IF(Chart[Critera 1 Range]=[@[Criteria 1]],COUNTIFS(Sheet1!$L$6:Sheet1!L6,Chart[Result Range],Sheet1!$K$6:Sheet1!K6,Chart[Critera 1 Range],Sheet1!$I$6:Sheet1!I6,Chart[Criteria 2 Range]),""),0)),""))
N7:N36N7=IF([@[Sponsor Aide]]=FALSE,"",IFERROR(IF(AND(OR([@[Buyer ]]="CANCELLED",[@[Buyer ]]="Delay"),OR([@Sponsor]="Tokurei",[@[Buyer ]]="NO PAYMENT",[@Sponsor]="NO PAYMENT",ISNUMBER([@[Buyer ]]),[@Sponsor]="Delay",[@Sponsor]="Cancelled",ISNUMBER([@Sponsor]))),"",IF([@Sponsor]="Tokurei",INDEX(#REF!,MATCH([@[Criteria 1]]&[@[Result Column]]&[@[Criteria 2]],Chart[Critera 1 Range]&Chart[Result Range]&Chart[Criteria 2 Range],0)),INDEX(#REF!,MATCH([@[Criteria 1]]&[@[Result Column]]&[@[Criteria 2]],Chart[Critera 1 Range]&Chart[Result Range]&Chart[Criteria 2 Range],0)))),""))
Press CTRL+SHIFT+ENTER to enter array formulas.



Data Sheet

Test Assistant.xlsm
ABCDEFGHIJ
1SalesDateSoldAtSlip#Critera 1 RangeBuyerProductIDProductCriteria 2 RangeCommisionType(1:8P,2:CS,3:6AEdu)Result Range
2253073012528814
3253073012528811
4253073012528810
5253073012528803
6253073012513722
7253058012528820
8253058012528817
9253058012528814
10253058012528811
11253058012528810
12253058012528803
13253058012513722
14253087412530730
15253087412528814
16253087412528811
17253087412528810
18253087412528803
19253087412513722
20253088712530654
21
Commission Chart
 
Upvote 0
Here is the problem, in your OP the starting column is column G.
1655217266744.png


But now your starting column is column K.
1655217457274.png


Here the macro with the adjustment of names of sheets and columns:

Rich (BB code): 
Sub MultipleMatches()
  Dim c As Range
  Dim dic As Object
  Dim n As Long
  
  Set dic = CreateObject("Scripting.Dictionary")
  With Sheets("Commission Chart")
    For Each c In .Range("D2", .Range("D" & Rows.Count).End(3))
      n = WorksheetFunction.CountIf(.Range("D2:D" & c.Row), c.Value)
      dic(n & "|" & c.Value) = .Range("J" & c.Row).Value
    Next
  End With
  
  With Sheets("Sheet1")
    For Each c In .Range("K2", .Range("K" & Rows.Count).End(3))
      n = WorksheetFunction.CountIf(.Range("K2:K" & c.Row), c.Value)
      c.Offset(, 1).Value = dic(n & "|" & c.Value)
    Next
  End With
End Sub
 
Upvote 0
Solution
DanteAmor said:
Here is the problem, in your OP the starting column is column G.
View attachment 67057

But now your starting column is column K.
View attachment 67058

Here the macro with the adjustment of names of sheets and columns:

Rich (BB code): 
Sub MultipleMatches()
  Dim c As Range
  Dim dic As Object
  Dim n As Long
 
  Set dic = CreateObject("Scripting.Dictionary")
  With Sheets("Commission Chart")
    For Each c In .Range("D2", .Range("D" & Rows.Count).End(3))
      n = WorksheetFunction.CountIf(.Range("D2:D" & c.Row), c.Value)
      dic(n & "|" & c.Value) = .Range("J" & c.Row).Value
    Next
  End With
 
  With Sheets("Sheet1")
    For Each c In .Range("K2", .Range("K" & Rows.Count).End(3))
      n = WorksheetFunction.CountIf(.Range("K2:K" & c.Row), c.Value)
      c.Offset(, 1).Value = dic(n & "|" & c.Value)
    Next
  End With
End Sub
Click to expand...
Oh! I'm sorry about that! Careless oversight on my part :(

Thank you so much for the new code! It works brilliantly!!
I'll mark this solved now.. once again, thank you! :biggrin:
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Solved
VBA Code for lookup with multiple criteria
Replies
7
Views
423
kvsrinivasamurthy
K
N
  • Solved
COUNTIF With Multiple Criteria From Different Ranges
Replies
3
Views
591
Alex Blakenburg
A
S
  • Solved
VBA VLOOKUP with multiple conditions
Replies
3
Views
1K
Alex Blakenburg
A
A
  • Question Question
Excel - looking up data with multiple criteria from table/array, date must be same or nearest before the target cell
Replies
8
Views
561
Peter_SSs
Peter_SSs
C
  • Solved
Lookup multiple criteria and find match within mixed alpha numerical cells and return the value matched
2 3
Replies
21
Views
879
Chrissy_M
C

Forum statistics

Threads
1,223,884
Messages
6,175,177
Members
452,615
Latest member
bogeys2birdies

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top