if anyone can help with these i will be ternally grateful...

I have a column which contains the following


12345
12BF1
12356
14321
45670
05F60
23871



Ok a mixture of letters an numbers.....what I would like to do is
concentrate on the last two digits from the right. so for the first
row the two digits are 45 then the second row is F1 and so on.....

I would like to have some conditional formatting that highlighted the
row if the last two digits were in dscending order and the last digit
is less than 7

for example row 2 would be highlighted as any letter followed by a
number less than 7 would meet the crieteria as would row 4 and row 7.

If not possible to do with letters and number =s is there a way to do
it with numbers only so that the conditional formatting kicks in when
the last two digits are as folows:

03
02
01
11
21
32
41
51
61


etc etc


Any help gratefully appreciated

Thanks

On Wed, 22 Jun 2011 21:21:49 -0400, Ron Rosenfeld <(E-Mail Removed)>
wrote:

>On Thu, 23 Jun 2011 01:00:35 +0100, Sam Harman <(E-Mail Removed)> wrote:
>
>>if anyone can help with these i will be ternally grateful...
>>
>>I have a column which contains the following
>>
>>
>>12345
>>12BF1
>>12356
>>14321
>>45670
>>05F60
>>23871
>>
>>
>>
>>Ok a mixture of letters an numbers.....what I would like to do is
>>concentrate on the last two digits from the right. so for the first
>>row the two digits are 45 then the second row is F1 and so on.....
>>
>>I would like to have some conditional formatting that highlighted the
>>row if the last two digits were in dscending order and the last digit
>>is less than 7
>>
>>for example row 2 would be highlighted as any letter followed by a
>>number less than 7 would meet the crieteria as would row 4 and row 7.
>>
>
>If your data starts in A1, then a formula which will do what you wish will be:
>
>=AND(CODE(RIGHT($A1,2))>CODE(RIGHT($A1,1)),--RIGHT($A1,1)<7)

Thanks Ron for taking the time to respond. I fear that I have not
explained myself properly......


If the following are in cells A1 - A7 they would be conditionally
formatted as follows:

2P474 would be conditionally formatted green fill
46535 would not be formatted
2P475 would be conditionally formatted green fill
46536 would not be formatted
2P476 would not be formatted
46537 would not be formatted
2P477 would not be formatted

only those figures that end in the following belwo would meet the
criteria to be conditionally formatted.

61
51
41
31
21
11
65
64
63
62
54
52
52
43
42
32
F6
F5
F4
F3
F2
F1
P6
P5
P4
P3
P2
P1
U6
U5
U4
U3
U2
U1
B6
B5
B4
B3
B2
B1
O6
O5
O4
O3
O2
O1

I hope that helps and thanks again for your time

Sam

On Thu, 23 Jun 2011 13:26:26 -0400, Ron Rosenfeld <(E-Mail Removed)>
wrote:

>On Thu, 23 Jun 2011 14:57:33 +0100, Sam Harman <(E-Mail Removed)> wrote:
>
>>Thanks Ron for taking the time to respond. I fear that I have not
>>explained myself properly......
>>
>>
>>If the following are in cells A1 - A7 they would be conditionally
>>formatted as follows:
>>
>>2P474 would be conditionally formatted green fill
>>46535 would not be formatted
>>2P475 would be conditionally formatted green fill
>>46536 would not be formatted
>>2P476 would not be formatted
>>46537 would not be formatted
>>2P477 would not be formatted
>
>
>It is not clear what you want. My formula returns true for your initial description, and can be used as a conditionally formatting formula.
>
>But your current description not consistent with your initial requirement statement, and is also not internally consistent.
>
>>I would like to have some conditional formatting that highlighted the
>>row if the last two digits were in dscending order and the last digit
>>is less than 7
>
>2P476: The last two digits are in descending order and the last digit is less than 7.
>
>So why is it not highlighted?
>
>And your list does not clarify things. Neither 74 nor 75 are listed below, yet you have them listed above as being conditionally formatted.
>
>You need to rethink your requirements.
>
>>only those figures that end in the following belwo would meet the
>>criteria to be conditionally formatted.
>>



