How to assign a letter code based on values in previous columns

[chunt]

Hello,

I have the following data:

Responses: Code:
None NR
Hang up after accepting call AH 1
Claims Paid CP 1 1
Invoice Copy Hangup ICH 1 2
Invoice Copy Correct Address ICCA 1 2 1
Invoice Copy Inccorect Address ICIA 1 2 2
Payment Option Hangup POH 1 3
Call Client Company CCC 1 3 2
Call Client Company CCC 1 4
Wrong Party Left Message WPLM 2
Wrong Phone Number WPN 2 1


The above are possible codes and the combinations of numbers that
would produce them.

I will have hundreds of rows that could produce any of the above
combinations. I need to figure out how I can use a function that
would assign the appropriate code to each row.

I've tried a combination of OR and IF statements nested together, but
they will only return TRUE or FALSE and not the appropriate code
(#VALUE!). I guess the OR will only return a boolean and not text.

Do I need to use an array? I have no idea how to use them.

Any help would be greatly appreciated.

Thank you,
Chris.

 

[Pete_UK]

Could you explain what you have and what you want to happen in a bit
more detail?

Will you have one column of numeric codes which might be 1, 11, 12,
121, 122 etc and you want to show the description which the code
represents, or will your numbers be in different columns, or are your
codes actually NR, AH, CP etc ???

Please elaborate.

Pete

 

[chunt]

Hi Pete,

The numbers are actually in different columns.

I'll try to reformat this.

Responses: _____________________Code: _______Number combinations
(each number in its own column)

None____________________________NR
Hang up after accepting call_________AH __________1
Claims Paid______________________CP __________1__1
Invoice Copy Hangup_______________ICH__________1__2
Invoice Copy Correct Address________ICCA_________1__2__1
Invoice Copy Inccorect Address______ ICIA__________1__2__2
Payment Option Hangup____________POH _________1__3
Call Client Company_______________ CCC _________1__3__2
Call Client Company_______________ CCC _________1__4
Wrong Party Left Message__________ WPLM _______2
Wrong Phone Number______________ WPN ________2__1


Hopefully that'll help deliniate the columns.

I will have hundreds of rows with with the above number combinations.
( _ Means next column) For example:

1_2
1_2_2
1_4
2
1_3_2
1_4
1_2
etc.

Each number combination is assciated withthe 2-4 letter code before it
in the top table. I need to assign the appropriate code to each row,
in a new column so that the codes are all in the same column.

I hope that this helps. I'm still at a loss of how to do this.

Thank you so much.

Cheers,
Chris.

 

[Pete_UK]

I put your table in A1 to E11 and then copied your example codes from
A15 to C21. I entered this formula in F1:

=C1&D1&E1

and copied down to F11, which gave the composite codes:

F1:
F2: 1
F3: 11
F4: 12
F5: 121
F6: 122
F7: 13
F8: 132
F9: 14
F10: 2
F11: 21

Then I entered these formulae in the cells shown:

D15: =INDEX(A$1:B$11,MATCH(A15&B15&C15,F$1:F$11,0),2)
E15: =INDEX(A$1:B$11,MATCH(A15&B15&C15,F$1:F$11,0),1)

and copied these down to row 21. This resulted in:

ICH____Invoice Copy Hangup
ICIA____Invoice Copy Inccorect Address
CCC____Call Client Company
WPLM__Wrong Party Left Message
CCC____Call Client Company
CCC____Call Client Company
ICH_____Invoice Copy Hangup

which is what I think you want.

Hope this helps.

Pete



The first entry is blank