Comparing more data!

[merlin63]

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, howcould 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.

Here's one thing that I think made it difficult. One spreadsheet could contain 3500+ items (the one I download) and my spreadsheet containing my current items would probably be less than 500.

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

TIA!

[sym]

The forum doesn't seem to want to keep indented code, so it is a bit hard to read.


Public Sub CompareToDownload()
Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim curWBK As Workbook
Dim downloadWBK As Workbook
Dim curSheet As Worksheet
Dim downloadSheet As Worksheet
Dim compareSheet As Worksheet
Dim curItemNum As String
Dim curItemPrice As Double

Application.ScreenUpdating = False

' get references to the workbooks
Set curWBK = Workbooks("ItemNum_Price.xls")
Set downloadWBK = Workbooks("Download_Items.xls")

' get references to the relevant sheets
Set curSheet = curWBK.Sheets("Sheet1")
Set downloadSheet = downloadWBK.Sheets("Sheet1")

' this sheet is the page that the comparisons are displayed on
On Error Resume Next
Set compareSheet = curWBK.Sheets("CompareSheet")
If compareSheet Is Nothing Then
' if it doesn'n exist, create it
Set compareSheet = curWBK.Sheets.Add()
compareSheet.Name = "CompareSheet"
Else
' if it does exist, clear it of data
compareSheet.Cells.Delete
End If
On Error GoTo 0
compareSheet.Range("A1").Value = "Item #"
compareSheet.Range("B1").Value = "New Price"
compareSheet.Range("C1").Value = "Old Price"

k = 2
For i = 2 To curSheet.UsedRange.Rows.Count
curItemNum = curSheet.Range("A" & i).Value
curItemPrice = curSheet.Range("B" & i).Value
blnFound = False
For j = 2 To downloadSheet.UsedRange.Rows.Count
If downloadSheet.Range("A" & j).Value = curItemNum Then
If downloadSheet.Range("B" & j).Value <> curItemPrice Then
compareSheet.Range("A" & k).Value = curItemNum
compareSheet.Range("B" & k).Value = curItemPrice
compareSheet.Range("C" & k).Value = downloadSheet.Range("B" & j).Value
k = k + 1
End If
j = downloadSheet.UsedRange.Rows.Count
End If
Next j
Next i

Application.ScreenUpdating = True

End Sub


[sym]

Here are the example workbooks

[merlin63]

Thanks very much for your help! This works perfectly and is definately more than I would have expected to receive. Thanks again!