Help Comparing Two Worksheets...

Chi_Archon

New Member
Joined
Sep 7, 2007
Messages
5
I am new to excel and have been trying to write a VB macro to compare two spreadsheets. Here is what I need help achieving....

Compare Sheet1 and Sheet2. Data exists in rows that have a unique identifier in one column.

If unique ID (with row of data) exists in Sheet1 and NOT Sheet2, add row from Sheet1 to Sheet3 and mark "REMOVE" in new column next to row.

If unique ID (with row of data) exists in Sheet2 and NOT in Sheet1, add row from Sheet2 to Sheet 3 and mark "ADD" in new column next to row.

If unique ID (with row of data) exists in both Sheet1 and Sheet2, take row from Sheet2 and place in Sheet3 and mark "UPDATE" in new column next to row.


Can anyone be of help or does someone have sample code to achieve this? Thanks so much for your help. This forum is great.

:biggrin:
 

Some videos you may like

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,904
I doubt that you will get anyone to post actual code to help you with what looks like a homework problem.

I would use the Advanced Filter to seperate the three classes of data. Consider what happens if the Sheet1 data set is the "List Range" and the ID column on sheet 2 is used as the "Criteria".
 

Chi_Archon

New Member
Joined
Sep 7, 2007
Messages
5
Managing Used Book Inventory

I doubt that you will get anyone to post actual code to help you with what looks like a homework problem.

I would use the Advanced Filter to seperate the three classes of data. Consider what happens if the Sheet1 data set is the "List Range" and the ID column on sheet 2 is used as the "Criteria".

I actually sell used books online and need help with my upload files..When I acquire books/sell books..my inventory changes and I need to upload a new file. I am trying to find an automated way to handle this.
 

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,454
Hello Chi_Archon, welcome to the board.
What you're asking for is fairly easily done (a few things come to mind right off) but
in order to provide a solution that's more specific to your needs than just a general
example that will require tweaking before it does you any good, perhaps you could
answer these questions first.
Compare Sheet1 and Sheet2. Data exists in rows that have a unique identifier in one column.
What column(s) are being used for these identifiers?
If unique ID (with row of data) exists in Sheet1 and NOT Sheet2, add row from Sheet1 to Sheet3 and mark "REMOVE" in new column next to row.
When you say 'add row', do you just want to copy the row from sheet1 to sheet3?
- And then which column (of which sheet?) do you want marked with 'Remove'?

(Same question applies for the 'ADD' and 'UPDATE' conditions.)
 

Chi_Archon

New Member
Joined
Sep 7, 2007
Messages
5

ADVERTISEMENT

Hello Chi_Archon, welcome to the board.
What you're asking for is fairly easily done (a few things come to mind right off) but
in order to provide a solution that's more specific to your needs than just a general
example that will require tweaking before it does you any good, perhaps you could
answer these questions first.
Compare Sheet1 and Sheet2. Data exists in rows that have a unique identifier in one column.
What column(s) are being used for these identifiers?
If unique ID (with row of data) exists in Sheet1 and NOT Sheet2, add row from Sheet1 to Sheet3 and mark "REMOVE" in new column next to row.
When you say 'add row', do you just want to copy the row from sheet1 to sheet3?
- And then which column (of which sheet?) do you want marked with 'Remove'?

(Same question applies for the 'ADD' and 'UPDATE' conditions.)


Thanks for welcoming me MVP! This seems like a great resource.

To answer your questions..

