VBA Excel To Match 2 columns on sheet A with 2 columns on sheet B and return value

[itonic]

I need a VBA Excel Script To Compare columns A & B in Worksheet One Against A & B In worksheet Two If A Match Is Found Return column C from Worksheet One.
Ive can do this in excel using a formula but it is speed im after so hopefully doing via VBA would be quicker and id prefer the final output of the table to only contain values instead of formulas.
Ive done a lot of digging but cant find this piticular requirement.
Any help on this is gratefully appreciated.
This is th excel formula im currently using
{=IFERROR(INDEX(SQLData!D:D,MATCH(1,(SQLData!A:A=A2)*(SQLData!B:B=B2),0)),"0")}

I have already asked this question on SO VBA Excel To Match 2 columns on A with 2 columns on B and return value - Stack Overflow however ive had no replies for a few days.
If this gets answered on either I will be sure to close and ref the answer.

 

[Stridhan]

Im not sure I understand.
e.g. sheet1 A2&B2 = sheet2 A68&B68 = return sheet1 C2 in sheet2 C68?

 

[itonic]

Im not sure I understand.
e.g. sheet1 A2&B2 = sheet2 A68&B68 = return sheet1 C2 in sheet2 C68?

sheet 1 A68&B68 = sheet 2 A2&B2


To return the value sheet 1 C68

So it will search sheet 1 A:B and return sheet 1 C to sheet 2 C if sheet 1 matchs the value in sheet 2 A2&B2

Hope that makes sense

Cheers

 

[Stridhan]