Thanks again Ron for your patience. It is not that easy to explain or
I am not explainign it that well.....i think the list below would be
the full list of all the possible permutations.....that would return a
conditional format of a green fill

06
05
04
03
02
01
96
95
94
93
92
91
86
85
84
83
82
81
76
75
74
73
72
71
65
64
63
62
61
54
53
52
51
43
42
41
32
31
21
11

F6
F5
F4
F3
F2
F1
B6
B5
B4
B3
B2
B1
U6
U5
U4
U3
U2
U1

The following single digits would also qualify

6
5
4
3
2
1

Thanks again ron......

Cheers

Sam

On Thu, 23 Jun 2011 18:28:49 -0400, Ron Rosenfeld <(E-Mail Removed)>
wrote:

>On Thu, 23 Jun 2011 22:52:46 +0100, Sam Harman <(E-Mail Removed)> wrote:
>
>>Thanks again Ron for your patience. It is not that easy to explain or
>>I am not explainign it that well.....i think the list below would be
>>the full list of all the possible permutations.....that would return a
>>conditional format of a green fill
>>
>...Snipped
>
>What is the rule here? Is 11 a special case? Obviously it the two digits are not in "Descending Order"
>
>>11
>>
>>
>>The following single digits would also qualify
>>
>>6
>>5
>>4
>>3
>>2
>>1
>
>Does that mean you might have "strings" which consist of only a single digit? You did not provide any examples of that.
>
>?????
>
>If there are a number of exceptions to your initially, and clearly stated rule, as you seem to be writing, perhaps the simplest thing for you would be to set up a list of allowable terminations, and use vlookup for your conditional formatting.

Ron, I did say that I was not explainign myself well enough and for
that I apologise as I can see that you are really trying to help me
here...perhaps if I explain the use of the figures it will
help.......I am carrying out some research on the results of horse
races and in particular whether there is any trend in winning
performance which relates to previous performance.

The information as shown (and the one that ended in 76 was an over
sight as that would count) in the last two digits (or1 digit if the
horse has only run once) would equate to its last two/one runs......

Horses that have progressive form I would like to identify quickly as
I have over 14,000 records to analyse and therefore being able to
conditionally format the cell when I enter the data then filter on the
cell colour would be a godsend..........

So any horse that has run one and had a form figure of 6, 5, 4, 3, 2,
or 1 would qualify for conditional formatting and any horse where its
last two runs (shown as the last two figures to the right if more than
two runs) showed progressive form would also qualify as in the long
list I previously provided.....65, 43, 21, etc.

Horses that have their last two form figures as 11 are clearly in good
heart and would qualify as they cannot be any more progressive than
11.

I hope that helps Ron and if you in any way can assist me it will be
very much appreciated.......


Thanks in advance

Sam

On Thu, 23 Jun 2011 21:55:22 -0400, Ron Rosenfeld <(E-Mail Removed)>
wrote:

>On Thu, 23 Jun 2011 23:40:49 +0100, Sam Harman <(E-Mail Removed)> wrote:
>
>>Ron, I did say that I was not explainign myself well enough and for
>>that I apologise as I can see that you are really trying to help me
>>here...perhaps if I explain the use of the figures it will
>>help.......I am carrying out some research on the results of horse
>>races and in particular whether there is any trend in winning
>>performance which relates to previous performance.
>>
>>The information as shown (and the one that ended in 76 was an over
>>sight as that would count) in the last two digits (or1 digit if the
>>horse has only run once) would equate to its last two/one runs......
>>
>>Horses that have progressive form I would like to identify quickly as
>>I have over 14,000 records to analyse and therefore being able to
>>conditionally format the cell when I enter the data then filter on the
>>cell colour would be a godsend..........
>>
>>So any horse that has run one and had a form figure of 6, 5, 4, 3, 2,
>>or 1 would qualify for conditional formatting and any horse where its
>>last two runs (shown as the last two figures to the right if more than
>>two runs) showed progressive form would also qualify as in the long
>>list I previously provided.....65, 43, 21, etc.
>>
>>Horses that have their last two form figures as 11 are clearly in good
>>heart and would qualify as they cannot be any more progressive than
>>11.
>>
>>I hope that helps Ron and if you in any way can assist me it will be
>>very much appreciated.......
>
>But what does the cell entry look like if the horse has run only once?

