Excel 97 - Comparison

[Jerim79]

I have searched for any previous topics that may have already answered
this, but didn't find any.

I am running Excel 97. I have two spreadsheets. One is the 2006
version and the other is the 2007 version. It is a list of customers.
We want to see how many customers are on both the 2006 and the 2007
spreadsheets. The only comparison tips/programs/tools I have seen, do
just a line by line comparison. That works great if you the rows are
exactly the same, in the same order and you just want to see what data
has changed. However, my two spreadsheets may not have the same
ordering. Even when sorted, we may have added/deleted customers that
throws the order off completely from the other spreadsheet. Since a
customer may be on both spreadsheets, just not on the same row, all
the tools/tips/programs I have used so far have found every line to be
different.

What I need is something that will take the value of cell A1 from
Spreadsheet 1 and compare it against cells A1-A1000 on Spreadsheet 2,
looking for a match. Then take cell A2 from Spreadsheet 1 and compare
it against cells A1-A1000 on Spreadsheet 2, looking for a match. And
so on and so on and so on. Any help would be appreciated.

 

[Asko Telinen]

I have searched for any previous topics that may have already answered
this, but didn't find any.

I am running Excel 97. I have two spreadsheets. One is the 2006
version and the other is the 2007 version. It is a list of customers.
We want to see how many customers are on both the 2006 and the 2007
spreadsheets. The only comparison tips/programs/tools I have seen, do
just a line by line comparison. That works great if you the rows are
exactly the same, in the same order and you just want to see what data
has changed. However, my two spreadsheets may not have the same
ordering. Even when sorted, we may have added/deleted customers that
throws the order off completely from the other spreadsheet. Since a
customer may be on both spreadsheets, just not on the same row, all
the tools/tips/programs I have used so far have found every line to be
different.

What I need is something that will take the value of cell A1 from
Spreadsheet 1 and compare it against cells A1-A1000 on Spreadsheet 2,
looking for a match. Then take cell A2 from Spreadsheet 1 and compare
it against cells A1-A1000 on Spreadsheet 2, looking for a match. And
so on and so on and so on. Any help would be appreciated.

I´m not sure if the Match formula is included in excel97 but this one
could do the job. It searches for row index from another matrix and
returns the that row index if match is found or a #missing value if not
found.

Here´s a example:

Sheet1 A1-A1000 contains year 2006 names.
Sheet2 A2-A1000 contains year 2007 names.
Result column is B2-B1000 in Sheet1.

Define a following formula to cell B1 in Sheet1:
=Match(A1;Sheet2!$A$1:$A$1000;0)

Now you can copy that formula down to cell 1000.

That´s all. Now you have an row index in B column or #missing value if
not found.

Asko.

 

[Don Guillett]

Have a look in the vba index for FINDNEXT. There is an excellent example

 

[Jerim79]

I´m not sure if the Match formula is included in excel97 but this one
could do the job. It searches for row index from another matrix and
returns the that row index if match is found or a #missing value if not
found.

Here´s a example:

Sheet1 A1-A1000 contains year 2006 names.
Sheet2 A2-A1000 contains year 2007 names.
Result column is B2-B1000 in Sheet1.

Define a following formula to cell B1 in Sheet1:
=Match(A1;Sheet2!$A$1:$A$1000;0)

Now you can copy that formula down to cell 1000.

That´s all. Now you have an row index in B column or #missing value if
not found.

Asko.

Thank you, Match was the trick. Just one question if you have time. I
actually wound up just using the Insert option to put a function in.
That gave me this formula: =MATCH(AF2,Sheet2!AF1:AF14438,0)

As I copy that down the page, it changes Sheet2!AF1 to Sheet2!AF2 and
Sheet2!AF3, etc. Anyway I can stop that? All the other values are
okay. When I tried your formula, it told me there was a syntax error.
I am using Excel 97.

 

[Jerim79]

Thank you, Match was the trick. Just one question if you have time. I
actually wound up just using the Insert option to put a function in.
That gave me this formula: =MATCH(AF2,Sheet2!AF1:AF14438,0)

As I copy that down the page, it changes Sheet2!AF1 to Sheet2!AF2 and
Sheet2!AF3, etc. Anyway I can stop that? All the other values are
okay. When I tried your formula, it told me there was a syntax error.
I am using Excel 97.

Actually, never mind. I just figured out that the $ sign declares a
constant. I just added that in front of the 1 and it stays put. Thanks
again.