Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to update column info based on column info from another worksh
I have a worksheet that has many columns. One of the columns contains Vendor
Sku Numbers and another column on the same worksheet contains Price information for that Part Number. I need to update pricing information regularly based on a new worksheet that I receive which has the part numbers and new pricing. How do I update pricing for thousands of parts without having to look at each row and column. example: wks "A": Vendor SKUNumber Descr Price 0120 Bulb 1.35 0115 Pen 2.68 wks "B": Vendor SKUNumber Descr Price 0120 Bulb 1.44 0115 Pen 2.78 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to update column info based on column info from another worksh
A Non-VBA Solution....
Insert a helper column just to the left of the current price, this helper column will be deleted later. Assuming the following: SKU (part number) is in column A, unit price is in column D, your helper column is column C. And assuming your 'price updates' have SKU in column A, and new price in column C. Type the following formula in helper column (C) in wkbk A. =IF(COUNTIF([wkB.xls]Sheet1!$A$2:$A$100,$A2)=1,VLOOKUP($A2,[wkB.xls]Sheet1!$A$2:$N$5,2,FALSE),"") This will populate column C with ONLY the new prices, leaving prices that remain the same as blank. Highlight the entire data selection, Data--Filter--AutoFilter. Filter for NonBlanks on the newprice column, this should leave only the data for cells that have new prices. Highlight columns C and D and use Edit--Fill--Right. This will move the formulas moving over dragging along the new price. NOTE: Ensure you are filtered, as you don't want to erase the old prices. Then show all (Data--Filter--Show All). Highlight the entire column of prices (column D), copy, paste special in its own column (still column D). Then delete column C. I am sure there are VBA methods too. -- John C "Hummingbird" wrote: I have a worksheet that has many columns. One of the columns contains Vendor Sku Numbers and another column on the same worksheet contains Price information for that Part Number. I need to update pricing information regularly based on a new worksheet that I receive which has the part numbers and new pricing. How do I update pricing for thousands of parts without having to look at each row and column. example: wks "A": Vendor SKUNumber Descr Price 0120 Bulb 1.35 0115 Pen 2.68 wks "B": Vendor SKUNumber Descr Price 0120 Bulb 1.44 0115 Pen 2.78 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to update column info based on column info from another wo
I tried what you suggested, and while this worked for some of the part
numbers, it did not work for all. I did not mention the following in my original question, but there may be part numbers in A that are not in B and vice versa. Does the solution you suggested account for the fact that there may be additional (different) part numbers in Worksheet A that may not be in Worksheet B? and Vice Versa? Also, both workheets may contain a part number twice. Would the non-VBA solution you sent need to be tweaked for these additional considerations? "John C" wrote: A Non-VBA Solution.... Insert a helper column just to the left of the current price, this helper column will be deleted later. Assuming the following: SKU (part number) is in column A, unit price is in column D, your helper column is column C. And assuming your 'price updates' have SKU in column A, and new price in column C. Type the following formula in helper column (C) in wkbk A. =IF(COUNTIF([wkB.xls]Sheet1!$A$2:$A$100,$A2)=1,VLOOKUP($A2,[wkB.xls]Sheet1!$A$2:$N$5,2,FALSE),"") This will populate column C with ONLY the new prices, leaving prices that remain the same as blank. Highlight the entire data selection, Data--Filter--AutoFilter. Filter for NonBlanks on the newprice column, this should leave only the data for cells that have new prices. Highlight columns C and D and use Edit--Fill--Right. This will move the formulas moving over dragging along the new price. NOTE: Ensure you are filtered, as you don't want to erase the old prices. Then show all (Data--Filter--Show All). Highlight the entire column of prices (column D), copy, paste special in its own column (still column D). Then delete column C. I am sure there are VBA methods too. -- John C "Hummingbird" wrote: I have a worksheet that has many columns. One of the columns contains Vendor Sku Numbers and another column on the same worksheet contains Price information for that Part Number. I need to update pricing information regularly based on a new worksheet that I receive which has the part numbers and new pricing. How do I update pricing for thousands of parts without having to look at each row and column. example: wks "A": Vendor SKUNumber Descr Price 0120 Bulb 1.35 0115 Pen 2.68 wks "B": Vendor SKUNumber Descr Price 0120 Bulb 1.44 0115 Pen 2.78 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to update column info based on column info from another wo
Got it now. Thanks for your help. I tweaked what you sent a little bit.
In my price update file, I had the SKU in column A, and new price in column B (not C as you originally suggested). Then, I removed all the duplicate part numbers from both spreadsheets. Last, I made an adjustment ito the range in the formula you sent. There were two places where I changed the range to $3500 in the formula to account for the number of rows in my spreadsheet. =IF(COUNTIF([wkB.xls]Sheet1!$A$2:$A$3500,$A2)=1,VLOOKUP($A2,[wkB.xls]Sheet1!$A$2:$N$3500,2,FALSE),"") This pulled the new prices in. Thanks so much! "Hummingbird" wrote: I tried what you suggested, and while this worked for some of the part numbers, it did not work for all. I did not mention the following in my original question, but there may be part numbers in A that are not in B and vice versa. Does the solution you suggested account for the fact that there may be additional (different) part numbers in Worksheet A that may not be in Worksheet B? and Vice Versa? Also, both workheets may contain a part number twice. Would the non-VBA solution you sent need to be tweaked for these additional considerations? "John C" wrote: A Non-VBA Solution.... Insert a helper column just to the left of the current price, this helper column will be deleted later. Assuming the following: SKU (part number) is in column A, unit price is in column D, your helper column is column C. And assuming your 'price updates' have SKU in column A, and new price in column C. Type the following formula in helper column (C) in wkbk A. =IF(COUNTIF([wkB.xls]Sheet1!$A$2:$A$100,$A2)=1,VLOOKUP($A2,[wkB.xls]Sheet1!$A$2:$N$5,2,FALSE),"") This will populate column C with ONLY the new prices, leaving prices that remain the same as blank. Highlight the entire data selection, Data--Filter--AutoFilter. Filter for NonBlanks on the newprice column, this should leave only the data for cells that have new prices. Highlight columns C and D and use Edit--Fill--Right. This will move the formulas moving over dragging along the new price. NOTE: Ensure you are filtered, as you don't want to erase the old prices. Then show all (Data--Filter--Show All). Highlight the entire column of prices (column D), copy, paste special in its own column (still column D). Then delete column C. I am sure there are VBA methods too. -- John C "Hummingbird" wrote: I have a worksheet that has many columns. One of the columns contains Vendor Sku Numbers and another column on the same worksheet contains Price information for that Part Number. I need to update pricing information regularly based on a new worksheet that I receive which has the part numbers and new pricing. How do I update pricing for thousands of parts without having to look at each row and column. example: wks "A": Vendor SKUNumber Descr Price 0120 Bulb 1.35 0115 Pen 2.68 wks "B": Vendor SKUNumber Descr Price 0120 Bulb 1.44 0115 Pen 2.78 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lookup info in one Column and then returning info in other columns | Excel Worksheet Functions | |||
Consolidate information from Column B Based on Info In Column A | Excel Worksheet Functions | |||
How do I sort by info in one column and it stay in line with info | Excel Worksheet Functions | |||
Update a column data with info from new worksheet | Excel Discussion (Misc queries) | |||
Link info in one cell to info in several cells in another column (like a database) | Excel Discussion (Misc queries) |