Making 'Category' using formula criteria

[John Lemmon]

I have data that has columns that includes things like Account-code, Procedure-name, Step-name....

e.g.
Acc Proc Step
ABC123 PROC1 STEP1
23ZX APROC 1S
470168 X1 STP3

I want to create a helper column called 'Category' and have a formula that will create this. However (and this is my question), my data is different for many different clients and I'd like to externalise that formula... so for example for Client1 the formula would use first 3 Chars of Account to know the Category, for another Client the last 2 Chars are used and so on...

So, my question is, how would you go about creating a helper column in the data that 'refers' to another cell that contains the forumula to be used (to allow the client to specify how the category is set), a follow-up question would be are there any clever ways to set the category column/formula to support mulltiple columns.

Basically what I'm looking for is pointers/ideas on a good way to set a Category 'helper' column, but give the control of entering the criteria to set this to the End-User.

 

[NARAYANK991]

Hi John ,

I think the explanation is still not adequate ; can you indicate all the possibilities ? How many ways of setting the category are you going to allow ?

Is it possible to have a sample workbook with real-life working data , rather than made-up data , and the desired output of what the formula should result in ?

Narayan

 

[John Lemmon]

Real Data:

Product GroupApplication GroupSMF30JBNSMF30STMSMF30PGM
z/OSOTHERMSTJCL00IEEMB860
JES2OTHERJES2MONIEFPROCHASJES2$
z/OSOTHERDUMPSRVDUMPSRVIEAVTDSV
z/OSOTHERIOSASIEFPROCIOSVROUT
OmegamonOTHERKCNDLCNDLKCNDLMN
OTHEROTHERCEAIEFPROCCEAINIT
USSOTHEROMVSOMVSBPXINIT
OTHEROTHERJESXCFIEFPROCIXZIX00
OTHEROTHERHWIBCPIIIEFPROCHWIAMIN2
OTHEROTHERDEVMANIEFPROCDMOVS001
WLMOTHERWLMIEFPROCIWMINJST
USSOTHERBPXOINITBPXOINITBPXPINPR
OTHEROTHERANTMAINIEFPROCANTMAIN
OTHEROTHERMIMGRMIMGRMIMDRBGN
z/OSOTHERXCFASIEFPROCIXCINJST
DFSMSOTHERCATALOGIEFPROCIGG0CLX0
DFSMSOTHERSMSVSAMIEFPROCIDAVSJST
SMFOTHERSMFIEFPROCIFASMF
z/OSOTHERIXGLOGRIEFPROCIXGBLF01
M204OTHERDBLRXDMDBLRM204XDM
TCP/IPOTHERRESOLVEREZBREINIEZBREINI
DB2OTHERDILBIRLMDXRRLM00
OTHEROTHEROSZRTCSOSZRTCSOSZMOSYS
OTHEROTHERVLFVLFCOFMINIT
TSOOTHERTSOSTEP1IKTCAS00

The Formula in Product Group Column is:
=IFERROR(INDEX(ProductGroup,MATCH(LEFT(SMF30PGM,3)&"*",ProductPrefix,0)),"OTHER")

I have Ranges called ProductGroup and ProductPrefix like this:
ProductGroupProductPrefix
CICSDFH*
CICS CPSMEYU*
IMSDFS*
z/OSCQS*
IMS ConnectHWS*
BMC IMS UtilSSLOAD*
DB2DSN*
DB2DXR*
BMC DB2 UtilADU*
MQCSQ*
z/OSIEA*
z/OSIEE*
z/OSIGE*
z/OSIGF*
z/OSIXG*
z/OSATR*
z/OSIOS*
z/OSIXC*
RACFIRR*

So, what I'm looking for is a way to externalise that Formula: INDEX(ProductGroup,MATCH(LEFT(SMF30PGM,3)&"*",ProductPrefix,0)) so that it is edited/maintained by the end-user - held in special cell, or even some interface to allow the user to enter it.

 

[John Lemmon]

Oh Yuk, it's not kept the formatting... Here's the data attached this time