Hi Ron, the following cells relate to the form cells for an 8 runner
race.


1
122
21
41
832
4457
35
49

All cells are formatted as General as they can contain numbers and
letters i.e 3F213 or 3U232 (U is where the horse unseated the rider, F
when the horse fell in the race, B means the horse was brought down by
another horse.

Thanks again

Sam

On Fri, 24 Jun 2011 06:20:08 -0400, Ron Rosenfeld <(E-Mail Removed)>
wrote:

>On Fri, 24 Jun 2011 08:34:03 +0100, Sam Harman <(E-Mail Removed)> wrote:
>
>>Hi Ron, the following cells relate to the form cells for an 8 runner
>>race.
>>
>>
>>1
>>122
>>21
>>41
>>832
>>4457
>>35
>>49
>>
>>All cells are formatted as General as they can contain numbers and
>>letters i.e 3F213 or 3U232 (U is where the horse unseated the rider, F
>>when the horse fell in the race, B means the horse was brought down by
>>another horse.
>>
>>Thanks again
>>
>>Sam
>
>Well, if you've described the variations, the following formula should cover it:
>
>=OR(AND($A1>=1,$A1<=6),
>--RIGHT($A1,2)=11,
>AND(CODE(RIGHT($A1,2))>CODE(RIGHT($A1,1)),--RIGHT($A1,1)<7))
>
>Use that in the conditional formatting dialog, changing $A1 to the appropriate first cell of your column of form cells, using the same form of mixed addressing.


Ron, thanks very much it is almost perfect but I have the following
problems.............


If a horse has 2 runs or more and its form figures end in 06, 05, 04,
03, 02, 01 it does not highlight the cell

It also doesnt highlight if the form is

F6
F5
F4
F3
F2
F1
B6
B5
B4
B3
B2
B1
U6
U5
U4
U3
U2
U1

......but wrongly highlights the horses with the following form figures

10
20
30
40
50
60


Thanks for any further help on this.....at the very least i am much
further forward than I was

Cheers

Sam

On Fri, 24 Jun 2011 13:47:54 -0400, Ron Rosenfeld <(E-Mail Removed)>
wrote:

>On Fri, 24 Jun 2011 14:59:35 +0100, Sam Harman <(E-Mail Removed)> wrote:
>
>>Ron, thanks very much it is almost perfect but I have the following
>>problems.............
>>
>>
>>If a horse has 2 runs or more and its form figures end in 06, 05, 04,
>>03, 02, 01 it does not highlight the cell
>
>Those two digits are NOT in "descending order" as you specified previously was a requirement.
>
>>
>>It also doesnt highlight if the form is
>>
>>F6
>>F5
>>F4
>>F3
>>F2
>>F1
>
>That is due to an error in my formula. If you change it to:
>
>=OR(AND($A1>=1,$A1<=6),
>RIGHT($A1,2)="11",
>AND(CODE(RIGHT($A1,2))>CODE(RIGHT($A1,1)),--RIGHT($A1,1)<7))
>
>it will correctly highlight those endings.
>
>>
>>.....but wrongly highlights the horses with the following form figures
>>
>>10
>>20
>>30
>>40
>>50
>>60
>
>Those two digits ARE in descending order and the last digit is less than 7, meeting your specifications.
>
>Again, you still need to fully state your specifications in a clear and unambiguous way.


Ron, you have the patience of a saint

Yes they are in descending order, what I didn't make clear (again) was
that 0 in horse race terms is a horse that ran 10th or more in the
race, hence the 0.

Is there any way that can be incorporated into the formula so that 01,
02, 03, 04, 05 and 06 are counted as descending and 10, 20, 30, 40,
50, 60 are counted as ascending?

Thanks again

Sam

On Fri, 24 Jun 2011 16:21:29 -0400, Ron Rosenfeld <(E-Mail Removed)>
wrote:

>On Fri, 24 Jun 2011 21:10:26 +0100, Sam Harman <(E-Mail Removed)> wrote:
>
>>Ron, you have the patience of a saint
>>
>>Yes they are in descending order, what I didn't make clear (again) was
>>that 0 in horse race terms is a horse that ran 10th or more in the
>>race, hence the 0.
>>
>>Is there any way that can be incorporated into the formula so that 01,
>>02, 03, 04, 05 and 06 are counted as descending and 10, 20, 30, 40,
>>50, 60 are counted as ascending?
>>
>>Thanks again
>>
>>Sam
>
>Probably.
>
>Answer this: Can a form cell contain ONLY a single digit, or will there ALWAYS be at least two characters in the cell?

Ron, a form cell can contain a single digit such as 1, 2, 3, 4, 5, 6,
7, 8, 9, 0, or indeed a single letter such as F (Fell), B (Brought
Down), U (Unseated Rider)

Cheers

Sam

On Fri, 24 Jun 2011 16:37:21 -0400, Ron Rosenfeld <(E-Mail Removed)>
wrote:

>On Fri, 24 Jun 2011 21:10:26 +0100, Sam Harman <(E-Mail Removed)> wrote:
>
>>On Fri, 24 Jun 2011 13:47:54 -0400, Ron Rosenfeld <(E-Mail Removed)>
>>wrote:
>>
>>>On Fri, 24 Jun 2011 14:59:35 +0100, Sam Harman <(E-Mail Removed)> wrote:
>>>
>>>>Ron, thanks very much it is almost perfect but I have the following
>>>>problems.............
>>>>
>>>>
>>>>If a horse has 2 runs or more and its form figures end in 06, 05, 04,
>>>>03, 02, 01 it does not highlight the cell
>>>
>>>Those two digits are NOT in "descending order" as you specified previously was a requirement.
>>>
>>>>
>>>>It also doesnt highlight if the form is
>>>>
>>>>F6
>>>>F5
>>>>F4
>>>>F3
>>>>F2
>>>>F1
>>>
>>>That is due to an error in my formula. If you change it to:
>>>
>>>=OR(AND($A1>=1,$A1<=6),
>>>RIGHT($A1,2)="11",
>>>AND(CODE(RIGHT($A1,2))>CODE(RIGHT($A1,1)),--RIGHT($A1,1)<7))
>>>
>>>it will correctly highlight those endings.
>>>
>>>>
>>>>.....but wrongly highlights the horses with the following form figures
>>>>
>>>>10
>>>>20
>>>>30
>>>>40
>>>>50
>>>>60
>>>
>>>Those two digits ARE in descending order and the last digit is less than 7, meeting your specifications.
>>>
>>>Again, you still need to fully state your specifications in a clear and unambiguous way.
>>
>>
>>Ron, you have the patience of a saint
>>
>>Yes they are in descending order, what I didn't make clear (again) was
>>that 0 in horse race terms is a horse that ran 10th or more in the
>>race, hence the 0.
>>
>>Is there any way that can be incorporated into the formula so that 01,
>>02, 03, 04, 05 and 06 are counted as descending and 10, 20, 30, 40,
>>50, 60 are counted as ascending?
>>
>>Thanks again
>>
>>Sam
>
>Try this:
>
>=OR(AND($A1>=1,$A1<=6),
>RIGHT($A1,2)="11",
>AND(CODE(RIGHT($A1,2))=48,RIGHT($A1,1)<"7",RIGHT($A1,1)>"0"),
>AND(CODE(RIGHT($A1,2))>CODE(RIGHT($A1,1)),RIGHT($A1,1)<"7",RIGHT($A1,1)>"0"))

Ron, I think you may have nailed it..i haven't tested it fully as it
is very late but will feedback to you if there any problems althought
at first glance it looks great - thank you so much.....

Can I be cheeky and ask you another quick question?

Say for example I have 40 races all going off at different times and
therefore in colum B I have the race times formatted as time as
follows:

Date Time Form

24-Jun 18:40:00 2
24-Jun 18:40:00 2
24-Jun 18:40:00 6
24-Jun 18:40:00 632
24-Jun 18:40:00 none
24-Jun 18:40:00 none
24-Jun 18:40:00 30
24-Jun 19:15:00 41542
24-Jun 19:15:00 78161
24-Jun 19:15:00 36888
24-Jun 19:15:00 23343
24-Jun 19:15:00 29324
24-Jun 19:15:00 53282
24-Jun 19:15:00 31357
24-Jun 19:15:00 71608
24-Jun 19:15:00 835
24-Jun 19:15:00 52447
24-Jun 19:15:00 48145
24-Jun 19:15:00 878
24-Jun 19:15:00 906
24-Jun 19:15:00 82400
24-Jun 19:45:00 94211
24-Jun 19:45:00 11001
24-Jun 19:45:00 17131
24-Jun 19:45:00 62713
24-Jun 19:45:00 15075
24-Jun 19:45:00 16323
24-Jun 19:45:00 37433
24-Jun 20:15:00 600
24-Jun 20:15:00 49237
24-Jun 20:15:00 39218
24-Jun 20:15:00 20084
24-Jun 20:15:00 8008
24-Jun 20:15:00 4208
24-Jun 20:15:00 23567
24-Jun 20:15:00 50004
24-Jun 20:15:00 80008
24-Jun 20:15:00 44432
24-Jun 20:50:00 12
24-Jun 20:50:00 9621
24-Jun 20:50:00 53472
24-Jun 20:50:00 43208
24-Jun 20:50:00 66312
24-Jun 20:50:00 33148
24-Jun 20:50:00 62245
24-Jun 20:50:00 953
24-Jun 21:20:00 22406
24-Jun 21:20:00 92444
24-Jun 21:20:00 90857
24-Jun 21:20:00 78268
24-Jun 21:20:00 60573
24-Jun 21:20:00 74109
24-Jun 21:20:00 14470
24-Jun 21:20:00 0U941
24-Jun 21:20:00 09P60
24-Jun 21:20:00 12402
24-Jun 21:20:00 660P8
24-Jun 21:20:00 51462
24-Jun 21:20:00 96415
24-Jun 21:20:00 1206
24-Jun 21:20:00 44458
24-Jun 21:20:00 76825
24-Jun 21:20:00 29887


Now suppose I filter the form column to only show those that have
progressive form. i.e those cells filled yellow as per the conditional
formatting.......and my list then becomes like below

24-Jun 19:15:00 53282
24-Jun 19:45:00 94211
24-Jun 19:45:00 11001
24-Jun 19:45:00 17131
24-Jun 19:45:00 62713
24-Jun 19:45:00 15075
24-Jun 19:45:00 16323
24-Jun 19:45:00 37433
24-Jun 20:15:00 20084
24-Jun 20:50:00 12
24-Jun 20:50:00 9621
24-Jun 20:50:00 53472
24-Jun 20:50:00 43208
24-Jun 20:50:00 66312
24-Jun 20:50:00 33148
24-Jun 20:50:00 62245
24-Jun 20:50:00 953
24-Jun 21:20:00 22406
24-Jun 21:20:00 92444
24-Jun 21:20:00 51462
24-Jun 21:20:00 96415
24-Jun 21:20:00 1206
24-Jun 21:20:00 44458
24-Jun 21:20:00 76825
24-Jun 21:20:00 29887

Imagine if you can 200 rows in the time column where some have one
time entry and others have more than one......

Is there any way I can filter the time column to only show those race
times whn only one horse has progressive form

So if I were to filter the time column as per the example above the
19:15 race and the 20:15 race would be the only remaining records.


Thanks very much again Ron


Cheers

Sam

On Fri, 24 Jun 2011 23:14:20 -0400, Ron Rosenfeld <(E-Mail Removed)>
wrote:

>=COUNTIF($B:$B,$B2)


Thanks Ron, sorry but the list was illustrative of the single entry in
the time field and therefore the entries in the form field were not
all yellow.....

I have added the formula in as you suggest but instead of getting a
count of 1, 2,3 etc i get the following

Date Time Count
25-Jun 13:45:00 105
25-Jun 13:45:00 105
25-Jun 13:45:00 105
25-Jun 14:00:00 284
25-Jun 14:00:00 284
25-Jun 14:05:00 97
25-Jun 14:05:00 97
25-Jun 14:05:00 97


Isa this because it is counting the time field which is formatted in
hh:mm:ss ?

Thanks again Ron

Sam