Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 5
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1,358
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 5
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 5
Default 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
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
Lookup info in one Column and then returning info in other columns Cyndi513 Excel Worksheet Functions 1 June 23rd 08 02:36 PM
Consolidate information from Column B Based on Info In Column A Consol. Info from One Column to another Excel Worksheet Functions 1 October 27th 07 04:02 PM
How do I sort by info in one column and it stay in line with info stephanie Excel Worksheet Functions 2 March 14th 07 06:43 PM
Update a column data with info from new worksheet BRB Excel Discussion (Misc queries) 3 November 21st 06 02:57 PM
Link info in one cell to info in several cells in another column (like a database) hansdiddy Excel Discussion (Misc queries) 1 February 22nd 06 03:27 AM


All times are GMT +1. The time now is 11:42 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"