Comparing Spreadsheets
 

Maybe someone can help with this.

I have data that I automatically download with Excel that contains
item numbers, price and quantities, among other things in other
columns. I have another spreadsheet that contains my current prices
and item numbers. The spreadsheet that I download contains many more
items than I currently have on my spreadsheet. If I had the new items
I downloaded located in Sheet 1 and my current items in Sheet 2, how
could I (with a macro) compare the two and display only the data that
has changed on a different sheet (ignoring all items that I currently
do not have on my spreadsheet)? Here's an example:

On my new spreadsheet (the one that is updated daily) an item is
listed with an item number "100" and a price of $25.
My current spreadsheet has item number "100" listed at a price of $17.
When I run the macro, I would like it to list (on another sheet) the
item number "100" and the new price of "$25"
All other data will be ignored if I do not have it listed on my
spreadsheet.

Can anyone help? If needed, I can list more details.

TIA!

Comparing Spreadsheets
 

This should do the job.
You will have to change some of the lines to match your setup.

'---------------------------------------------------------
Sub test()
Dim Mysheet As Worksheet
Dim MyRow As Long
Dim MyPrice As Double
Dim CurrentSheet As Worksheet
Dim CurrentPrice As Double
Dim CurrentRow As Long
Dim Changelist As Worksheet
Dim ChangeRow As Long
Dim MyItem
Dim FoundCell As Object
'-----------------------------
Set Mysheet = ActiveSheet
Worksheets.Add befo=Worksheets(1)
Set Changelist = ActiveSheet
ChangeRow = 2
Set CurrentSheet = Workbooks("book1.xls").Worksheets("Current")
'--------------------------
For MyRow = 1 To 1000
MyItem = Mysheet.Cells(MyRow, 1).Value
Set FoundCell = CurrentSheet.Columns(1).Find(MyItem)
If Not FoundCell Is Nothing Then
MyPrice = Mysheet.Cells(MyRow, 2).Value
CurrentRow = FoundCell.Row
CurrentPrice = CurrentSheet.Cells(CurrentRow, 2)
If CurrentPrice < MyPrice Then
Changelist.Cells(ChangeRow, 1).Value = MyItem
Changelist.Cells(ChangeRow, 2).Value = MyPrice
Changelist.Cells(ChangeRow, 3).Value = CurrentPrice
ChangeRow = ChangeRow + 1
End If
End If
Next
MsgBox ("Done.")
End Sub
'---------------------------------------------------------------

--
Message posted from http://www.ExcelForum.com

Comparing Spreadsheets
 

Brian,

Thanks very much for the code! Keep receiving a "Subscript out of
range" message when running this. Maybe I am working the macro wrong?

Also, since MyRow is set for 1 to 1000, my assumption is that this
only supports 1000 rows. I changed this to deal with 5000, since I
will be dealing with over 3500 rows of information.

Another question: Will this properly support two different sheets of
data, one with about 3500+ rows and one with only about 100 or more?

Thanks!


On Wed, 28 Jan 2004 08:24:35 -0600, BrianB
wrote:

This should do the job.
You will have to change some of the lines to match your setup.

'---------------------------------------------------------
Sub test()
Dim Mysheet As Worksheet
Dim MyRow As Long
Dim MyPrice As Double
Dim CurrentSheet As Worksheet
Dim CurrentPrice As Double
Dim CurrentRow As Long
Dim Changelist As Worksheet
Dim ChangeRow As Long
Dim MyItem
Dim FoundCell As Object
'-----------------------------
Set Mysheet = ActiveSheet
Worksheets.Add befo=Worksheets(1)
Set Changelist = ActiveSheet
ChangeRow = 2
Set CurrentSheet = Workbooks("book1.xls").Worksheets("Current")
'--------------------------
For MyRow = 1 To 1000
MyItem = Mysheet.Cells(MyRow, 1).Value
Set FoundCell = CurrentSheet.Columns(1).Find(MyItem)
If Not FoundCell Is Nothing Then
MyPrice = Mysheet.Cells(MyRow, 2).Value
CurrentRow = FoundCell.Row
CurrentPrice = CurrentSheet.Cells(CurrentRow, 2)
If CurrentPrice < MyPrice Then
Changelist.Cells(ChangeRow, 1).Value = MyItem
Changelist.Cells(ChangeRow, 2).Value = MyPrice
Changelist.Cells(ChangeRow, 3).Value = CurrentPrice
ChangeRow = ChangeRow + 1
End If
End If
Next
MsgBox ("Done.")
End Sub
'----------------------------------------------------------------


---
Message posted from http://www.ExcelForum.com/