Comparing Items and Prices in Excel

[merlin63]

Greetings!

Maybe someone could help me out here. I'm looking for an easy way of comparing two different sheets of data and recording their differences. For instance, I want to compare a list of item numbers and prices and 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 information and are out of order. Item4 is missing from Sheet 2 and Item5 is missing from Sheet 1. These should be ignored. Also, it should not matter where in either sheet the data is located. What should happen is 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 how to do all of this. My assumption is that a macro would probably be an easier way to do this. Any suggestions?

Thanks!!

[drew.]

ok, i dont know the exact function but im sure there is one. Go to insert>functions and at the top there will be a place you can type something and it will look up functions that will help you do what you want to do.

[drew.]

the search field is only in excel xp, so if you have an older version youll just have to look through all the functions

[merlin63]

Someone suggested using =IF(COUNTIF($A$1:$A$10,B4)>0,B4,"") but this does not seem like it would work for me. I did look through several of the functions that I thought would meet my needs but I could not find one. I'll keep looking though. In the meantime, if anyone find anything please let me know!

[merlin63]

Ok, maybe this could be an easier way to do this... If I just took one sheet and then put the data into three columns: Old Items, New Items and Price, it could then run down the list of items and find what items match in either column and carry the item number and cost over to a new 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 match every entry and one column could possibly be longer than the other. Therefore something needs to run down both columns until it identifies that the item number in the old item column found a match in the new item colum. If the match is found, it could paste both the item number 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 new 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, this 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 for any value that is not #N/A or 0 and copy the item number and new price down to a new sheet for that row. Is this easily done?

Thanks!