Compare Data from Sheet 1 against Sheet 2

Question

I am trying to compare column A data and column C data on sheet 1 against sheet 2 column A data and C data. If there's a match on both columns then sheet one column ("F")& row = sheet two's entire matched row. I haven't worked with Excel VBA in years and haven't been able to solve this. Initially I wanted to do this using a for each loop. Not really wanting to copy paste. I'm just trying to see if whats on sheet2 matches all the data on the source sheet1. If it matches place that data in column F on sheet1..

For Those who can't understand. I've worked on different codes for over a week and this one doesn't work either. I'm trying to see if all the data on sheet two matches that on sheet one. What I want to do:

If cells on sheet one columns A and Columns C match cells on sheet two columns A and Columns C. Then

Copy cells that match from sheet two columns A, include Columns B and C

Paste to sheet one columns F

Sub TransferCompare()
   Dim i As Long, j As Long, lastrow1 As Long, lastrow2 As Long, lastrow3 As Long
Dim OutputRow As Long
Dim bnk1, bnk3, qb1, qb3 As Variant



lastrow1 = Sheets("sheet1").Range("C" & Rows.Count).End(xlUp).row
lastrow2 = Sheets("Sheet2").Range("C" & Rows.Count).End(xlUp).row

For i = 3 To lastrow1
    bnk1 = Sheets("Sheet1").Cells(i, 1).Value
        bnk3 = Sheets("Sheet1").Cells(i, 3).Value
            OutputRow = i

For j = 3 To lastrow2
    qb1 = Sheets("Sheet2").Cells(j, 1).Value
        qb3 = Sheets("Sheet2").Cells(j, 3).Value

            If bnk1 = qb1 And bnk3 = qb3 Then
                Sheets("Sheet1").Cells(OutputRow, 6).Resize(ColumnSize:=3).Value = _
                Sheets("Sheet2").Cells(j, 1).Resize(ColumnSize:=3).Value

j = j + 1

    OutputRow = OutputRow + 1 'increase row counter

            End If

Next j
Next i
End Sub

Not sure about this ...sheet one column ("F")& row = sheet two's entire matched row.. Sheet two's row in single column of sheet1? .. But it seems you are looking for vlookup with multiple criteria which can be found on a web search. Or if you still want vba for vlookup .. stackoverflow.com/a/60904045/9808063 — Naresh
I'm afraid, both your English and your code are in need of improvement to their respective logic. On the code side, remove the Activate and Select statements (they are really quite useless) and decide whether to address a cell as Cells(i, 3) or Cells(i, "3") - there is no column "3". In the English department, please review your question and state what you wish to do if a match is found. — Variatus
If there's a match on both columns then sheet one column F and row = sheet two's entire matched row — Don Austin
@DonAustin Your code cannot work. There is a double End If. Also remove the Else if it is epmty. • And I don't get what your last comment should mean. Please try to explain everything detailed inside your original question. — Pᴇʜ
@Pᴇʜ hope you can understand now. I've included an image. Hope that helps. — Don Austin

Pᴇʜ Pᴇʜ · Accepted Answer · 2020-03-30T09:51:57

You need to insert another counter four your output row counting eg. OutputRow:

For Example

Dim OutputRow As Long
OutputRow = 3 'start output in row 3

and the following need to adjust too

If bnk1 = qb1 And bnk3 = qb3 Then
    Sheets("Sheet1").Cells(OutputRow, 6).Resize(ColumnSize:=3).Value = Sheets("Sheet2").Cells(i, 1).Resize(ColumnSize:=3).Value

    OutputRow = OutputRow + 1 'increase row counter
End If

Compare Data from Sheet 1 against Sheet 2

1 Answers