PDA

View Full Version : One final code ( I hope)



iki
11-24-2008, 02:09 PM
Alright, I have asked a few times for various cell searches and hopefully this will be my last. I should have a clear understanding of how the formulas work so I can begin to start making my own instead of always asking for your help. So here goes

Thousands of entries in the following format: ESK1018, ESC1932, ESCG9923 All 7-8 digit codes. I am trying to turn this into multiple columns to organize all the data.
The first 3 digits are always letters and indicate what type of job it is. If the 4th digit is the letter R then it indicates that the job was a rebuild as opposed to a brand new job. Finally the 4-5th digit indicates what type of job, 1 = 100, 9 = 900
SKIP ESK
CAGE ECG
SKIP/CATE ESC
LOADING CHUTE ELC
MISC EMS
LOADING STATION ELS
HEAD SHEAVE EHS
CAR ECR
LEVELOK ELV
ENGINEERING EEN
CUSTOM MACHINGING ECM


I will have to separate these into 3 different columns. So for example
ESK1018 and ECGR9923 =
JOB TYPE TYPE OF JOB
SKIP New 100
CAGE REBUILD 900
Any help you can give me on how to create the formula or method to get what I need will be very much appreciated.

Kenneth Hobs
11-24-2008, 03:25 PM
I think I know what you mean but if you post a short xls example, it will be easier to help. You probably want the " SKIP ESK" and such to be in a two column table so that you can use =vLookup.

Also, detail what the 4 digit letters mean other than Rebuild. Here again, another column of 2 for letters and what they mean could be used.

iki
11-25-2008, 06:18 AM
Ok added a sample .xlm, how do you use v-lookups?

iki
11-25-2008, 06:20 AM
sorry, forgot to add something into the file. Just put to entries to show what i would like outputed

Kenneth Hobs
11-25-2008, 07:06 AM
Press F1 and type vLookup to find help for the command. You can also find it by clicking the Fx button on the Formula Bar.

See the attachment. The first step is to create the lookup list. I named it PickList. Then it is a simple matter to add the formulas.

Picklist:
ESK SKIP ECG CAGE ESC SKIP/CATE ELC LOADING CHUTE EMS MISC ELS LOADING STATION EHS HEAD SHEAVE ECR CAR ELV LEVELOK EEN ENGINEERING ECM CUSTOM MACHINGING
Formulas for B2, C2, and D2:
=VLOOKUP(LEFT(A2,3),PickList,2,0)
=IF(MID(A2,4,1)="R","Rebuild","New")
=VALUE(LEFT(RIGHT(A2,4),1)&"00")

iki
11-25-2008, 07:44 AM
Press F1 and type vLookup to find help for the command. You can also find it by clicking the Fx button on the Formula Bar.

See the attachment. The first step is to create the lookup list. I named it PickList. Then it is a simple matter to add the formulas.

Picklist:
ESK SKIP ECG CAGE ESC SKIP/CATE ELC LOADING CHUTE EMS MISC ELS LOADING STATION EHS HEAD SHEAVE ECR CAR ELV LEVELOK EEN ENGINEERING ECM CUSTOM MACHINGING
Formulas for B2, C2, and D2:
=VLOOKUP(LEFT(A2,3),PickList,2,0)
=IF(MID(A2,4,1)="R","Rebuild","New")
=VALUE(LEFT(RIGHT(A2,4),1)&"00")

thank you so much that worked perfectly. Are there any books or online excel guides that you would recomend? never relized how powerfull excel could be.

Kenneth Hobs
11-25-2008, 09:20 AM
Not sure what you need to learn. These forums tend to be a good source.

John Walkenbach's "Excel 2003 Power Programming with VBA" book is one of the few that I have purchased. See this link for some sources. http://www.vbaexpress.com/resources.php#Excel