Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Compare Old & New Product List (i.e. VLOOKUP)
Hello,
Once a week I receive an updated price list from our supplier reflecting newly added products, deleted products, and new pricing for certain products in an Excel spreadsheet format. The list consists of approx. 1,500 products. Is there a way to compare the Old list with the New one and somehow identify the new changes in a few quick steps? Someone mentioned using "VLOOKUP" but I'm not too familiar with it. Any help will be greatly appreciated. Thank you in advance. cww |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Compare Old & New Product List (i.e. VLOOKUP)
It really depends on exactly how you want to compare the data.
Let's say each list has a product name and price: old list new list sheet1: sheet2: name1 2.30 name4 2.20 name2 4.10 name3 4.10 name3 0.10 name1 0.20 name4 1.00 name2 1.00 If you wanted to compare to see if the price was the same on items in both sheets you could do this in say column C on sheet1: =IF(b1=VLOOKUP(A1,Sheet2!A$1:B$4,2,FALSE), "same price","price changed") Then fill down. The VLOOKUP command tells excel to find the value from A1 in the table of new prices on Sheet2 and return the value from column 2 on Sheet2. Then it's just a matter of comparing this value to the old price (B1 in my example)... There's plenty of other things you could do to compare just depends on exactly what you want. Hope that makes sense. "Hal" wrote: Hello, Once a week I receive an updated price list from our supplier reflecting newly added products, deleted products, and new pricing for certain products in an Excel spreadsheet format. The list consists of approx. 1,500 products. Is there a way to compare the Old list with the New one and somehow identify the new changes in a few quick steps? Someone mentioned using "VLOOKUP" but I'm not too familiar with it. Any help will be greatly appreciated. Thank you in advance. cww |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Compare Old & New Product List (i.e. VLOOKUP)
As a start:
Assume Oldlist and Newlist are in Column A of sheets 1 & 2 and OldPriceList is Column B of sheet1. Highlight column A sheet1 and do Insert==Name==Define==Oldlist Highlight column B sheet1 and do Insert==Name==Define==OldPricelist Highlight column A sheet2 and do Insert==Name==Define==Newlist Assuming data starts in row 1: In C1 of Sheet1 put =IF(COUNTIF(Newlist,A1)=0,"Deleted","") and copy down This will highlight producs Deleted in C1 of sheet2 put =IF(COUNTIF(OldList,A1)=0,"Added","") and copy down This will highlight products Added For products which have price changes: In D1 of sheet2 put: =IF(ISERROR(MATCH(A1,OldList,0)),"",IF(INDEX(OldPr iceList,MATCH(A1,OldList,0))<B1,"Price Changed","")) and copy down HTH "Hal" wrote: Hello, Once a week I receive an updated price list from our supplier reflecting newly added products, deleted products, and new pricing for certain products in an Excel spreadsheet format. The list consists of approx. 1,500 products. Is there a way to compare the Old list with the New one and somehow identify the new changes in a few quick steps? Someone mentioned using "VLOOKUP" but I'm not too familiar with it. Any help will be greatly appreciated. Thank you in advance. cww |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Compare Old & New Product List (i.e. VLOOKUP)
Thank you so much for your quick response! I will get started on it.
cww "Hal" wrote in message ... Hello, Once a week I receive an updated price list from our supplier reflecting newly added products, deleted products, and new pricing for certain products in an Excel spreadsheet format. The list consists of approx. 1,500 products. Is there a way to compare the Old list with the New one and somehow identify the new changes in a few quick steps? Someone mentioned using "VLOOKUP" but I'm not too familiar with it. Any help will be greatly appreciated. Thank you in advance. cww |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Compare product prices from two supliers and produce report | Excel Worksheet Functions | |||
Subtotals in a list | Excel Discussion (Misc queries) | |||
Product list to match price list | Excel Discussion (Misc queries) | |||
connector line between vlookup and list. | Excel Discussion (Misc queries) | |||
Which function(s)? | Excel Worksheet Functions |