Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Help please
Hi guys,
We have a little problem at work, that I suggested that you guys could help us with. We have a spreedsheet with 153 sheet within, and every sheet contains Articelnumbers and prices and some other fields. We can create another worksheet were we can get articlenumbers and prices from our system, the problem is that we would like to get these prices in the worksheet with 153 sheet. Is it possible to match the articlenumber from "our worksheet" and the articlenumbers from "153 sheet" if there is a match then it will take the price from "our worksheet" and put it in the "153 sheet". A articelnumber could exist in several sheets in the "153 sheet", there is one more problem, the articlenumbers from "our worksheet" and the "153 sheet" could be a little diffrent, I will give you an example in "153 sheet" an articlenumber could look like 123456-0012, but in "our worksheet" it could look like this 123456-12. We have decided that if there is a zero after the - then it will be removed, but there can also be an exact match, because latley they have registerd articles in that format. I hope that there is someone out there that could help me with this matter. Best Regards // Peter // |
#2
|
|||
|
|||
"Högberg" wrote in message ... Hi guys, We have a little problem at work, that I suggested that you guys could help us with. We have a spreedsheet with 153 sheet within, and every sheet contains Articelnumbers and prices and some other fields. We can create another worksheet were we can get articlenumbers and prices from our system, the problem is that we would like to get these prices in the worksheet with 153 sheet. Is it possible to match the articlenumber from "our worksheet" and the articlenumbers from "153 sheet" if there is a match then it will take the price from "our worksheet" and put it in the "153 sheet". A articelnumber could exist in several sheets in the "153 sheet", there is one more problem, the articlenumbers from "our worksheet" and the "153 sheet" could be a little diffrent, I will give you an example in "153 sheet" an articlenumber could look like 123456-0012, but in "our worksheet" it could look like this 123456-12. We have decided that if there is a zero after the - then it will be removed, but there can also be an exact match, because latley they have registerd articles in that format. I hope that there is someone out there that could help me with this matter. Best Regards // Peter // How come you have this difference? Has someone been sloppy when the articlenumbers were entered? I think you will need to write some code that will replace articlenumbers like 123456-0012 with 123456-12 before you can proceed. An alternative is to make an article number comparison function in VBA but that will make everything slower. It seems as if you are using Excel as a database. Is that so? /Fredrik |
#3
|
|||
|
|||
"Fredrik Wahlgren" skrev i meddelandet ... "Högberg" wrote in message ... Hi guys, We have a little problem at work, that I suggested that you guys could help us with. We have a spreedsheet with 153 sheet within, and every sheet contains Articelnumbers and prices and some other fields. We can create another worksheet were we can get articlenumbers and prices from our system, the problem is that we would like to get these prices in the worksheet with 153 sheet. Is it possible to match the articlenumber from "our worksheet" and the articlenumbers from "153 sheet" if there is a match then it will take the price from "our worksheet" and put it in the "153 sheet". A articelnumber could exist in several sheets in the "153 sheet", there is one more problem, the articlenumbers from "our worksheet" and the "153 sheet" could be a little diffrent, I will give you an example in "153 sheet" an articlenumber could look like 123456-0012, but in "our worksheet" it could look like this 123456-12. We have decided that if there is a zero after the - then it will be removed, but there can also be an exact match, because latley they have registerd articles in that format. I hope that there is someone out there that could help me with this matter. Best Regards // Peter // How come you have this difference? Has someone been sloppy when the articlenumbers were entered? I think you will need to write some code that will replace articlenumbers like 123456-0012 with 123456-12 before you can proceed. An alternative is to make an article number comparison function in VBA but that will make everything slower. It seems as if you are using Excel as a database. Is that so? /Fredrik The thing is that they started registering articles way back, like 15 years ago, then they thought that it wasnt necessary to use all numbers so they excluded the zero`s. No, we are not using excel as a database, we are using a MPS program that can convert information into excel were we think it´s easier to handle, the problem now is that we have this "153 sheet" with more than 3000 articles to fill in the prices, now it takes like 2-3 days for two persons to do the job. It would be easier if we could make a macro or some VBA code to take care of the problem. Best Regards // Peter // |
#4
|
|||
|
|||
"Högberg" wrote in message ... "Fredrik Wahlgren" skrev i meddelandet ... "Högberg" wrote in message ... Hi guys, We have a little problem at work, that I suggested that you guys could help us with. We have a spreedsheet with 153 sheet within, and every sheet contains Articelnumbers and prices and some other fields. We can create another worksheet were we can get articlenumbers and prices from our system, the problem is that we would like to get these prices in the worksheet with 153 sheet. Is it possible to match the articlenumber from "our worksheet" and the articlenumbers from "153 sheet" if there is a match then it will take the price from "our worksheet" and put it in the "153 sheet". A articelnumber could exist in several sheets in the "153 sheet", there is one more problem, the articlenumbers from "our worksheet" and the "153 sheet" could be a little diffrent, I will give you an example in "153 sheet" an articlenumber could look like 123456-0012, but in "our worksheet" it could look like this 123456-12. We have decided that if there is a zero after the - then it will be removed, but there can also be an exact match, because latley they have registerd articles in that format. I hope that there is someone out there that could help me with this matter. Best Regards // Peter // How come you have this difference? Has someone been sloppy when the articlenumbers were entered? I think you will need to write some code that will replace articlenumbers like 123456-0012 with 123456-12 before you can proceed. An alternative is to make an article number comparison function in VBA but that will make everything slower. It seems as if you are using Excel as a database. Is that so? /Fredrik The thing is that they started registering articles way back, like 15 years ago, then they thought that it wasnt necessary to use all numbers so they excluded the zero`s. No, we are not using excel as a database, we are using a MPS program that can convert information into excel were we think it´s easier to handle, the problem now is that we have this "153 sheet" with more than 3000 articles to fill in the prices, now it takes like 2-3 days for two persons to do the job. It would be easier if we could make a macro or some VBA code to take care of the problem. Best Regards // Peter // Let's see if I understand this. 1) You have a workbook with 153 worksheets 2) Your original problem seems to be that you want to update the price in those 153 sheets from a generated Excel file, created by something like Visma. 3) Unfortunately, the articlenumber in those 153 sheets don't always match what you have in the generated workbook How are these 153 sheets organized. To me it seems as if it would be possible to generate a workbook with these 153 sheets from the generated sheet Another approach would be to create a user definde function which would query the data from the MPS database. This solution requires that you know your datbase. Essentially, instead of writing down a price you would enter =GetPrice("123456-12 ") and you would get some value. You would have to write this function in such a way that it would recognize 123456-0012 as being equivalent unless you are willing to clean up these sheets manually. /Fredrik |
#5
|
|||
|
|||
"Fredrik Wahlgren" skrev i meddelandet ... "Högberg" wrote in message ... "Fredrik Wahlgren" skrev i meddelandet ... "Högberg" wrote in message ... Hi guys, We have a little problem at work, that I suggested that you guys could help us with. We have a spreedsheet with 153 sheet within, and every sheet contains Articelnumbers and prices and some other fields. We can create another worksheet were we can get articlenumbers and prices from our system, the problem is that we would like to get these prices in the worksheet with 153 sheet. Is it possible to match the articlenumber from "our worksheet" and the articlenumbers from "153 sheet" if there is a match then it will take the price from "our worksheet" and put it in the "153 sheet". A articelnumber could exist in several sheets in the "153 sheet", there is one more problem, the articlenumbers from "our worksheet" and the "153 sheet" could be a little diffrent, I will give you an example in "153 sheet" an articlenumber could look like 123456-0012, but in "our worksheet" it could look like this 123456-12. We have decided that if there is a zero after the - then it will be removed, but there can also be an exact match, because latley they have registerd articles in that format. I hope that there is someone out there that could help me with this matter. Best Regards // Peter // How come you have this difference? Has someone been sloppy when the articlenumbers were entered? I think you will need to write some code that will replace articlenumbers like 123456-0012 with 123456-12 before you can proceed. An alternative is to make an article number comparison function in VBA but that will make everything slower. It seems as if you are using Excel as a database. Is that so? /Fredrik The thing is that they started registering articles way back, like 15 years ago, then they thought that it wasnt necessary to use all numbers so they excluded the zero`s. No, we are not using excel as a database, we are using a MPS program that can convert information into excel were we think it´s easier to handle, the problem now is that we have this "153 sheet" with more than 3000 articles to fill in the prices, now it takes like 2-3 days for two persons to do the job. It would be easier if we could make a macro or some VBA code to take care of the problem. Best Regards // Peter // Let's see if I understand this. 1) You have a workbook with 153 worksheets 2) Your original problem seems to be that you want to update the price in those 153 sheets from a generated Excel file, created by something like Visma. 3) Unfortunately, the articlenumber in those 153 sheets don't always match what you have in the generated workbook How are these 153 sheets organized. To me it seems as if it would be possible to generate a workbook with these 153 sheets from the generated sheet Another approach would be to create a user definde function which would query the data from the MPS database. This solution requires that you know your datbase. Essentially, instead of writing down a price you would enter =GetPrice("123456-12 ") and you would get some value. You would have to write this function in such a way that it would recognize 123456-0012 as being equivalent unless you are willing to clean up these sheets manually. /Fredrik 1) Yes 2) Yes 3) Yes The "153 sheet" looks like this column A here we find the article number, columns B-E are used for other information about the articles. Column F here we wanna have the prices. "Our worksheet" looks almost the same Column A there we find the articlenumber, but here we find the price in column G. I dont think that this is a big problem, but I´m not the man to solve it. Best Regards // Peter // |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|