Try this in a copy (first macro I've written in a year)
Edit sheetnames to suite your sheets, written according to your example.

Sub Stridhan()

Dim c As Range, d As Range, lr As Long
Dim ws1 As Worksheet, ws2 As Worksheet
Dim rng1 As Range, rng2 As Range

With Application
    .ScreenUpdating = False
    .Calculation = xlCalculationManual
End With

'rename Sheet1 and Sheet2
Set ws1 = ThisWorkbook.Sheets("Sheet1")
Set ws2 = ThisWorkbook.Sheets("Sheet2")

Set rng1 = ws1.Range("A2", ws1.Range("A" & ws1.Cells(Rows.Count, 1).End(xlUp).Row))
Set rng2 = ws2.Range("A2", ws2.Range("A" & ws2.Cells(Rows.Count, 1).End(xlUp).Row))

With ws2
    lr = .Cells(Rows.Count, 3).End(xlUp).Row
    If lr > 1 Then .Range("C2", "C" & lr).ClearContents
End With

For Each c In rng2
    For Each d In rng1
        If c = d Then
            If c.Offset(0, 1) = d.Offset(0, 1) Then
                c.Offset(0, 2).Value = d.Offset(0, 2).Value
                GoTo Nextone
            End If
        End If
    Next d
Nextone:
Next c

With Application
    .ScreenUpdating = True
    .Calculation = xlCalculationAutomatic
End With

End Sub

 

[itonic]

Thank you for taking the time to answer,

Is there a way it can load the data into a vba array or dictionary. sheet 1 has over 30000 rows.

The story goes..

The original spreadsheet was able to do the match in an sql database so took less then 10 seconds which the accounts team are used to, however, this access is no longer available so it must all be done within the spreadsheet now so speed is definitly paramount.

Also, could it return D as well as C?

Once again thank you.

 

[itonic]

Also, could you add comments to each part so I can fully understand each step please for future reference.

Cheers

 

[Stridhan]

Sure can make it faster, wasn't aware of how big the data was.
Making dinner atm but can make it faster after that, if not anyone else already has.

Will explain then aswell.

Stridhan

 

[itonic]

Superb, thank you

 

[Stridhan]

Sorry, was/am in a bit of a hurry so I can't explain in tonight (sweden here..)

Try this in a copy

Sub Stridhan()

Dim x As Long, i As Long, lr As Long
Dim ws1 As Worksheet, ws2 As Worksheet
Dim rng1 As Variant, rng2 As Variant
Dim vals()

'rename Sheet1 and Sheet2

Set ws1 = ThisWorkbook.Sheets("Sheet1")
Set ws2 = ThisWorkbook.Sheets("Sheet2")

rng1 = ws1.Range("A2", ws1.Range("C" & ws1.Cells(Rows.Count, 1).End(xlUp).Row)).Value
rng2 = ws2.Range("A2", ws2.Range("B" & ws2.Cells(Rows.Count, 1).End(xlUp).Row)).Value


ReDim vals(1 To UBound(rng2, 1), 1)
For x = 1 To UBound(rng2, 1)
    For i = 1 To UBound(rng1)
        If rng2(x, 1) = rng1(i, 1) Then
            If rng2(x, 2) = rng1(i, 2) Then
                vals(x, 0) = rng1(i, 3)
                GoTo Nextone
            End If
        End If
    Next i
Nextone:
Next x

ws2.Range("C2").Resize(UBound(vals, 1), 1).Value = vals

End Sub
[

 

[Stridhan]

Hope I got this right now, doing this in a learningpurpose

Sub Stridhan()

'Declaring the variables I use in the macro
Dim x As Long, i As Long, lr As Long
Dim ws1 As Worksheet, ws2 As Worksheet
Dim rng1 As Variant, rng2 As Variant
Dim vals()

'rename Sheet1 and Sheet2
'Setting the sheetnames to variables, not necessary in this case realy case but had it in the first macro which I changed into this.
Set ws1 = ThisWorkbook.Sheets("Sheet1")
Set ws2 = ThisWorkbook.Sheets("Sheet2")

'Below we assign the values from A:C sheet1 and A:B sheet2 to variables.
'Important here is if theres values in the cells or not.
'I've assumed theres values in all cells and can therefor look for the last used cell with "ws1.Range("C" & ws1.Cells(Rows.Count, 1).End(xlUp).Row)).Value"
'In sheet1 I've looked in column C and in sheet2 in column B for the last used cell.
'If the data i different we need a different approach.
rng1 = ws1.Range("A2", ws1.Range("C" & ws1.Cells(Rows.Count, 1).End(xlUp).Row)).Value
rng2 = ws2.Range("A2", ws2.Range("B" & ws2.Cells(Rows.Count, 1).End(xlUp).Row)).Value

'Below set the dimension to vals, an array. I set it to be as big as the range in sheet2 column B is because its the range we want to use in our compare.
'Instead of letting it be just a singledimension array which would suit this function I let it be a two-dimension array because of limitations of storing in a singel.
'Learned the limitations yesterday by writing this code
'The for loops steps through the values we stored from sheet1 and 2.
'First i checkes if value 1 from column A in sheet2 match any of the vals we stored from column A in sheet1, if theres a match i checkes if the value in column B match aswell.
'If thats the case it adds the value from stored from Sheet1 column C into the vals-array and then goes to the next value from Sheet2 column A by using the label "Goto Nextone:"
'which send us up to next value instead of looping through all values if we allready have a match.
'If theres no match we dont assign a value to the vals-array.
ReDim vals(1 To UBound(rng2, 1), 1)
For x = 1 To UBound(rng2, 1)
    For i = 1 To UBound(rng1)
        If rng2(x, 1) = rng1(i, 1) Then
            If rng2(x, 2) = rng1(i, 2) Then
                vals(x, 0) = rng1(i, 3)
                GoTo Nextone
            End If
        End If
    Next i
Nextone:
Next x

'When we've looked through all the vals from Sheet2 column A the vals-array will be full thou it has the same size as the vals in Sheet2.
'Below we resize the range where we want to fill in the vals-array.
'We start in C2 and resize it to be as big as the vals-array by using "ubound" that gives us the last number in the array.
ws2.Range("C2").Resize(UBound(vals, 1), 1).Value = vals

End Sub