Forum Home
Press F1
Thread ID: 61084	2005-08-24 07:03:00	Changing Data in excel or Access	tvg70 (2047)	Press F1

Post ID	Timestamp	Content	User
383400	2005-08-24 07:03:00	Hello, I wonder if someone can help me with this tricky problem. I have two databases that I wish to link together using a key field. Both data bases us the same data link but one needs to be changed so it matches the other. At present it uses a alpha numeric code to identify the items. eg HABEWL1-1. 1)First I need to change the HABEWL1 – HABEWL99 to read HABEWL001-1 TO HABEWL099-1 The alpha part changes regularly to different alpha codes. 2 Next I need to delete the –1 of the end of the alphanumeric number. eg BEARWL001-1 becomes BEARWL001 But any that have a –2 or greater after the alphanumeric number needs to be changed to #2 eg GOATWL001-2 becomes GOATWL001#2. I hope this explains what I want to do clearly. The data can be either exported to excel of Access 2000. Any help of suggestions to Or where to go to find help on solving this problem will be gratefully appreciated. Regards Terry	tvg70 (2047)
383401	2005-08-24 11:23:00	Hi Terry This can definitely be done in Access (and Excel too), but I have a question. In your example, you state that HABEWL1 should be changed to HABEWL001-1, which is ok. You then mention that items like BEARWL001-1 need to be changed to BEARWL001 which is ok. BUT, you then say "any that have a –2 or greater after the alphanumeric number needs to be changed to #2". Under what situation will the -2 arise given we are changing codes like HABEWL1 to HABEWL1-1, so how does the 2 (or greater) get there? Is there another peice of info I am missing? Or are you talking about 2 different fields? Lastly, are you currently using Excel or Access? We might as well provide a solution for the platform you are currently using. Andrew :)	andrew93 (249)
383402	2005-08-24 12:41:00	They are in the one field, just that one system that I am linking two has been set up as in the set example . Why there are -2 is because when there is more than one of the same alaphanmeric name name it is given -2, -3 etc . For example GOATWL001, GOATWL001#2, GOATWL001#3 etc . I hope this clarifies the situation . The problem is that there are two systems that have been set up differently . The data use by one is related to data used in the other but for different purposes . The reason I mentioned excel and Access is that I have been using excel to correct the data and then convert them back to DB database files . I just thought that one program may be easier to do this than the other . Thank you for your input and time, I hope this helps to clarify things . Thanks Terry	tvg70 (2047)
383403	2005-08-26 08:42:00	Hi Terry Sorry for not getting back to you sooner. If you are already using Excel to manipulate the data, then keep doing it that way. I'm still not 100% sure why you would have multiple entries that require the differing suffix because it will be harder to link the item from one table to another (in Access) if there is a differing numerical suffix for what is essentially the same thing....but I digress.... Anyway, in Excel paste the list of items from your table. Sort the list on the code, in ascending order. I have assumed your list starts in cell A2 and have provided formulae for cells B2, C2 and D2. I think one of these 3 cells will contain the answer / format you are seeking. Please note that it is dependant upon the list in column A first having been sorted into alphabetical order and it assumes there are not more than 2 digits on the end of the code (per your example). Also, I have asumed you want the format you showed of x characters followed by 3 digits and for non-1 items to append #n (where n is a number greater than 1 for additional instances of the code) Insert the following formula into Cell B2 : =B2&IF(B2=B1,"-"&(1+VALUE(RIGHT(C1,LEN(C1)-FIND("-",C1)))),"-1") the following formula into Cell C2 : =B2&IF(LEFT(B2,LEN(B2)-3)=LEFT(B1,LEN(B1)-3), "-" & (1+VALUE(RIGHT(C1,LEN(C1)-FIND("-",C1)))),"-1") and the following formula into Cell D2 : =IF(RIGHT(C2,2)="-1", LEFT(C2,LEN(C2)-2), SUBSTITUTE(C2,"-","#")) I think column D has the final format you are seeking. HTH, Andrew :) P.S. Make sure you have a heading of some sort (or at least 4 spaces instead) in cell B1 - otherwise you get #VALUE! errors. If this wasn't a one-time exercise I would have trapped this error but you know....	andrew93 (249)
383404	2005-08-26 09:48:00	Thanks for that, will try that, thanks for your valuable input. Terry	tvg70 (2047)
1