Compare column values in two sheets and copy data

[gmoorthy]

I have 2 huge excel spreadsheet sheet 1 and sheet 2

I need to compare columnA in Sheet2 with Column A in sheet1 and if a match is found i need to copy the entire row from Sheet1 to sheet3. I am not used to macro but i guess that will do it. Any help is appreciated

 

[SkipVought]

hi,

Please post VBA questions in forum707.

Please post your current code as well, explaining where you are having problems: I tried THIS and THIS is what happened.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[zelgar]

Even though Skip is right about the forum, here's a macro that should work. (Note: this macro only grabs the data from col 1 to 20 (A - T), if you need more change the 20 in the macro line "Range(Sheet1.Cells(iRow1, 1), Sheet1.Cells(iRow1, [highlight #FCE94F]20[/highlight])).Copy _ " to the numeric value of the column (e.g. CD would be 26*3+4 or 82))

Sub gmoorthy()
'
Dim iRow1 As Integer, iRow2 As Integer, iRow3 As Integer
iRow1 = 2   ' Starting Row of Data in Sheet1
iRow2 = 2   ' Starting Row of Data in Sheet2
iRow3 = 2   ' Starting Row to place data in Sheet3
'
Do Until IsEmpty(Sheet1.Cells(iRow1, 1))
    Do Until IsEmpty(Sheet2.Cells(iRow2, 1))
        If Cells(iRow1, 1) = Cells(iRow2, 1) Then
            Range(Sheet1.Cells(iRow1, 1), Sheet1.Cells(iRow1, 20)).Copy _
                Destination:=Sheet3.Cells(iRow3, 1)
            iRow3 = iRow3 + 1
        End If
        iRow2 = iRow2 + 1
    Loop
    iRow2 = 2
    iRow1 = iRow1 + 1
Loop
End Sub

 

[gmoorthy]

Thanks for the macro but when i try to run this i am getting a error

Run - time error 424 object required

Range(Sheet1.Cells(iRow1, 1), Sheet1.Cells(iRow1, 20)).Copy _
Destination:=Sheet3.Cells(iRow3, 1)

 

[SkipVought]

Geez the code worked for me.

Please describe what data you have in row 2 in Sheet1 & Sheet2 m(CodeName, that is)

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[SkipVought]

...and do you have a sheet CodeName Sheet3?

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[zelgar]

The only problem I have with the macro is that it doesn't catch duplicates if I only run the macro once. If I run it a second time, it will catch duplicates.

 

[zelgar]

When I mean duplicates, I mean if Sheet2 has more than 1 cell that matches the data in Sheet1. The macro should copy the duplicates, but doesn't if I only run the macro once. If I run it a 2nd time with data in Sheet3, it catches the duplicates and copies them.

 

[SkipVought]

I need to compare columnA in Sheet2 with Column A in sheet1

This is an ambiguous requirement.

It could mean that if a value in a row in column A in Sheet2 matches the same value on Sheet1 column A in ANY row OR only in the SAME row as Sheet2.

Please clarify.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[gmoorthy]

The first column are numbers i also checked the format in both sheets seems to be fine. In the meantime i tried something like this below and it is copying only the first row which is the header.

Sub CopyMatch()

Dim StartingScreenUpdateValue As Boolean
Dim StartingEventsValue As Boolean
Dim StartingCalculations As XlCalculation

With Application
StartingScreenUpdateValue = .ScreenUpdating
StartingEventsValue = .EnableEvents
StartingCalculations = .Calculation
.ScreenUpdating = False
.EnableEvents = False
.Calculation = xlCalculationManual
End With


Dim varTestValues As Variant
Dim sh1 As Worksheet
Dim sh2 As Worksheet
Dim sh3 As WorkSheet

Set sh1 = Sheets("Sheet1")
Set sh2 = Sheets("Sheet2")
Set sh3 = Sheets("Sheet3")

With sh2
varTestValues = .Range("A1", .Range("A" & .Rows.Count).End(xlUp))
End With

With sh1
.Range("A1", .Range("A" & .Rows.Count).End(xlUp)) _
.AutoFilter Field:=1, Criteria1:=Application.Transpose(varTestValues), Operator:=xlFilterValues

.Range("A2", sh1.Range("A" & .Rows.Count).End(xlUp)) _
.SpecialCells(xlCellTypeVisible).EntireRow.Copy sh3.Range("A1")

.AutoFilterMode = False
End With

With Application
.ScreenUpdating = StartingScreenUpdateValue
.EnableEvents = StartingEventsValue
.Calculation = StartingCalculations
End With

End Sub

 

[gmoorthy]

Sorry I should have been more specific

for your comment
It could mean that if a value in a row in column A in Sheet2 matches the same value on Sheet1 column A in ANY row OR only in the SAME row as Sheet2.


The Value in a row in ColumnA in sheet 2 matches the same value on Sheet1 column A in any ROW.

 

[SkipVought]

it is copying only the first row

TRUE! Er, uh, where's your LOOP?

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[zelgar]

I've noticed a ERROR in the above macro. Change the IF statement to the following:

If Sheet1.Cells(iRow1, 1) = Sheet2.Cells(iRow2, 1) Then

Right now, it's only looking at the data in Sheet1

 

[SkipVought]

Well I take that back. Your code DID copy all the rows from Sheet1 that matched ANY row in Sheet2 to Sheet3 WITHOUT the heading.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[gmoorthy]

Still getting the same error

Range(Sheet1.Cells(iRow1, 1), Sheet1.Cells(iRow1, 20)).Copy _
Destination:=Sheet3.Cells(iRow3, 1)

 

[zelgar]

gmoorthy, probably the best thing is to ADD a new sheet and change the Destination to the new sheet. For some reason, Excel always considers the 1st sheet Sheet1 even if you've renamed it. Prossibly what has happened is that you've added & deleted sheets and renamed sheets & Excel doesn't recognize Sheet3 as Sheet3 (typical twisted Microsoft logic)