Formula to provide order

[barcelonat]

Hello - I have four services, Service1, Service 2, Service 3 and Service 4. These may existing in a column multiple times and in any order. I they all exist I want to code them with A,B,C and D, respectively. However if one or more doesn't appear then i want it to just be A, or A,b or A,B,C.

So Service 1 will always be 'A', but if Service 1 doesn't exist, then Service 2 is 'A' and so on. And if Service 1 and 3 exists and Service doesn't, then Service 3 would be 'B'.

I have tried to do this with IF/COUNTIF Formula a 101 different ways, but I cannot get all the iterations to play out. Any ideas? TIA

One fail attempt was:

=IF(COUNTIF(I:I,"Service 1")>0,IF(I15="Service 1","A",IF(COUNTIF(I:I,"Service 1")*COUNTIF(I:I,"Service 2")>0,IF(I15="Service 2","B",IF(COUNTIF(I:I,"Service 1")*COUNTIF(I:I,"Service 2")*COUNTIF(I:I,"Service 3")>0,IF(I15="Service 3","C",IF(COUNTIF(I:I,"Service 1")*COUNTIF(I:I,"Service 2")*COUNTIF(I:I,"Service 3")*COUNTIF(I:I,"Service 4")>0,IF(I15="Service 4","D",
IF(COUNTIF(I:I,"Service 1")*COUNTIF(I:I,"Service 4")>0,IF(I15="Service 4","B",
IF(COUNTIF(I:I,"Service 2")*COUNTIF(I:I,"Service 4")>0,IF(I15="Service 2","A",IF(I15="Service 4","B",
IF(COUNTIF(I:I,"Service 3")*COUNTIF(I:I,"Service 4")>0,IF(I15="Service 3","A",IF(I15="Service 4","B",
IF(COUNTIF(I:I,"Service 4")>0,IF(I15="Service 4","A"))))))))))))))))))

 

[dreid1011]

Can you provide an example of this column your services may be in, and the expected results please? You can use the XL2BB add-in, link in my signature, or post some sample data as a table.

And update your profile to show which version(s) of Excel you are using.