Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Hal Hal is offline
external usenet poster
 
Posts: 19
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 40
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 4,339
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Hal Hal is offline
external usenet poster
 
Posts: 19
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Compare product prices from two supliers and produce report [email protected] Excel Worksheet Functions 0 June 9th 06 07:16 AM
Subtotals in a list Bagheera Excel Discussion (Misc queries) 9 May 20th 06 01:46 PM
Product list to match price list badgrandntl Excel Discussion (Misc queries) 13 February 2nd 06 03:28 AM
connector line between vlookup and list. mostakimm Excel Discussion (Misc queries) 0 January 26th 06 06:26 PM
Which function(s)? LB Excel Worksheet Functions 3 January 5th 05 07:19 PM


All times are GMT +1. The time now is 03:06 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"