Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comparing Items and Prices in Excel
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! -- Message posted from http://www.ExcelForum.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comparing Items and Prices in Excel
Hi
have a look at http://www.cpearson.com/excel/duplic...tractingCommon -- Regards Frank Kabel Frankfurt, Germany "merlin63 " schrieb im Newsbeitrag ... 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!! --- Message posted from http://www.ExcelForum.com/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comparing Items and Prices in Excel
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 -- Message posted from http://www.ExcelForum.com |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comparing Items and Prices in Excel
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 -- Message posted from http://www.ExcelForum.com |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comparing Items and Prices in Excel
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 -- Message posted from http://www.ExcelForum.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Chart showing ranges of prices of different items? | Charts and Charting in Excel | |||
Comparing dientical part numbers but with different prices | Excel Discussion (Misc queries) | |||
relation between 'items' en 'prices' | Excel Worksheet Functions | |||
excel 2003 help comparing prices for lowest | Excel Worksheet Functions |