Macros for Matching Multiple Criteras

[khaos09]

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>

 

[Jerry Sullivan]

Hi khaos09,

I've responded on your orginal thread on this topic.

http://www.mrexcel.com/forum/showth...ching-Multiple-Criteras&p=3204970#post3204970

 

[b.downey]

Here is the 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