The unique identifier (a ten digit ISBN number) will exist in column A. Yes, I want to copy the designated row. The column that should be marked with an action REMOVE/ADD/MODIFY will be column J. In each of my three scenarios, the appropriate row from the designated sheet must be copied into Sheet3 and marked. My first two sheets will have data filling colums A-I for each row. J will be the next empty column where this action note will be placed.
 

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,454
Hello Chi_Archon,
Try something like this and see if I got all your criteria straight.
(Assumes you've got headers in row 1 of the sheets.)
Code:
Sub CompareSheets()
Dim LstRw1&, LstRw2&
Dim Sht1Rng As Range, Sht2Rng As Range, _
    ISBN As Range, ISBNmatch As Range
    
Application.ScreenUpdating = False
    
With Sheets("Sheet1")
  LstRw1 = .Cells(Rows.Count, "A").End(xlUp).Row
  Set Sht1Rng = .Range(.Cells(2, "A"), .Cells(LstRw1, "A"))
End With

With Sheets("Sheet2")
  LstRw2 = .Cells(Rows.Count, "A").End(xlUp).Row
  Set Sht2Rng = .Range(.Cells(2, "A"), .Cells(LstRw2, "A"))
End With

'''/// Compare sheet1 to sheet2
For Each ISBN In Sht1Rng
  If WorksheetFunction.CountIf(Sht2Rng, ISBN) > 0 Then
    With Sheets("Sheet2")
      Set ISBNmatch = Sht2Rng.Find(ISBN, lookat:=xlContents)
      ISBNmatch.Resize(, 8).Copy _
        Sheets("Sheet3").Cells(Rows.Count, "A").End(xlUp)(2)
      ISBNmatch.Offset(, 9).Value = "UPDATE"
    End With
  Else
    ISBN.Resize(, 8).Copy Sheets("Sheet3").Cells(Rows.Count, "A").End(xlUp)(2)
    ISBN.Offset(, 9).Value = "REMOVE"
  End If
Next
    
'''/// Compare sheet2 to sheet1
For Each ISBN In Sht2Rng
  If Not WorksheetFunction.CountIf(Sht1Rng, ISBN) > 0 Then
    ISBN.Resize(, 8).Copy Sheets("Sheet3").Cells(Rows.Count, "A").End(xlUp)(2)
    ISBN.Offset(, 9).Value = "ADD"
  End If
Next

Application.ScreenUpdating = True

End Sub

(Might take a while to execute if you've got many thousands of rows between these
sheets because it's looping all the way down through column A of each one.)

Hope it helps.
 

Chi_Archon

New Member
Joined
Sep 7, 2007
Messages
5

ADVERTISEMENT

Almost There

HalfAce,

This is great..I just tried to tweak one things but was unsuccessful. I need the action notes (ADD,REMOVE,UPDATE) to also appear in Sheet3. I think you script copies before adding the action tags. I tried to switch this and copy the update row, but it didn't work for some reason. Can you help?

Thanks so much!
 

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,454
Sure. Try replacing these two sections of code:
Code:
'''/// Compare sheet1 to sheet2 
For Each ISBN In Sht1Rng 
  If WorksheetFunction.CountIf(Sht2Rng, ISBN) > 0 Then 
    With Sheets("Sheet2") 
      Set ISBNmatch = Sht2Rng.Find(ISBN, lookat:=xlContents) 
      ISBNmatch.Resize(, 8).Copy _ 
        Sheets("Sheet3").Cells(Rows.Count, "A").End(xlUp)(2) 
      ISBNmatch.Offset(, 9).Value = "UPDATE" 
    End With 
  Else 
    ISBN.Resize(, 8).Copy Sheets("Sheet3").Cells(Rows.Count, "A").End(xlUp)(2) 
    ISBN.Offset(, 9).Value = "REMOVE" 
  End If 
Next 
    
'''/// Compare sheet2 to sheet1 
For Each ISBN In Sht2Rng 
  If Not WorksheetFunction.CountIf(Sht1Rng, ISBN) > 0 Then 
    ISBN.Resize(, 8).Copy Sheets("Sheet3").Cells(Rows.Count, "A").End(xlUp)(2) 
    ISBN.Offset(, 9).Value = "ADD" 
  End If 
Next

with these two slightly rearranged & tweaked sections:
Code:
'''/// Compare sheet1 to sheet2
For Each ISBN In Sht1Rng
  If WorksheetFunction.CountIf(Sht2Rng, ISBN) > 0 Then
    With Sheets("Sheet2")
      Set ISBNmatch = Sht2Rng.Find(ISBN, lookat:=xlContents)
      ISBNmatch.Offset(, 9).Value = "UPDATE"
      ISBNmatch.Resize(, 10).Copy _
        Sheets("Sheet3").Cells(Rows.Count, "A").End(xlUp)(2)
    End With
  Else
    ISBN.Offset(, 9).Value = "REMOVE"
    ISBN.Resize(, 10).Copy Sheets("Sheet3").Cells(Rows.Count, "A").End(xlUp)(2)
  End If
Next
    
'''/// Compare sheet2 to sheet1
For Each ISBN In Sht2Rng
  If Not WorksheetFunction.CountIf(Sht1Rng, ISBN) > 0 Then
    ISBN.Offset(, 9).Value = "ADD"
    ISBN.Resize(, 10).Copy Sheets("Sheet3").Cells(Rows.Count, "A").End(xlUp)(2)
  End If
Next

You were right. It was copying before entering the 'tags'.
The changes include entering the tags before copying, and then expanding the
range getting copied to include those tags.

That what you're looking for?
 

Chi_Archon

New Member
Joined
Sep 7, 2007
Messages
5
Great.

works perfect! thanks so much for your help. I learned a lot from your code too.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,469
Messages
5,596,315
Members
414,053
Latest member
Dual Showman

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