excel

[kerry]

i have two different spreadsheets containing two columns,
column A contains reference numbers (both sheets will have
the same refernces) column B will contain the amounts
which may differ on each sheet.
i need to run a compare program to identify all amounts
that differ between the spreadsheets for the same refernce
number.
for eample

sheet 1
reference no amount
111 120.00
112 345.90
113 122.50

sheet 2
reference no amount
111 0.00
112 345.90
113 100.00

how can i find out the amounts and refernces that differ
from sheet 1 to sheet 2.

anybody who could provide me with any help could you
please email me on the following address:
(e-mail address removed) OR (e-mail address removed)

thank you

 

[Jay]

i have two different spreadsheets containing two columns,
column A contains reference numbers (both sheets will have
the same refernces) column B will contain the amounts
which may differ on each sheet.
i need to run a compare program to identify all amounts
that differ between the spreadsheets for the same refernce
number.
for eample

sheet 1
reference no amount
111 120.00
112 345.90
113 122.50

sheet 2
reference no amount
111 0.00
112 345.90
113 100.00

Here's one way. Combine the following into one line and paste it into
Sheet1!C2

=IF(ISERROR(VLOOKUP(A2,Sheet2!A:B,2,FALSE)),"missing",
IF(VLOOKUP(A2,Sheet2!A:B,2,FALSE)=B2, "same", "different"))

(Change the text constants to suit your needs.)

Then extend from C2 down for the length of the list.

I suggest using VLOOKUP (instead of comparing the same cells on the two
sheets) because it's more forgiving in case the two sheets get out of sync.