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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|