compare 2 sheets to find what is different

[markcoronado]

I'm using excel 2000.
I need to find a way to compare 2 sheets to see what is different.
I attached 2 of the sheets I need to compare to see what is different.
First you will see that one has 247 rows & the other has 249.
That is my biggest problem I need to know what 2 row are in one & not in the other & then mark them some way or move the the extra rows to the bottom.
There may also be some data changes in some of the other columns like the price or quantity columns.
I always start out by sorting the data ascending by column (W) Item ID.
I'm pretty new to excel so I really could use some help & would much appreciate it.

[mrice]

I would recommend the use of the countif and exact functions to help out.

If you paste the contents of the shorter list into the same sheet as the longer one from columns AA onward then you can add the following formulae

Cell X2 =COUNTIF(AA:AA,A2)
Cell Y2 =COUNTIF(A:A,A2)
Cell Z2 =EXACT(X2,Y2)

You will see that rows 26-28 come up as false and this shows the rows that are present in the longer list that are not in the shorter.

[markcoronado]

I would recommend the use of the countif and exact functions to help out.

If you paste the contents of the shorter list into the same sheet as the longer one from columns AA onward then you can add the following formulae

Cell X2 =COUNTIF(AA:AA,A2)
Cell Y2 =COUNTIF(A:A,A2)
Cell Z2 =EXACT(X2,Y2)

You will see that rows 26-28 come up as false and this shows the rows that are present in the longer list that are not in the shorter.

Thanks that worked.
If I'm correct your formula is comparing column A in the long list with column AA in the short list.
Could I change the AA to WW & A to W to have it compare the two lists with column W & WW instead of A & AA?
The reason I'm asking is that on some of my lists column A which is Product ID may be the same for 2 or more items in column W which is Item ID, so it would be better to compare the 2 lists by column W (Item ID).

(Edit)
I was just looking at the 2 lists again, & I found that row AA54 isn't in row A, so your formula only seems to be telling me what rows are in the long list that aren't in the short, but it isn't telling me if there are rows in the short list that aren't in the long list.
Is there a formula to make that happen?

Thanks for your help.
Mark