Comparing Spreadsheets from Different Sources

[McLovin]

I have 2 spreadsheets, each from a different source. Both spreadsheets
contain a store number, SKU and SKU quantity except one of the spreadsheets
includes SKUs that the other one doesn't . . . and v/v. I need to create a
report that compares both spreadsheets and returns the differences. For
example, if one of the spreadsheets from Source A shows a quantity of 5 for
one SKU, but the other spreadsheet from source B doesn't list that SKU at all
I need it to return a qty of zero or just show a blank. HELP!

 

[JP]

Have you checked out the VLOOKUP function? You could place the formula
in Source A workbook and look for the matching SKU in Source B. If it
doesn't exist, the formula returns the #N/A error.

http://tinyurl.com/yhurcy


HTH,
JP

 

[McLovin]

VLOOKUP doesn't work on the scale I need it to. Tried it. :-(

 

[ryguy7272]

I think this will do what you want:
Sub Compare2Shts()
For Each Cell In Worksheets("CompareSheet#1").UsedRange
If Cell.Value <> Worksheets("CompareSheet#2").Range(Cell.Address) Then
Cell.Interior.ColorIndex = 3
End If
Next

For Each Cell In Worksheets("CompareSheet#2").UsedRange
If Cell.Value <> Worksheets("CompareSheet#1").Range(Cell.Address) Then
Cell.Interior.ColorIndex = 3
End If
Next
End Sub


Regards,
Ryan---

 

[JP]

Maybe I'm not understanding what you need, or you aren't explaining it
properly. I've used VLOOKUP for situations just like you describe, on
worksheets with 16k+ rows. Granted, it's not nearly as efficient as
INDEX/MATCH but it does the job.


--JP

 

[Jim]

Mc,

Have you tried the advanced filter? Be sure you have a copy of all the data
before you start this.

Use one table as the list range and the other as the criteria range. This
will select all the records that match. Delete those and you then have all
the records that *don't* match. Then working with both original tables just
turn it around. Use the second table as the list range and the first table
as the criteria range. This will again select all the records that match.
Delete them and you will be left with all the records that don't match. The
combination of the two lists that don't match will be all the records you're
looking for.

HTH
Jim