Match query

[Steve]

Hi does anyone know how I could do the following:

Colum J has the following values

Class 7
Class 6
Class 6
Class 6
Class 6
Class 6
Class 6
Class 6
Class 6
Class 6
Class 6
Class 6
Class 5
Class 5
Class 5
Class 5
Class 5
Class 4
Class 4
Class 4
Class 4
Class 4
Class 4
Class 4
Class 4
Class 5

Column HI has the following values

Class 6-Class 6
Class 6-Class 6-Class 5
Class 6-Class 6
Class 6
Class 6

Class 6-Class 6

Class 6-Class 5-Class 4
Class 6-Class 4-Class 6
Class 6

Class 4-Class 4-Class 3
Class 5-Class 4-Class 4-Class 4-Class 4-Class 5-Class 4-Class 4-Class
Class 1-Class 2-Class 1-Class 1-Class 1
Class 4-Class 4-Class 4-Class 4-Class 4-Class 4-Class 4-Class
Class 4-Class 4-Class 5-Class 5-Class 5-Class 5-Class 5-Class
Class 5-Class 4-Class 4-Class 4-Class 4-Class 4-Class 4-Class
Class 4-Class 5-Class 4-Class 6-Class 6-Class 6
Class 5-Class 2-Class 4-Class 4-Class 2-Class 2-Class 3-Class 4-Class
Class 4-Class 1-Class 1-Class 1-Class 1-Class 1-Class 1-Class 1
Class 5-Class 1-Class 4-Class 4-Class 4-Class 4-Class 6-Class 6
Class 5-Class 5-Class 5
Class 3-Class 3-Class 4-Class 4-Class 4-Class 4-Class 4-Class
Class 4-Class 4-Class 3-Class 4-Class 4-Class 4-Class 3-Class
Class 5-Class 5-Class 5-Class 4-Class 4-Class 4

What I would like a formula to do is to look at the entry in Column J
and then the first entry in column HI and do the following and output
to HW:

if it matches then column HW would show 0

if J is less than HI (for example J would be Class 6 and HI would be
Class 7 then column HW would be 1

if J is greater than HI (for example J would be Class 7 and HI would
be Class 6, Class 5, Class 4, Class 3, Class 2 or Class 1 then column
HI would show -1, -2, -3, -4, -5 or -6 depending on what class HI was.

if there is no entry in HI or no match (sometimes happens) then HW
would have no entry.

Is this at all possible

Thanks


Steve

 

[Claus Busch]

Hi Steve,

Am Tue, 29 Jan 2013 18:05:06 +0000 schrieb Steve:

What I would like a formula to do is to look at the entry in Column J
and then the first entry in column HI and do the following and output
to HW:

if it matches then column HW would show 0

if J is less than HI (for example J would be Class 6 and HI would be
Class 7 then column HW would be 1

if J is greater than HI (for example J would be Class 7 and HI would
be Class 6, Class 5, Class 4, Class 3, Class 2 or Class 1 then column
HI would show -1, -2, -3, -4, -5 or -6 depending on what class HI was.

try in HW1:
=IFERROR(IF(SIGN(RIGHT(J1,1)-MID(HI1,7,1))="-1",RIGHT(J1,1)-MID(HI1,7,1),MIN(1,RIGHT(J1,1)-MID(HI1,7,1))),"")


Regards
Claus Busch