Welcome Guest
Log In | Register )
You last visited December 7, 2016, 8:59 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
PrintE-mailLink
frenchie's avatar - Lottery-041.jpg
Los Angeles
United States
Member #75410
June 2, 2009
479 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.

    Avatar

    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.

      Avatar

      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.

        Avatar

        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:

        http://www.lotterypost.com/thread/228252/1970360

        You might find it helpful.

          frenchie's avatar - Lottery-041.jpg
          Los Angeles
          United States
          Member #75410
          June 2, 2009
          479 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.

            JFN1's avatar - Batman
            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

             

            162223263531-2223--
            143348495018--484950
            583146504-----
            5921353820-----
            4272938455-----
            2151733358-----
            1411164537-----
            42242465320-----
            4815254742-----
            10121335569-1213--
            6183640497-----
            1516274052161516---
            81112313229-11123132
            112026465312-----
            182225313829-----
            232733444636-----
            4384553549---5354
            61112185612-1112--
            11161947532-----
            593443478-----
            2612343533---3435
            71431515435-----

            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

              frenchie's avatar - Lottery-041.jpg
              Los Angeles
              United States
              Member #75410
              June 2, 2009
              479 Posts
              Offline
              Posted: April 3, 2011, 2:15 am - IP Logged

              if you need something like this let me know

               

              162223263531-2223--
              143348495018--484950
              583146504-----
              5921353820-----
              4272938455-----
              2151733358-----
              1411164537-----
              42242465320-----
              4815254742-----
              10121335569-1213--
              6183640497-----
              1516274052161516---
              81112313229-11123132
              112026465312-----
              182225313829-----
              232733444636-----
              4384553549---5354
              61112185612-1112--
              11161947532-----
              593443478-----
              2612343533---3435
              71431515435-----

              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

              112131420
              1112131450
              211121334
              1112131415

              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.

                Avatar
                Pennsylvania
                United States
                Member #109275
                April 9, 2011
                1495 Posts
                Offline
                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.

                  frenchie's avatar - Lottery-041.jpg
                  Los Angeles
                  United States
                  Member #75410
                  June 2, 2009
                  479 Posts
                  Offline
                  Posted: April 10, 2011, 2:20 am - IP Logged

                  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.

                  That's all.

                  Thank you.

                    Avatar
                    Pennsylvania
                    United States
                    Member #109275
                    April 9, 2011
                    1495 Posts
                    Offline
                    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?

                      frenchie's avatar - Lottery-041.jpg
                      Los Angeles
                      United States
                      Member #75410
                      June 2, 2009
                      479 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.

                        Avatar
                        Pennsylvania
                        United States
                        Member #109275
                        April 9, 2011
                        1495 Posts
                        Offline
                        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..