|
Is this possible with excel ?
I have two tab delimited spreadsheet files of a product database. My
MAIN concern here is that I need to import the prices from one spreadsheet, to a specific column in my original spreadsheet, and I need those prices to correspond with the respective product IDs. Is this possible ??? thanks in advance guys! |
Is this possible with excel ?
It's likely very possible, but we'd need to see some sample data so we
know what data points are available to work with. Please post sample data. |
Is this possible with excel ?
Dave O wrote: It's likely very possible, but we'd need to see some sample data so we know what data points are available to work with. Please post sample data. Ok... My main spreadsheet (tab delimited file) I have it setup as follows: A B C D ProductID49483 Brief Description Image.jpg 3.00 ProductID93094 Brief Description Image.jpg 5.00 ProductID30940 Brief Description Image.jpg 8.00 my alternate spreadsheet (tab delimited file) is as follows: A B ProductID49483 4.50 ProductID93094 8.50 ProductID30940 9.50 I want to make it so that I can Import my alternate spreadsheet into my main spreadsheet, but I only want it to alter column D on my main spreadsheet (changing prices). I need a macro, or a formula which will import column B from my alternate spreadsheet into column D in my main spreadsheet. I also need this macro/formula to be able to correspond the price with the correct productID...so it changes the correct product to its designated price. Any Ideas guys??? |
Is this possible with excel ?
Have you tried using a VLOOKUP (using the fx button under the catagory =
Lookup & Reference)? Make sure you are able to sort in ascending order by the Product ID on both tabs. Then using column A from the Main spreadsheet as the Lookup Value, then on the alternate tab/spreadsheet use the columns A:B for the table array, the col_index_num will be "2" which will be referencing column B ( the prices), and you should set the Range_lookup to false because you want it to utilize exact matches on the Product IDs. Your formula would look something similar to this =VLOOKUP(A2,alternate!A:B,2,FALSE). Good Luck!! "ChrisB" wrote: Dave O wrote: It's likely very possible, but we'd need to see some sample data so we know what data points are available to work with. Please post sample data. Ok... My main spreadsheet (tab delimited file) I have it setup as follows: A B C D ProductID49483 Brief Description Image.jpg 3.00 ProductID93094 Brief Description Image.jpg 5.00 ProductID30940 Brief Description Image.jpg 8.00 my alternate spreadsheet (tab delimited file) is as follows: A B ProductID49483 4.50 ProductID93094 8.50 ProductID30940 9.50 I want to make it so that I can Import my alternate spreadsheet into my main spreadsheet, but I only want it to alter column D on my main spreadsheet (changing prices). I need a macro, or a formula which will import column B from my alternate spreadsheet into column D in my main spreadsheet. I also need this macro/formula to be able to correspond the price with the correct productID...so it changes the correct product to its designated price. Any Ideas guys??? |
Is this possible with excel ?
I received an #N/A error. I think this might be on the right track to
helping me....can you elaborate a bit more? I am referencing the main spreadsheet as alternate, and using A:G for the table array because A is what i need it to reference in the main spreadsheet and G is the prices on the alternate, using col_index_num 7 as that is the column number, all with your formula and im receiving that error. Smilingout_loud wrote: Have you tried using a VLOOKUP (using the fx button under the catagory = Lookup & Reference)? Make sure you are able to sort in ascending order by the Product ID on both tabs. Then using column A from the Main spreadsheet as the Lookup Value, then on the alternate tab/spreadsheet use the columns A:B for the table array, the col_index_num will be "2" which will be referencing column B ( the prices), and you should set the Range_lookup to false because you want it to utilize exact matches on the Product IDs. Your formula would look something similar to this =VLOOKUP(A2,alternate!A:B,2,FALSE). Good Luck!! "ChrisB" wrote: Dave O wrote: It's likely very possible, but we'd need to see some sample data so we know what data points are available to work with. Please post sample data. Ok... My main spreadsheet (tab delimited file) I have it setup as follows: A B C D ProductID49483 Brief Description Image.jpg 3.00 ProductID93094 Brief Description Image.jpg 5.00 ProductID30940 Brief Description Image.jpg 8.00 my alternate spreadsheet (tab delimited file) is as follows: A B ProductID49483 4.50 ProductID93094 8.50 ProductID30940 9.50 I want to make it so that I can Import my alternate spreadsheet into my main spreadsheet, but I only want it to alter column D on my main spreadsheet (changing prices). I need a macro, or a formula which will import column B from my alternate spreadsheet into column D in my main spreadsheet. I also need this macro/formula to be able to correspond the price with the correct productID...so it changes the correct product to its designated price. Any Ideas guys??? |
Is this possible with excel ?
Hello,
If you haven't already got a solution :) sorry for the late response - got tied up with work! If you want your prices in the main spreadsheet to be updated with the prices from the alternate spreadsheet then you will need to input the formula on the main spreadsheet because this is where the update should happen and reference the alternate spreadsheet as the table array because this is where the updated prices will come from....does that make sense? Where you input the formula is where the updated prices will be returned..so you want to put your formula on the tab that needs the new prices and reference the tab that has the info you need as the table array.... 1. Make sure that your product IDs on both tabs are in column A (the leftmost column) 2. Make sure both tabs have been sorted in ascending order by column A or the product ID - if your info isn't sort in ascending order then the formula will not work 3. on the tab that you want the prices to be updated (Main tab) insert a new, blank column next to the column with the prices. This is so you will not override your old prices until you are sure the formula works. 4. In the new blank column at the first row where you need an update price - input your VLookUp formula = VLOOKUP( 5. Now, since you are on the tab that needs the updated prices you are going to use the cell that contains the first product ID as the LookUp Value in the formula..this will be in column A because you put your product IDs in column A and that is how you are connecting these to spreadsheets by product ID...typically it is A2 because your headings are in row 1...your formula so far would be =VLOOKUP(A2, 6. Next you would input the table array into your formula this should reference the tab that has your updates prices. This is telling Excel where to go to find the new information to update the first tab....it should be your alternate tab and referencing all of the columns from A to where your new prices are located (A:B or A:G)..... 7. Depending on which column the new prices are located in that column # will be your col_index_num.. 8. Range_lookup will be false The returned values in the column you inserted should be your updated prices for that product ID on your original/main tab. If that is the case and it is correct (spot check 3 or 4 of them) then highlight this column and copy it. Then using Paste Special - Values paste your new prices over that same column (over the formula). You can then delete the column that contains the old prices :) "ChrisB" wrote: I received an #N/A error. I think this might be on the right track to helping me....can you elaborate a bit more? I am referencing the main spreadsheet as alternate, and using A:G for the table array because A is what i need it to reference in the main spreadsheet and G is the prices on the alternate, using col_index_num 7 as that is the column number, all with your formula and im receiving that error. Smilingout_loud wrote: Have you tried using a VLOOKUP (using the fx button under the catagory = Lookup & Reference)? Make sure you are able to sort in ascending order by the Product ID on both tabs. Then using column A from the Main spreadsheet as the Lookup Value, then on the alternate tab/spreadsheet use the columns A:B for the table array, the col_index_num will be "2" which will be referencing column B ( the prices), and you should set the Range_lookup to false because you want it to utilize exact matches on the Product IDs. Your formula would look something similar to this =VLOOKUP(A2,alternate!A:B,2,FALSE). Good Luck!! "ChrisB" wrote: Dave O wrote: It's likely very possible, but we'd need to see some sample data so we know what data points are available to work with. Please post sample data. Ok... My main spreadsheet (tab delimited file) I have it setup as follows: A B C D ProductID49483 Brief Description Image.jpg 3.00 ProductID93094 Brief Description Image.jpg 5.00 ProductID30940 Brief Description Image.jpg 8.00 my alternate spreadsheet (tab delimited file) is as follows: A B ProductID49483 4.50 ProductID93094 8.50 ProductID30940 9.50 I want to make it so that I can Import my alternate spreadsheet into my main spreadsheet, but I only want it to alter column D on my main spreadsheet (changing prices). I need a macro, or a formula which will import column B from my alternate spreadsheet into column D in my main spreadsheet. I also need this macro/formula to be able to correspond the price with the correct productID...so it changes the correct product to its designated price. Any Ideas guys??? |
Is this possible with excel ?
First off let me thank you for the detailed post with steps on how to
achieve and overcome my problem. I appreciate it more than you know :) Secondly, I have followed your steps to the T, and am getting this #N/A error. When I ask for help on the error, this is what excel says to me: Correct a #N/A error Occurs when a value is not available to a function or formula. Click the cell that displays the error, click the button that appears , and then click Trace Error if it appears. Review the possible causes and solutions. Possible causes and solutions Missing data, and #N/A or NA() has been entered in its place Replace #N/A with new data. Note You can enter #N/A in those cells where data is not yet available. Formulas that refer to those cells will then return #N/A instead of attempting to calculate a value. Giving an inappropriate value for the lookup_value argument in the HLOOKUP, LOOKUP, MATCH, or VLOOKUP worksheet function Make sure the lookup_value argument is the correct type of value- for example, a value or a cell reference, but not a range reference. Using the VLOOKUP, HLOOKUP, or MATCH worksheet function to locate a value in an unsorted table By default, functions that look up information in tables must be sorted in ascending order. However, the VLOOKUP and HLOOKUP worksheet functions contain a range_lookup argument that instructs the function to find an exact match even if the table is not sorted. To find an exact match, set the range_lookup argument to FALSE. The MATCH worksheet function contains a match_type argument that specifies the order the list must be sorted in to find a match. If the function cannot find a match, try changing the match_type argument. To find an exact match, set the match_type argument to 0. Using an argument in an array formula that is not the same number of rows or columns as the range that contains the array formula If the array formula has been entered into multiple cells, make sure the ranges referenced by the formula have the same number of rows and columns, or enter the array formula into fewer cells. For example, if the array formula has been entered into a range 15 rows high (C1:C15) and the formula refers to a range 10 rows high (A1:A10), the range C11:C15 will display #N/A. To correct this error, enter the formula into a smaller range (for example, C1:C10), or change the range to which the formula refers to the same number of rows (for example, A1:A15). Omitting one or more required arguments from a built-in or custom worksheet function Enter all arguments in the function. Using a custom worksheet function that is not available Make sure the workbook that contains the worksheet function is open and the function is working properly. Running a macro that enters a function that returns #N/A Make sure the arguments in the function are correct and in the correct position. Smilingout_loud wrote: Hello, If you haven't already got a solution :) sorry for the late response - got tied up with work! If you want your prices in the main spreadsheet to be updated with the prices from the alternate spreadsheet then you will need to input the formula on the main spreadsheet because this is where the update should happen and reference the alternate spreadsheet as the table array because this is where the updated prices will come from....does that make sense? Where you input the formula is where the updated prices will be returned..so you want to put your formula on the tab that needs the new prices and reference the tab that has the info you need as the table array.... 1. Make sure that your product IDs on both tabs are in column A (the leftmost column) 2. Make sure both tabs have been sorted in ascending order by column A or the product ID - if your info isn't sort in ascending order then the formula will not work 3. on the tab that you want the prices to be updated (Main tab) insert a new, blank column next to the column with the prices. This is so you will not override your old prices until you are sure the formula works. 4. In the new blank column at the first row where you need an update price - input your VLookUp formula = VLOOKUP( 5. Now, since you are on the tab that needs the updated prices you are going to use the cell that contains the first product ID as the LookUp Value in the formula..this will be in column A because you put your product IDs in column A and that is how you are connecting these to spreadsheets by product ID...typically it is A2 because your headings are in row 1...your formula so far would be =VLOOKUP(A2, 6. Next you would input the table array into your formula this should reference the tab that has your updates prices. This is telling Excel where to go to find the new information to update the first tab....it should be your alternate tab and referencing all of the columns from A to where your new prices are located (A:B or A:G)..... 7. Depending on which column the new prices are located in that column # will be your col_index_num.. 8. Range_lookup will be false The returned values in the column you inserted should be your updated prices for that product ID on your original/main tab. If that is the case and it is correct (spot check 3 or 4 of them) then highlight this column and copy it. Then using Paste Special - Values paste your new prices over that same column (over the formula). You can then delete the column that contains the old prices :) "ChrisB" wrote: I received an #N/A error. I think this might be on the right track to helping me....can you elaborate a bit more? I am referencing the main spreadsheet as alternate, and using A:G for the table array because A is what i need it to reference in the main spreadsheet and G is the prices on the alternate, using col_index_num 7 as that is the column number, all with your formula and im receiving that error. Smilingout_loud wrote: Have you tried using a VLOOKUP (using the fx button under the catagory = Lookup & Reference)? Make sure you are able to sort in ascending order by the Product ID on both tabs. Then using column A from the Main spreadsheet as the Lookup Value, then on the alternate tab/spreadsheet use the columns A:B for the table array, the col_index_num will be "2" which will be referencing column B ( the prices), and you should set the Range_lookup to false because you want it to utilize exact matches on the Product IDs. Your formula would look something similar to this =VLOOKUP(A2,alternate!A:B,2,FALSE). Good Luck!! "ChrisB" wrote: Dave O wrote: It's likely very possible, but we'd need to see some sample data so we know what data points are available to work with. Please post sample data. Ok... My main spreadsheet (tab delimited file) I have it setup as follows: A B C D ProductID49483 Brief Description Image.jpg 3.00 ProductID93094 Brief Description Image.jpg 5.00 ProductID30940 Brief Description Image.jpg 8.00 my alternate spreadsheet (tab delimited file) is as follows: A B ProductID49483 4.50 ProductID93094 8.50 ProductID30940 9.50 I want to make it so that I can Import my alternate spreadsheet into my main spreadsheet, but I only want it to alter column D on my main spreadsheet (changing prices). I need a macro, or a formula which will import column B from my alternate spreadsheet into column D in my main spreadsheet. I also need this macro/formula to be able to correspond the price with the correct productID...so it changes the correct product to its designated price. Any Ideas guys??? |
Is this possible with excel ?
could you give an example of how exactly your data is laid out on both tabs?
In addition, do you have repeating product Ids on either tab? "ChrisB" wrote: First off let me thank you for the detailed post with steps on how to achieve and overcome my problem. I appreciate it more than you know :) Secondly, I have followed your steps to the T, and am getting this #N/A error. When I ask for help on the error, this is what excel says to me: Correct a #N/A error Occurs when a value is not available to a function or formula. Click the cell that displays the error, click the button that appears , and then click Trace Error if it appears. Review the possible causes and solutions. Possible causes and solutions Missing data, and #N/A or NA() has been entered in its place Replace #N/A with new data. Note You can enter #N/A in those cells where data is not yet available. Formulas that refer to those cells will then return #N/A instead of attempting to calculate a value. Giving an inappropriate value for the lookup_value argument in the HLOOKUP, LOOKUP, MATCH, or VLOOKUP worksheet function Make sure the lookup_value argument is the correct type of value- for example, a value or a cell reference, but not a range reference. Using the VLOOKUP, HLOOKUP, or MATCH worksheet function to locate a value in an unsorted table By default, functions that look up information in tables must be sorted in ascending order. However, the VLOOKUP and HLOOKUP worksheet functions contain a range_lookup argument that instructs the function to find an exact match even if the table is not sorted. To find an exact match, set the range_lookup argument to FALSE. The MATCH worksheet function contains a match_type argument that specifies the order the list must be sorted in to find a match. If the function cannot find a match, try changing the match_type argument. To find an exact match, set the match_type argument to 0. Using an argument in an array formula that is not the same number of rows or columns as the range that contains the array formula If the array formula has been entered into multiple cells, make sure the ranges referenced by the formula have the same number of rows and columns, or enter the array formula into fewer cells. For example, if the array formula has been entered into a range 15 rows high (C1:C15) and the formula refers to a range 10 rows high (A1:A10), the range C11:C15 will display #N/A. To correct this error, enter the formula into a smaller range (for example, C1:C10), or change the range to which the formula refers to the same number of rows (for example, A1:A15). Omitting one or more required arguments from a built-in or custom worksheet function Enter all arguments in the function. Using a custom worksheet function that is not available Make sure the workbook that contains the worksheet function is open and the function is working properly. Running a macro that enters a function that returns #N/A Make sure the arguments in the function are correct and in the correct position. Smilingout_loud wrote: Hello, If you haven't already got a solution :) sorry for the late response - got tied up with work! If you want your prices in the main spreadsheet to be updated with the prices from the alternate spreadsheet then you will need to input the formula on the main spreadsheet because this is where the update should happen and reference the alternate spreadsheet as the table array because this is where the updated prices will come from....does that make sense? Where you input the formula is where the updated prices will be returned..so you want to put your formula on the tab that needs the new prices and reference the tab that has the info you need as the table array.... 1. Make sure that your product IDs on both tabs are in column A (the leftmost column) 2. Make sure both tabs have been sorted in ascending order by column A or the product ID - if your info isn't sort in ascending order then the formula will not work 3. on the tab that you want the prices to be updated (Main tab) insert a new, blank column next to the column with the prices. This is so you will not override your old prices until you are sure the formula works. 4. In the new blank column at the first row where you need an update price - input your VLookUp formula = VLOOKUP( 5. Now, since you are on the tab that needs the updated prices you are going to use the cell that contains the first product ID as the LookUp Value in the formula..this will be in column A because you put your product IDs in column A and that is how you are connecting these to spreadsheets by product ID...typically it is A2 because your headings are in row 1...your formula so far would be =VLOOKUP(A2, 6. Next you would input the table array into your formula this should reference the tab that has your updates prices. This is telling Excel where to go to find the new information to update the first tab....it should be your alternate tab and referencing all of the columns from A to where your new prices are located (A:B or A:G)..... 7. Depending on which column the new prices are located in that column # will be your col_index_num.. 8. Range_lookup will be false The returned values in the column you inserted should be your updated prices for that product ID on your original/main tab. If that is the case and it is correct (spot check 3 or 4 of them) then highlight this column and copy it. Then using Paste Special - Values paste your new prices over that same column (over the formula). You can then delete the column that contains the old prices :) "ChrisB" wrote: I received an #N/A error. I think this might be on the right track to helping me....can you elaborate a bit more? I am referencing the main spreadsheet as alternate, and using A:G for the table array because A is what i need it to reference in the main spreadsheet and G is the prices on the alternate, using col_index_num 7 as that is the column number, all with your formula and im receiving that error. Smilingout_loud wrote: Have you tried using a VLOOKUP (using the fx button under the catagory = Lookup & Reference)? Make sure you are able to sort in ascending order by the Product ID on both tabs. Then using column A from the Main spreadsheet as the Lookup Value, then on the alternate tab/spreadsheet use the columns A:B for the table array, the col_index_num will be "2" which will be referencing column B ( the prices), and you should set the Range_lookup to false because you want it to utilize exact matches on the Product IDs. Your formula would look something similar to this =VLOOKUP(A2,alternate!A:B,2,FALSE). Good Luck!! "ChrisB" wrote: Dave O wrote: It's likely very possible, but we'd need to see some sample data so we know what data points are available to work with. Please post sample data. Ok... My main spreadsheet (tab delimited file) I have it setup as follows: A B C D ProductID49483 Brief Description Image.jpg 3.00 ProductID93094 Brief Description Image.jpg 5.00 ProductID30940 Brief Description Image.jpg 8.00 my alternate spreadsheet (tab delimited file) is as follows: A B ProductID49483 4.50 ProductID93094 8.50 ProductID30940 9.50 I want to make it so that I can Import my alternate spreadsheet into my main spreadsheet, but I only want it to alter column D on my main spreadsheet (changing prices). I need a macro, or a formula which will import column B from my alternate spreadsheet into column D in my main spreadsheet. I also need this macro/formula to be able to correspond the price with the correct productID...so it changes the correct product to its designated price. Any Ideas guys??? |
Is this possible with excel ?
I have a spreadsheet titled "memory" which holds about 600 products.
that spreadsheets columns are as follows: A (product_ID) , B (empty) , C (product description) , D (product_img), E (product_price) , F (product_name), G (category). My Alternate spreadsheet titled "pricelist" which holds the CORRECT prices but also about 9,000 products...I need to import into my "memory" spreadsheet are as follows: A (product_id), B (manufacturer_product_id) , C (manufacturer) , D (category) , E (type), F (price) , G (in stock or not) , H (Weight). I made sure both the product ids were in the leftmost column (A) , and also in ascending order before continuing with the formula. I am pretty positive none of the ids are repeating. Smilingout_loud wrote: could you give an example of how exactly your data is laid out on both tabs? In addition, do you have repeating product Ids on either tab? |
Is this possible with excel ?
Thanks for the info. O.k. I made a sample of your spreadsheet (tabs names
and column headings, also filled in sample product IDs and prices) with the information that you provided. Just to make sure this works for me..On the sample spreadsheet on tab - memory I inserted a new column to the left of E (product_price) to put the Vlookup into...then used the following formula: =VLOOKUP(A2,pricelist!A:F,6,FALSE) It worked and pulled across the corresponding price for the first product ID...just remember if it works for you to copy/paste special values so that you keep the new prices and not the formula!! :) I'll be here for about 45 more minutes hope it works!! "ChrisB" wrote: I have a spreadsheet titled "memory" which holds about 600 products. that spreadsheets columns are as follows: A (product_ID) , B (empty) , C (product description) , D (product_img), E (product_price) , F (product_name), G (category). My Alternate spreadsheet titled "pricelist" which holds the CORRECT prices but also about 9,000 products...I need to import into my "memory" spreadsheet are as follows: A (product_id), B (manufacturer_product_id) , C (manufacturer) , D (category) , E (type), F (price) , G (in stock or not) , H (Weight). I made sure both the product ids were in the leftmost column (A) , and also in ascending order before continuing with the formula. I am pretty positive none of the ids are repeating. Smilingout_loud wrote: could you give an example of how exactly your data is laid out on both tabs? In addition, do you have repeating product Ids on either tab? |
Is this possible with excel ?
I copied everything to a flash drive to try to work on this over the
weekend. Maybe something is wrong within my spreadsheet, Im gonna try to find a way to show you parts of my spreadsheet, there might be something wrong with it.` |
Is this possible with excel ?
I'm so sorry it just occurred to me that you are not working with 2 tabs in
one workbook but 2 completely seperate files all together. I do apologize - this is what happens when you muli-task. If that is the case then the vlookup would look like this: =VLOOKUP(A2,pricelist.xls!$A:$F,6,FALSE) just add ".xls" to the end of pricelist just to be certain make sure to have both files open "Smilingout_loud" wrote: Thanks for the info. O.k. I made a sample of your spreadsheet (tabs names and column headings, also filled in sample product IDs and prices) with the information that you provided. Just to make sure this works for me..On the sample spreadsheet on tab - memory I inserted a new column to the left of E (product_price) to put the Vlookup into...then used the following formula: =VLOOKUP(A2,pricelist!A:F,6,FALSE) It worked and pulled across the corresponding price for the first product ID...just remember if it works for you to copy/paste special values so that you keep the new prices and not the formula!! :) I'll be here for about 45 more minutes hope it works!! "ChrisB" wrote: I have a spreadsheet titled "memory" which holds about 600 products. that spreadsheets columns are as follows: A (product_ID) , B (empty) , C (product description) , D (product_img), E (product_price) , F (product_name), G (category). My Alternate spreadsheet titled "pricelist" which holds the CORRECT prices but also about 9,000 products...I need to import into my "memory" spreadsheet are as follows: A (product_id), B (manufacturer_product_id) , C (manufacturer) , D (category) , E (type), F (price) , G (in stock or not) , H (Weight). I made sure both the product ids were in the leftmost column (A) , and also in ascending order before continuing with the formula. I am pretty positive none of the ids are repeating. Smilingout_loud wrote: could you give an example of how exactly your data is laid out on both tabs? In addition, do you have repeating product Ids on either tab? |
Is this possible with excel ?
|
Is this possible with excel ?
|
Is this possible with excel ?
I got it to work!!! GREAT!! thanks so much.... Now is it possible to
get it automated so it filters through the entire list? or do I have to do this on my own ? |
| All times are GMT +1. The time now is 10:13 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com