A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

matching items, comparing price changes in rows from 2 sheets



 
 
Thread Tools Display Modes
  #1  
Old February 28th, 2004, 05:08 PM
ken
external usenet poster
 
Posts: n/a
Default matching items, comparing price changes in rows from 2 sheets

How can I do the following:

I have a vendor who sends me price updates in msexcel of
about 10,000 items. The vendor's list of items is always
growing so there are new items inserted between rows on
updated lists.
I wish to maintain a list (subset) of say 1000 of these
items and do the following:
Match the rows, show price difference, create a
new list of the same 1000 items with the updated prices.


  #2  
Old February 28th, 2004, 05:20 PM
JulieD
external usenet poster
 
Posts: n/a
Default matching items, comparing price changes in rows from 2 sheets

Hi Ken

off the top of my head, i would approach it like this
your list of 1000 items in sheet2
copy / add sheet - get his 10,000 items in sheet 1

assume on sheet 2 you have this arrangement for your 1000 items - your first
item being on row 2
column A: Item number
column B: item name
column C: current price
column D: this is where you want the new price to go
column E: column C - D (or vice versa)

assume on sheet 1 you have for his 10000 items
column A: item number
column B: item name
column C: current price

what i would do is use a vlookup statement in column D of sheet 2, e.g.

so D2 would have the formula
=vlookup(A2,Sheet1!$A$1:$C$10000,3,false)
and then copy this formula down column D (double click on autofill handle is
the easiest way)

you can then hide sheet2 columnC when you want to print it
next time, copy sheet 2 column D and paste special on column C

hope this helps
JulieD


"ken" wrote in message
...
How can I do the following:

I have a vendor who sends me price updates in msexcel of
about 10,000 items. The vendor's list of items is always
growing so there are new items inserted between rows on
updated lists.
I wish to maintain a list (subset) of say 1000 of these
items and do the following:
Match the rows, show price difference, create a
new list of the same 1000 items with the updated prices.




  #3  
Old February 28th, 2004, 05:53 PM
Ken Wright
external usenet poster
 
Posts: n/a
Default matching items, comparing price changes in rows from 2 sheets

How much history do you want to keep? Are you looking to just compare current
to previous? because if so then this is an absolute breeze with a Pivot table,
AS LONG as data is consistent from one list to the next for the same item in
terms of either product number or description or whatever you use to uniquely
identify the circa 10,000 items. Doesn't matter if it doesn't appear in one or
the other, but if Widget X is called Widget Y in the other list it makes life
real hard.

I would take the previous list, eg:-

Item Price
a 8.68
b 7.92
c 8.12
d 6.83
e 6.89
f 8.99
g 5.61
h 8.93

and insert 2 columns, and name them 'List' and 'Watch', and then in the List
field, put '1 - Old' eg:-

List Watch Item Price
1 - Old a 8.68
1 - Old b 7.92
1 - Old c 8.12
1 - Old d 6.83
1 - Old e 6.89
1 - Old f 8.99
1 - Old g 5.61
1 - Old h 8.93

Directly underneath this put the current list and put '2 - New' in the list
field, so it now looks like this:-

List Watch Item Price
1 - Old a 8.68
1 - Old b 7.92
1 - Old c 8.12
1 - Old d 6.83
1 - Old e 6.89
1 - Old f 8.99
1 - Old g 5.61
1 - Old h 8.93
2 - New a 8.68
2 - New b 7.92
2 - New c 8.12
2 - New d 6.83
2 - New e 6.89
2 - New f 8.99
2 - New g 5.61
2 - New h 8.93

Now create a table for the items you want to watch, eg:-

'Watch Table'
Item Flag
b YES
d YES
g YES

Name the table, eg FlagItems

Now in the first cell underneath the WATCH header (I'm assuming this is cell B2)
put this formula and copy down:-

=IF(ISNA(VLOOKUP(C2,FlagItems,2,0)),"NO",VLOOKUP(C 2,FlagItems,2,0))

Now put a -1 in any empty cell and then copy that cell. Select all the item
prices where the value in Col A is '1 - Old' and do Edit / Paste Special /
Multiply. That is now your source data sorted out.

Select all the data including the 4 headers and do Data / Pivot Table and
PivotChart report, then hit Next / Next / Finish

Drag 'Item' to the middle far left of the table.
Drag 'List' to the Top Middle of the table
Drag 'Price' into the center of the table
Drag 'Watch' to the very Top left of the Sheet

At the moment you will have a price comparison for every item in the list, as
well as being able to identify new items and items that have gone, and by simply
clicking on the dropdown arrow top left where it says Watch and clicking on
'YES' you will see just the Watched items. Ignore the fact that the Old items
all appear negative, but concentrate on the TOTAL column, as any price delta
will be recorded there. Positive numbers mean a price hike, negative a
reduction, and 0 means it is static.

Any problems I will happily knock up a dummy sheet for you, or if you send me
two lists I will put them into a conmparison for you.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------



"ken" wrote in message
...
How can I do the following:

I have a vendor who sends me price updates in msexcel of
about 10,000 items. The vendor's list of items is always
growing so there are new items inserted between rows on
updated lists.
I wish to maintain a list (subset) of say 1000 of these
items and do the following:
Match the rows, show price difference, create a
new list of the same 1000 items with the updated prices.




---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.598 / Virus Database: 380 - Release Date: 28/02/2004


  #4  
Old February 28th, 2004, 08:35 PM
ken
external usenet poster
 
Posts: n/a
Default matching items, comparing price changes in rows from 2 sheets

it worked. thanks a lot


-----Original Message-----
Hi Ken

off the top of my head, i would approach it like this
your list of 1000 items in sheet2
copy / add sheet - get his 10,000 items in sheet 1

assume on sheet 2 you have this arrangement for your 1000

items - your first
item being on row 2
column A: Item number
column B: item name
column C: current price
column D: this is where you want the new price to go
column E: column C - D (or vice versa)

assume on sheet 1 you have for his 10000 items
column A: item number
column B: item name
column C: current price

what i would do is use a vlookup statement in column D of

sheet 2, e.g.

so D2 would have the formula
=vlookup(A2,Sheet1!$A$1:$C$10000,3,false)
and then copy this formula down column D (double click on

autofill handle is
the easiest way)

you can then hide sheet2 columnC when you want to print it
next time, copy sheet 2 column D and paste special on

column C

hope this helps
JulieD


"ken" wrote in message
...
How can I do the following:

I have a vendor who sends me price updates in msexcel of
about 10,000 items. The vendor's list of items is

always
growing so there are new items inserted between rows on
updated lists.
I wish to maintain a list (subset) of say 1000 of these
items and do the following:
Match the rows, show price difference, create a
new list of the same 1000 items with the updated prices.




.

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 02:22 PM.


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