United States
Member #105,307
January 29, 2011
474 Posts
Offline
Quote: Originally posted by frenchie on Mar 20, 2011
Hi everyone,
I need CF formula that will highlight 2,3,4 or 5 consecutive numbers from 5 numbers combination.
A1:E1 : 5,23,24,36,54. here 23 and 24 should be highlighted in green.
A1:E1 : 5,23,24,25,54. here 23,24 and 25 should be highlighted in red.
A1:E1 : 22,23,24,25,54. here 22,23,24 and 25 should be highlighted bleu.
A1:E1 : 22,23,24,25,26. here 22,23,24,25 and 26 should be highlighted black.
I put the example for each one, for the different colour.
Thank you in advance for any help... Serge.
I can't suggest a formula but there's a fast way of doing it using search and replace. Highlight A1 through E1, click the menu for search / replace, choose 'Options', Format or choose options from format or cell. Then provide the color choice for each number you want changed to a particular color.
United States
Member #105,307
January 29, 2011
474 Posts
Offline
If you're trying to identify when a particular number or combination of numbers happened you might find it easier to use countifs, say, by spreading each number within the history/matrix across the top horizontally to the right of the history beginning with H
Insert =COUNTIFS($A2:$E2,H$1) in H2, then copy and paste the formula all the way across the matrix, then copy and paste those downward until you run out of history.
That will allow you to filter and sort for each number or each combination.
Los Angeles United States
Member #75,408
June 2, 2009
590 Posts
Offline
Quote: Originally posted by JFN1 on Apr 2, 2011
if you need something like this let me know
16
22
23
26
35
31
-
22
23
-
-
14
33
48
49
50
18
-
-
48
49
50
5
8
31
46
50
4
-
-
-
-
-
5
9
21
35
38
20
-
-
-
-
-
4
27
29
38
45
5
-
-
-
-
-
2
15
17
33
35
8
-
-
-
-
-
1
4
11
16
45
37
-
-
-
-
-
4
22
42
46
53
20
-
-
-
-
-
4
8
15
25
47
42
-
-
-
-
-
10
12
13
35
56
9
-
12
13
-
-
6
18
36
40
49
7
-
-
-
-
-
15
16
27
40
52
16
15
16
-
-
-
8
11
12
31
32
29
-
11
12
31
32
11
20
26
46
53
12
-
-
-
-
-
18
22
25
31
38
29
-
-
-
-
-
23
27
33
44
46
36
-
-
-
-
-
4
38
45
53
54
9
-
-
-
53
54
6
11
12
18
56
12
-
11
12
-
-
11
16
19
47
53
2
-
-
-
-
-
5
9
34
43
47
8
-
-
-
-
-
2
6
12
34
35
33
-
-
-
34
35
7
14
31
51
54
35
-
-
-
-
-
Code:
on cell h5 formula =IF(B5=A5+1,A5,"-")
on cell i5 formula =IF(C5=B5+1,B5,IF(B5=A5+1,B5,"-"))
on cell j5 formula =IF(D5=C5+1,C5,IF(C5=B5+1,C5,"-"))
on cell k5 formula =IF(E5=D5+1,D5,IF(D5=C5+1,D5,"-"))
on cell l5 formula =IF(E5=D5+1,E5,"-")
Apply your Conditional formatting colors to this formulas
Hi JFN1,
And thank you for your time, but I would like to have the CF directly on the numbers themself without extra or outside formula to make it happen.
I have already the following from someone else, it's pretty good, but still not exactly what I need, very close though, because when I have 2 pairs it should be in green but with this CF I get them in red, or 1 triple and 1 pair I should have the triple in purple and the 1 pair in green but here it's all in yellow !!!
I need the CF within the draw numbers with a border all around the consecutive numbers, it will need kind few formulas in this CF for each color and border.
For the following your data should be from B:F.
Formula in X1
=SUM(IF(B1:F1=C1:G1-1,1,IF(B1:F1=A1:E1+1,IF(A1:E1<>"",1))))
confirmed with Ctrl+Shift+Enter (not just Enter)
copy down till the end of your data
Select your entire data-range (B1:G4), CF New Rule
CF formula
=OR(AND(B1=A1+1,A1<>"",$X1=2),AND(B1=C1-1,$X1=2))
pick Green
CF New Rule
=OR(AND(B1=A1+1,A1<>"",$X1=3),AND(B1=C1-1,$X1=3))
pick Purple
CF New Rule
=OR(AND(B1=A1+1,A1<>"",$X1=4),AND(B1=C1-1,$X1=4))
pick Red
CF New Rule
=OR(AND(B1=A1+1,A1<>"",$X1=5),AND(B1=C1-1,$X1=5))
pick Yellow
1
12
13
14
20
11
12
13
14
50
21
1
12
13
34
11
12
13
14
15
I hope someone can do it otherwise it's OK.
I want to thank guys for trying helping, and I know it's not an easy one but this is how I need it for my display.
Pennsylvania United States
Member #109,270
April 9, 2011
1,628 Posts
Offline
Frenchie..
I signed up here to reply to your post. I think the only way to make this work is VBA. It is Visual Basic for Microsoft Applications. I went to school for VB.. I can write the code, but no guarantee when I will have time to write it...It shouldn't be too involved. You can add it as a macro and just click play to run through your data... I am comfused by your one post. If you have 4 in a row, you want them to be 2 pairs of green or all 4 of them red? Or do you want the cell to be colored green and red as the border around all 4.. If you clear that up, i can see what I can do.
Los Angeles United States
Member #75,408
June 2, 2009
590 Posts
Offline
Hi omiller315,
Thanks for the reply.
I use the draw results from my state, and when a pair came up I want it to be in green with a border around it etc...for 3 or 4 or 5 consecutive numbers, but sometime you have a draw that have two pairs in it, like for example : 5-23-24-45-46, so those 2 pairs 23-24 and 45-46 should be highlighted in green with each pair a border around it, and the red ( or what ever color ) would be, if 4 numbers are consecutive numbers.like 5-24-25-26-27.
But I don't want no macro because I want to use this CF for other data.
Los Angeles United States
Member #75,408
June 2, 2009
590 Posts
Offline
Hi omiller,
Thanks for the help but, it has to be a formula for that CF.
Like I said before , I need it also for different data other than draw number, that would actualy have consecutive number too,( I have so many different data to study) it's just that when you look at the data it look nice and clear it's maybe more formula work, but to me it's worth it.I can see better for analizing them.
Pennsylvania United States
Member #109,270
April 9, 2011
1,628 Posts
Offline
You are welcome. This would work for anything with consecutive numbers, but I can understand you wanting to do it without macros. I am working on something for a guy who runs my golf league. I put a button in a field that goes through all scores, finds the most recent 6(even if they miss weeks), drops high and low and then averages out the remaining 4 for the handicap. Like I said, VBA makes working with Excel data simple. He clicks the button and all the work is done for him..
