Welcome Guest
You last visited April 28, 2017, 3:41 pm
All times shown are
Eastern Time (GMT-5:00)

Need help with Conditional Formatting for Consecutive numbers

Topic closed. 11 replies. Last post 6 years ago by omiller315.

 Page 1 of 1
Los Angeles
United States
Member #75410
June 2, 2009
502 Posts
Offline
 Posted: March 20, 2011, 11:56 pm - IP Logged

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.

United States
Member #105312
January 29, 2011
435 Posts
Offline
 Posted: March 27, 2011, 8:27 am - IP Logged

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 #105312
January 29, 2011
435 Posts
Offline
 Posted: March 27, 2011, 8:44 am - IP Logged

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.

United States
Member #105312
January 29, 2011
435 Posts
Offline
 Posted: March 27, 2011, 9:22 am - IP Logged

I was just going over winsumloosesome's past posts and came across this:

Los Angeles
United States
Member #75410
June 2, 2009
502 Posts
Offline
 Posted: April 2, 2011, 6:54 pm - IP Logged

Hi JosephusMinimus,

Sorry for the delay, and thank you for your respond, but what I'm looking for is only to highlight the consecutive

numbers ( per draw) serounded by a border and not each number, for my history draw, and I would also use

this formatting for other data.Your formulas don't work for what I need, but thank you for the suggestion.

Best Regards...

Serge.

New Member

United States
Member #80762
October 4, 2009
6 Posts
Offline
 Posted: April 2, 2011, 10:18 pm - IP Logged

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

Los Angeles
United States
Member #75410
June 2, 2009
502 Posts
Offline
 Posted: April 3, 2011, 2:15 am - IP Logged

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.

Best Regards...  Serge.

Pennsylvania
United States
Member #109275
April 9, 2011
1523 Posts
Online
 Posted: April 9, 2011, 9:15 pm - IP Logged

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 #75410
June 2, 2009
502 Posts
Offline
 Posted: April 10, 2011, 2:20 am - IP Logged

Hi omiller315,

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.

That's all.

Thank you.

Pennsylvania
United States
Member #109275
April 9, 2011
1523 Posts
Online
 Posted: April 10, 2011, 1:00 pm - IP Logged

VBA is a powerful tool. What other types of data are you looking into CF for? Also what version of Microsoft Excel are you using?

Los Angeles
United States
Member #75410
June 2, 2009
502 Posts
Offline
 Posted: April 17, 2011, 6:31 pm - IP Logged

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.

Thanks anyway, I think this tread is over now.

Serge.

Pennsylvania
United States
Member #109275
April 9, 2011
1523 Posts
Online
 Posted: April 24, 2011, 6:44 pm - IP Logged

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..

 Page 1 of 1