Comparing Items and Prices in Excel

[merlin63]

Greetings!

Maybe someone could help me out here. I'm looking for an easy way o
comparing two different sheets of data and recording their differences
For instance, I want to compare a list of item numbers and prices an
see exactly which prices have changed from one sheet to the next
Here's an example:

Sheet 1 (Let's say that this is my current prices)

ITEM PRICE
ITEM1 2.00
ITEM2 7.70
ITEM3 4.00
ITEM4 5.10

Sheet 2 (These would be the new prices)

ITEM PRICE
ITEM5 9.50
ITEM3 4.50
ITEM1 1.75
ITEM2 6.80

Now, as you can see, both sheets do not contain the same informatio
and are out of order. Item4 is missing from Sheet 2 and Item5 i
missing from Sheet 1. These should be ignored. Also, it should no
matter where in either sheet the data is located. What should happen i
that the following items and new prices should be copied to sheet 3:

ITEM1 1.75
ITEM2 6.80
ITEM3 4.50

I attempted to do this with VLOOKUP but couldn't figure out exactly ho
to do all of this. My assumption is that a macro would probably be a
easier way to do this. Any suggestions?

Thanks!

 

[Frank Kabel]

Hi
have a look at
http://www.cpearson.com/excel/duplicat.htm#ExtractingCommon

 

[merlin63]

Thanks for the reply. This function would help me a bit, but I a
trying to find only items that have had their prices changed. Tha
function will find me all of the items even if the prices have no
changed for them. Do you know of a different way to use the sam
function to obtain the results I am looking for?

Thanks

 

[merlin63]

Ok, maybe this could be an easier way to do this... If I just took on
sheet and then put the data into three columns: Old Items, New Item
and Price, it could then run down the list of items and find what item
match in either column and carry the item number and cost over to a ne
sheet. For example:

Old Items New Items Price
ITEM123 ITEM998 21.95
ITEM124 ITEM997 31.45
ITEM997 ITEM543 14.85
ITEM999 9.95
ITEM555 10.50

As you can see here, there are two columns with data that doesn't matc
every entry and one column could possibly be longer than the other.
Therefore something needs to run down both columns until it identifie
that the item number in the old item column found a match in the ne
item colum. If the match is found, it could paste both the item numbe
and price in a new sheet. Would this be a bit easier

 

[merlin63]

Ok, here is what I eventually came up with.

Someone on VBforums.com suggested using this VLOOKUP formula:
=VLOOKUP(A1,Sheet2!$A$1:$B$1000,2,FALSE)

These were the steps I needed to take:

1. Made a new column (C) in sheet1 with the lookup formula to show ne
prices against the old.

2. In column D I put another formula to show differences in price
=C1-B1

When comparing the two sets of data using the VLOOKUP shown above, thi
is what comes up:


ITEM1 2 1.75 -0.25
ITEM2 7.7 6.8 -0.9
ITEM3 4 4.5 0.5
ITEM4 5.1 #N/A #N/A
ITEM9 11.5 11.5 0

Now, I would just need to have something run down column D, look fo
any value that is not #N/A or 0 and copy the item number and new pric
down to a new sheet for that row. Is this easily done?

Thanks