Macros for Matching Multiple Criteras

khaos09

New Member
Joined
Jul 9, 2012
Messages
9
Hi All,
I am new to this forum and looking for some assistance for creating a macro for a process that takes me about a day to complete. Any help would be greatly appreciated. And Thank you in advance!
Below is the table:

ProviderName
TotalCharge
DateOfService
Pairing
John Smith
120.00
1/1/11
Michael Dean
130.00
2/1/11
John Smith
60.00
1/1/11
Michael Dean
50.00
2/1/11
John Smith
45
1/1/11

<TBODY>
</TBODY>

What I currently do manually is look at the ProviderName and look for the names that matches, then I look at the DateOfService and look for the dates that matches. When those two criteria are met, I would add matching numbers to the column call Pairing. What I want the macro to do is automatically add the matching numbers for me in the Pairing Column. Below is the result.
ProviderName
TotalCharge
DateOfService
Pairing
John Smith
120.00
1/1/11
1
Michael Dean
130.00
2/1/11
2
John Smith
60.00
1/1/11
1
Michael Dean
50.00
2/1/11
2
John Smith
45
1/1/11
1

<TBODY>
</TBODY>
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

Jerry Sullivan

MrExcel MVP
Joined
Mar 18, 2010
Messages
8,787
Hi and Welcome to the Board,

You can do this with formulas instead of a macro.

For large datasets, it's best to add a helper column to concatentate the multiple criteria into a string.

Paste the formulas shown into D2 and E2 and then copy down.
Excel Workbook
ABCDE
1ProviderNameTotalChargeDateOfServiceMatch CodePairing
2John Smith1201/1/2011John Smith|405441
3Michael Dean1302/1/2011Michael Dean|405752
4John Smith601/1/2011John Smith|405441
5Michael Dean502/1/2011Michael Dean|405752
6John Smith451/1/2011John Smith|405441
Sheet


Also, this approach assumes that a single item that doesn't have any matches will stil be counted as a "pair" or better put, a unique combination.
 

ZAX

Well-known Member
Joined
Jul 5, 2012
Messages
715
Hi khaos09,

this code works perfectly but it works only with Excel 2007 and Excel 2010 .
and by the way JS411, your method takes a lot of time to finish, whereas the code is faster and easier .

ZAX

Code:
Sub MULTIPLE_CRITERIA()
Dim COL As Range, Cell As Range
Set COL = Range("D2:D" & Right(ActiveCell.SpecialCells(xlCellTypeLastCell).Address(1, 0, xlA1), _
Len(ActiveCell.SpecialCells(xlCellTypeLastCell).Address(1, 0, xlA1)) - InStr(1, _
ActiveCell.SpecialCells(xlCellTypeLastCell).Address(1, 0, xlA1), "$")))
Range("D1").Formula = "PAIRS"
For Each Cell In COL
Cell.Select
ActiveCell.Formula = Application.CountIfs(Range("A:A"), _
Range("A" & Right(ActiveCell.Address(1, 0), Len(ActiveCell.Address(1, 0)) - InStr(1, _
ActiveCell.Address(1, 0), "$"))), _
Range("C:C"), Range("C" & Right(ActiveCell.Address(1, 0), _
Len(ActiveCell.Address(1, 0)) - InStr(1, ActiveCell.Address(1, 0), "$"))))
If Cell.Value = 0 Then Cell.Delete
Next Cell
Range("A1").Select
End Sub
 

khaos09

New Member
Joined
Jul 9, 2012
Messages
9
Thank you to both of you! This is awesome to know that there are very knowledgeable people out there that is willing to help others.

Zax,
I ran the code and it takes seconds to populate the "Pairing" field. What took me 8 hours to do, I am able to complete in seconds!
 

Jerry Sullivan

MrExcel MVP
Joined
Mar 18, 2010
Messages
8,787

ADVERTISEMENT

ZAX, Nice of you to offer a VBA approach. As much as l like VBA, sometimes it's helpful to offer a formula-based alternative when a problem can be solved with formulas.

khaos09, Did ZAX's VBA code provide the desired result? It looks like it provides a count of each unique combination instead of numbering them sequentiallly like your example.

Here is some code that does what's described in your original post...

Code:
Sub Label_Unique_Combinations()
    Dim lRow As Long
    
    Application.ScreenUpdating = False
 
    With ActiveSheet
        lRow = .Cells(Rows.Count, "A").End(xlUp).Row
        With .Range("D2:D" & lRow)
            .Offset(0, 1).FormulaR1C1 = "=RC1 &""|""&RC3"
            .FormulaR1C1 = "=IF(COUNTIF(R2C[1]:RC[1],RC[1])=1," _
                & "MAX(R1C:R[-1]C)+1,LOOKUP(RC[1],R2C[1]:RC[1],R2C:RC))"
            .Value = .Value
            .Offset(0, 1).ClearContents
        End With
    End With
    Application.ScreenUpdating = True
End Sub
 
Last edited:

khaos09

New Member
Joined
Jul 9, 2012
Messages
9
JS411,
I just analyzed the data using ZAX's code and you are correct it only provides the count. I went back to the excel and used your formula based logic and it worked well. I will try this new code out.

Thanks again!
 

Jerry Sullivan

MrExcel MVP
Joined
Mar 18, 2010
Messages
8,787
Hi again khaos09,

Glad that you spotted that my formula was giving some incorrect results.
The Lookup function wasn't correct because the list is not sorted.
Here is code with a corrected formula using Index-Match.

Code:
Sub Label_Unique_Combinations()
    Dim lRow As Long
    
    Application.ScreenUpdating = False
 
    With ActiveSheet
        lRow = .Cells(Rows.Count, "A").End(xlUp).Row
        With .Range("D2:D" & lRow)
            .Offset(0, 1).FormulaR1C1 = "=RC1 &""|""&RC3"
            .FormulaR1C1 = "=IF(COUNTIF(R2C[1]:RC[1],RC[1])=1," _
                & "MAX(R1C:R[-1]C)+1,INDEX(R1C:R[-1]C,MATCH(RC[1],R1C[1]:R[-1]C[1],0)))"
            .Value = .Value
            .Offset(0, 1).ClearContents
        End With
    End With
    Application.ScreenUpdating = True
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,130,258
Messages
5,641,151
Members
417,195
Latest member
Vishal kumar

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
Top