Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Updating lists
Hi,
In our company we have a very long pricelist in an Excel-file. Once a week we get a list with updated prices, also in Excel-format. Is there an easy way to update our list with the prices from the updating list? We have a Product ID that is the identification in both lists. However we do not want our list to be updated with products that are not already in our list. Is it also possible to add information to a column "Modified date"? Thanks in advance |
#2
|
|||
|
|||
There's a nice worksheet function that can be used to bring over values from
another worksheet when there's a unique key that can be matched on. I'd insert a new column with a nice label to retrieve that newer value and then use a formula like: =vlookup(a1,sheet2!a:b,2,false) to match up the key in A1 with sheet2's column A. It brings back column 2 for that exact match. Then you can compare the values to see if you really, really want to overwrite your values. (I've never seen data come in that I would trust without checking.) =vlookup() requires that the key value in the table be in the leftmost column of the range (doesn't have to be column A, though. It could be column E in a range of E:G.) If your key value is to the right of the new price, you can use =index(match()). Debra Dalgleish has nice instructions on both these (with pictures!) at: http://www.contextures.com/xlFunctions02.html http://www.contextures.com/xlFunctions03.html Prixton wrote: Hi, In our company we have a very long pricelist in an Excel-file. Once a week we get a list with updated prices, also in Excel-format. Is there an easy way to update our list with the prices from the updating list? We have a Product ID that is the identification in both lists. However we do not want our list to be updated with products that are not already in our list. Is it also possible to add information to a column "Modified date"? Thanks in advance -- Dave Peterson |
#3
|
|||
|
|||
Thank you, Dave!
I have used the VLOOKUP. However, When having used the VLOOKUP I get #N/A when there is no match.. I have tried Copy/Paste special/values and after that Replace #N/A with "" and after that I have pasted it into the original Price Column with Paste special/Values and Skip blanks. It works when doing it manually, but when I do it recording a macro it does not work. There I get the #N/A again. I cannot find out what I am doing wrong and I have tried so many times. Is there something you or someone else can help me with "Dave Peterson" wrote in message ... There's a nice worksheet function that can be used to bring over values from another worksheet when there's a unique key that can be matched on. I'd insert a new column with a nice label to retrieve that newer value and then use a formula like: =vlookup(a1,sheet2!a:b,2,false) to match up the key in A1 with sheet2's column A. It brings back column 2 for that exact match. Then you can compare the values to see if you really, really want to overwrite your values. (I've never seen data come in that I would trust without checking.) =vlookup() requires that the key value in the table be in the leftmost column of the range (doesn't have to be column A, though. It could be column E in a range of E:G.) If your key value is to the right of the new price, you can use =index(match()). Debra Dalgleish has nice instructions on both these (with pictures!) at: http://www.contextures.com/xlFunctions02.html http://www.contextures.com/xlFunctions03.html Prixton wrote: Hi, In our company we have a very long pricelist in an Excel-file. Once a week we get a list with updated prices, also in Excel-format. Is there an easy way to update our list with the prices from the updating list? We have a Product ID that is the identification in both lists. However we do not want our list to be updated with products that are not already in our list. Is it also possible to add information to a column "Modified date"? Thanks in advance -- Dave Peterson |
#4
|
|||
|
|||
Using Dave's original formula as the basis, this would replace #N/A errors
with a null: =IF(ISNA(MATCH(A1,Sheet2!A:A,0)),"",VLOOKUP(A1,She et2!A:B,2,0)) -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Prixton" wrote in message ... Thank you, Dave! I have used the VLOOKUP. However, When having used the VLOOKUP I get #N/A when there is no match.. I have tried Copy/Paste special/values and after that Replace #N/A with "" and after that I have pasted it into the original Price Column with Paste special/Values and Skip blanks. It works when doing it manually, but when I do it recording a macro it does not work. There I get the #N/A again. I cannot find out what I am doing wrong and I have tried so many times. Is there something you or someone else can help me with "Dave Peterson" wrote in message ... There's a nice worksheet function that can be used to bring over values from another worksheet when there's a unique key that can be matched on. I'd insert a new column with a nice label to retrieve that newer value and then use a formula like: =vlookup(a1,sheet2!a:b,2,false) to match up the key in A1 with sheet2's column A. It brings back column 2 for that exact match. Then you can compare the values to see if you really, really want to overwrite your values. (I've never seen data come in that I would trust without checking.) =vlookup() requires that the key value in the table be in the leftmost column of the range (doesn't have to be column A, though. It could be column E in a range of E:G.) If your key value is to the right of the new price, you can use =index(match()). Debra Dalgleish has nice instructions on both these (with pictures!) at: http://www.contextures.com/xlFunctions02.html http://www.contextures.com/xlFunctions03.html Prixton wrote: Hi, In our company we have a very long pricelist in an Excel-file. Once a week we get a list with updated prices, also in Excel-format. Is there an easy way to update our list with the prices from the updating list? We have a Product ID that is the identification in both lists. However we do not want our list to be updated with products that are not already in our list. Is it also possible to add information to a column "Modified date"? Thanks in advance -- Dave Peterson |
#5
|
|||
|
|||
RagDyer gave you an alternate formula, but my guess about why your macro still
returned #n/a's is that you didn't convert to values correctly. And then you're replace wouldn't work. But that's just a wild guess. If you can't get your code working, you may want to post the relevant portions (not the workbook--just the code). Prixton wrote: Thank you, Dave! I have used the VLOOKUP. However, When having used the VLOOKUP I get #N/A when there is no match.. I have tried Copy/Paste special/values and after that Replace #N/A with "" and after that I have pasted it into the original Price Column with Paste special/Values and Skip blanks. It works when doing it manually, but when I do it recording a macro it does not work. There I get the #N/A again. I cannot find out what I am doing wrong and I have tried so many times. Is there something you or someone else can help me with "Dave Peterson" wrote in message ... There's a nice worksheet function that can be used to bring over values from another worksheet when there's a unique key that can be matched on. I'd insert a new column with a nice label to retrieve that newer value and then use a formula like: =vlookup(a1,sheet2!a:b,2,false) to match up the key in A1 with sheet2's column A. It brings back column 2 for that exact match. Then you can compare the values to see if you really, really want to overwrite your values. (I've never seen data come in that I would trust without checking.) =vlookup() requires that the key value in the table be in the leftmost column of the range (doesn't have to be column A, though. It could be column E in a range of E:G.) If your key value is to the right of the new price, you can use =index(match()). Debra Dalgleish has nice instructions on both these (with pictures!) at: http://www.contextures.com/xlFunctions02.html http://www.contextures.com/xlFunctions03.html Prixton wrote: Hi, In our company we have a very long pricelist in an Excel-file. Once a week we get a list with updated prices, also in Excel-format. Is there an easy way to update our list with the prices from the updating list? We have a Product ID that is the identification in both lists. However we do not want our list to be updated with products that are not already in our list. Is it also possible to add information to a column "Modified date"? Thanks in advance -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I return dynamic lists? | Excel Worksheet Functions | |||
Comparing multiple lists | Excel Worksheet Functions | |||
Comparing 2 Customer Lists to Identify Shared Customers | Excel Worksheet Functions | |||
How do I compare two lists of names in excel? | Excel Discussion (Misc queries) | |||
Aligning Two Lists in Excel | Excel Discussion (Misc queries) |