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 or query 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:</SPAN></SPAN>

A</SPAN></SPAN>
B</SPAN></SPAN>
C</SPAN></SPAN>
E</SPAN></SPAN>
1</SPAN></SPAN>
ProviderName</SPAN></SPAN>
TotalCharge</SPAN></SPAN>
DateOfService</SPAN></SPAN>
Pairing</SPAN></SPAN>
2</SPAN></SPAN>
John Smith</SPAN></SPAN>
120</SPAN></SPAN>
1/1/2011</SPAN></SPAN>
3</SPAN></SPAN>
Michael Dean</SPAN></SPAN>
130</SPAN></SPAN>
2/1/2011</SPAN></SPAN>
4</SPAN></SPAN>
John Smith</SPAN></SPAN>
60</SPAN></SPAN>
1/1/2011</SPAN></SPAN>
5</SPAN></SPAN>
Michael Dean</SPAN></SPAN>
50</SPAN></SPAN>
2/1/2011</SPAN></SPAN>
6</SPAN></SPAN>
John Smith</SPAN></SPAN>
45</SPAN></SPAN>
1/1/2011</SPAN></SPAN>

<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 called Pairing. What I want the macro to do is automatically add the matching numbers for me in the Pairing Column. Below is the result.</SPAN></SPAN>

A</SPAN></SPAN>
B</SPAN></SPAN>
C</SPAN></SPAN>
E</SPAN></SPAN>
1</SPAN></SPAN>
ProviderName</SPAN></SPAN>
TotalCharge</SPAN></SPAN>
DateOfService</SPAN></SPAN>
Pairing</SPAN></SPAN>
2</SPAN></SPAN>
John Smith</SPAN></SPAN>
120</SPAN></SPAN>
1/1/2011</SPAN></SPAN>
1</SPAN></SPAN>
3</SPAN></SPAN>
Michael Dean</SPAN></SPAN>
130</SPAN></SPAN>
2/1/2011</SPAN></SPAN>
2</SPAN></SPAN>
4</SPAN></SPAN>
John Smith</SPAN></SPAN>
60</SPAN></SPAN>
1/1/2011</SPAN></SPAN>
1</SPAN></SPAN>
5</SPAN></SPAN>
Michael Dean</SPAN></SPAN>
50</SPAN></SPAN>
2/1/2011</SPAN></SPAN>
2</SPAN></SPAN>
6</SPAN></SPAN>
John Smith</SPAN></SPAN>
45</SPAN></SPAN>
1/1/2011</SPAN></SPAN>
1</SPAN></SPAN>

<TBODY>
</TBODY>
 

Some videos you may like

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

b.downey

Active Member
Joined
Oct 16, 2011
Messages
484
Here is the code

Code:
Option Explicit

Sub Process()
    Dim Wb As Workbook
    Dim Ws As Worksheet
    Dim RowNo As Integer
    Dim Key As String
    
    Dim Rec() As String
    ReDim Rec(0)
    
    Set Wb = ThisWorkbook
    Set Ws = Wb.Sheets("Sheet1")
    For RowNo = 2 To Ws.Cells(Rows.Count, "A").End(xlUp).Row
        Key = Ws.Cells(RowNo, "A") & "~" & Ws.Cells(RowNo, "C")
        Ws.Cells(RowNo, "D") = FindIdx(Key, Rec)
    Next RowNo
End Sub
Function FindIdx(Key As String, Rec() As String) As Integer
    Dim I As Integer
    
    For I = 1 To UBound(Rec)
        If Rec(I) = Key Then
            FindIdx = I
            Exit Function
        End If
    Next I
    
    ReDim Preserve Rec(I)
    Rec(I) = Key
    FindIdx = I
End Function
 

Watch MrExcel Video

Forum statistics

Threads
1,128,175
Messages
5,629,170
Members
416,370
Latest member
Lgathana

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