Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Comparing Spreadsheets from Different Sources
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! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Comparing Spreadsheets from Different Sources
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 On Feb 29, 1:00*pm, McLovin wrote: 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! * |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Comparing Spreadsheets from Different Sources
VLOOKUP doesn't work on the scale I need it to. Tried it. :-(
"JP" wrote: 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 On Feb 29, 1:00 pm, McLovin wrote: 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! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Comparing Spreadsheets from Different Sources
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--- -- RyGuy "McLovin" wrote: VLOOKUP doesn't work on the scale I need it to. Tried it. :-( "JP" wrote: 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 On Feb 29, 1:00 pm, McLovin wrote: 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! |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Comparing Spreadsheets from Different Sources
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 On Feb 29, 2:24*pm, McLovin wrote: VLOOKUP doesn't work on the scale I need it to. *Tried it. *:-( * "JP" wrote: 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 On Feb 29, 1:00 pm, McLovin wrote: 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! *- Hide quoted text - - Show quoted text - |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Comparing Spreadsheets from Different Sources
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 "McLovin" wrote: 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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
comparing two spreadsheets | Excel Discussion (Misc queries) | |||
Comparing spreadsheets | Excel Worksheet Functions | |||
Comparing Spreadsheets | Excel Discussion (Misc queries) | |||
Comparing two spreadsheets | Excel Worksheet Functions | |||
Comparing two spreadsheets. | New Users to Excel |