matching items, comparing price changes in rows from 2 sheets

  • Thread starter Thread starter ken
  • Start date Start date
K

ken

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.
 
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
 
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(C2,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.
 
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





.
Click to expand...
 
Back
Top