Comparing spreadsheets
 

I'm looking for help on the best way to compare two
different spreadsheets (in different workbooks). Each
spreadsheet is a report of open receivables.

Workbook 1 is last week's report with notes. Workbook 2
is this week's report without notes.

If an item exists in both workbooks, I want to copy the
notes from Workbook 1 and put them in workbook 2 (so the
user doesn't have to research the item all over again).

Not sure if 2 arrays should be (or can be used) and or how
to compare fields in both arrays to find the same item.

I could read a cell in Workbook 1 and then see if it
exists in Workbook 2 but then I would be taking each item
in Workbook 1 and reading thru Workbook 2. That doesn't
seen very efficient.

I could read a cell in Workbook 1 and run thru Workbook 2
until I find a cell that is equal to or greater than the
same key field in Workbook 2.

I'm just wondering if there is an easier, better, or more
efficient way to accomplish this. Any suggestions would
be great. Thank you for the help....

Comparing spreadsheets
 

JT: I update a spreadsheet every week in which I have note written against
certain items. When the next update comes out, I can find that (a) certain
items aren't there any more, (b) new items have been added, and (c) an item
with a note isn't on the same row any more. What I do is put the following
formula in Row 2 of the column where the note is to appear and run down to
the bottom of the used range -
Range("X2").Select
ActiveCell.Formula =
"=IF(ISERROR(MATCH($C2,TIRs!$C$2:$C$15000,0)),0,IN DEX(TIRs!X$2:X$15000,MATCH
($C2,TIRs!$C$2:$C$15000,0)))"

It's a long formula, so watch for a line break. In this formula, Col. C
contains the item's unique designator, and Col. X contains the notes. If
the item designator in the row containing the formula is found anywhere on
the sheet names TIRs in cells C2 to C15000, it will find the matching row
and pull the note from there onto the new sheet.

Two notes: When I use this, both sheets are in the same workbook, so you
will have to adjust the sheet name to include the workbook, too. Also, my
data is sorted with no duplicate designators and a header row.

HTH
Ed

"JT" wrote in message
...
I'm looking for help on the best way to compare two
different spreadsheets (in different workbooks). Each
spreadsheet is a report of open receivables.

Workbook 1 is last week's report with notes. Workbook 2
is this week's report without notes.

If an item exists in both workbooks, I want to copy the
notes from Workbook 1 and put them in workbook 2 (so the
user doesn't have to research the item all over again).

Not sure if 2 arrays should be (or can be used) and or how
to compare fields in both arrays to find the same item.

I could read a cell in Workbook 1 and then see if it
exists in Workbook 2 but then I would be taking each item
in Workbook 1 and reading thru Workbook 2. That doesn't
seen very efficient.

I could read a cell in Workbook 1 and run thru Workbook 2
until I find a cell that is equal to or greater than the
same key field in Workbook 2.

I'm just wondering if there is an easier, better, or more
efficient way to accomplish this. Any suggestions would
be great. Thank you for the help....