Comparing items and prices

[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 and PRICE are two separate columns)

ITEM PRICE
ITEM1 2.00
ITEM2 7.70
ITEM3 4.00
ITEM4 5.10
ITEM9 11.50

Sheet 2 (These would be the new prices, ITEM and PRICE are two separate columns)

ITEM PRICE
ITEM5 9.50
ITEM3 4.50
ITEM9 11.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

Notice that ITEM9 was not copied over. This was because the price did not change. If the price has not changed, I would like to ignore it.

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!!

[BrianB]

VLOOKUP will be just the job.
1. You need to make a new column in sheet 1 with the lookup formula to show new prices against the old. Say Column C, copy formula down something like :-
=VLOOKUP(A1,Sheet2!$A$1:$B$1000,2,FALSE)

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

[merlin63]

Wow, this looks like it will work. Right now I have three rows of data:


A B C D
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

Obviously the only differences in these five items are ITEM1, ITEM2 and ITEM3. ITEM9 has a match, but does not have a price difference.


Two things:

The one formula is =VLOOKUP(A1,Sheet2!$A$1:$B$1000,2,FALSE)

Should I change the $B$1000 to $B$5000 if I am dealing with more than 1000 items (I may have around 5000 items to do)

Secondly, do you know of a way that I can have a macro automatically scroll down column D and copy anything that has changed (ignoring #N/A and 0) and copy the new value and item number to a sheet? The new sheet would have these values and ignore the rest:

A B
ITEM1 1.75
ITEM2 6.8
ITEM3 4.5

Again, thanks very much fro the help!

[TheFIDDLER]

If you want a cleaner looking sheet - use the following to code against the N/A error from showing in the cell.

IF( ISERROR(VLOOKUP(A1,Sheet2!$A$1:$B$1000,2,FALSE)),"",VLOOKUP(A1,Sheet2!$A$1:$B$1000,2,FALSE))

Also, if your range exceeds 1000, your vlookup must include the entire range set. So yes, code to $A$1:$B$5000.

Yes - you can code a macro that looks for a specific value in one row, and if found will copy to another sheet. I'd post it here but have to leave so hopefully someone else will do so for you. If not, I'll do it when I get back.

And if you use vlookup as posted here, you only have to code for looking at cells that are <> zero, and can happily ignore the not available statements.

[merlin63]

Thanks alot for the VLOOKUP hint. It does make everything that has changed stand out a bit better.

I was looking for a macro that would copy items <>0 and move then but have not been successful. If I find anything first, I will post.

[Garratt]

Move them how?

As copy them to another sheet and delete the row they are on?

Public Sub test()
Dim LastRow As Integer
'assuming column is "B"
LastRow = Cells.Find(What:="*", _
            SearchDirection:=xlPrevious, _
            SearchOrder:=xlByRows).Row

For X = 1 To LastRow
    If Cells(X, 2).Value <> "0" Then
        'move contents somewhere
        Cells(X, 2).Select
        Selection.EntireRow.Delete
    End If
Next X
End Sub

[merlin63]

I copied and pasted the macro into Excel. I ran it on the following data:

ITEM1 0
ITEM2 -1
ITEM3 2
ITEM4 0
ITEM5 4

After running that macro, it gave me the following data:
ITEM1 0
ITEM3 2
ITEM4 0

What could be wrong with the macro? Also, I wouldn't need for anything to be removed from the sheet, just any item <>0 to be copied to a different sheet.

[Garratt]

Post the missing part you added ('move cell somwhere), or the whole thing as you ran it.