Welcome Guest
Log In | Register )
You last visited December 6, 2016, 7:06 am
All times shown are
Eastern Time (GMT-5:00)

Color conditioning in Ecel to pin point patterns

Topic closed. 8 replies. Last post 7 months ago by powerplayer.

Page 1 of 1
51
PrintE-mailLink
powerplayer's avatar - Lottery-022.jpg

United States
Member #17834
June 28, 2005
2083 Posts
Offline
Posted: May 7, 2016, 8:35 pm - IP Logged

I need some help trying to do something I'm not sure if I even can in excel using color conditioning or code maybe.

 

I want to do the following with the example below:

 

290

803 791

874 175

902 784

 

I want to be able to take the number #4 that is the 3rd digit in the 784 and the number #4 that is the 3rd digit in the 874 and highlight only those 4's. I want the code to highlight this anytime that 2 of the same digit are in the same position and also when they stack on top of each other.

Example like the two 8's from the 874 and 803 which has the # 8 in the 1st position in both numbers.

 

Thank you in advance if anyone can figure this out..

PP

Good luck to everyone!!!

    Avatar

    United States
    Member #41846
    June 23, 2006
    459 Posts
    Offline
    Posted: May 8, 2016, 7:54 am - IP Logged

    the short answer is yes you can.  now for the but, it will require a lot of work on your part

    so questions. what version of excel are you using?

    is you data in separate cells ?  ex "a1" 290  or "a1" 2 "a2" 9 "a3" 0 

    is your data in a single column?

    is your data always in the same place?

    there's more but this is a start if you want to proceed

      powerplayer's avatar - Lottery-022.jpg

      United States
      Member #17834
      June 28, 2005
      2083 Posts
      Offline
      Posted: May 8, 2016, 8:23 am - IP Logged

      the short answer is yes you can.  now for the but, it will require a lot of work on your part

      so questions. what version of excel are you using?

      is you data in separate cells ?  ex "a1" 290  or "a1" 2 "a2" 9 "a3" 0 

      is your data in a single column?

      is your data always in the same place?

      there's more but this is a start if you want to proceed

      Thx phileright for answering my post.

       

      I am using excel 2016 360 ver.

       

      The way I have the data set up is the exact way it's displayed on lottery post when you go to the full list of states and then click into a single state you have a table with play 3 and play 4 displayed with date . This how my table is set up. I took out the dashex. So it's displayed 290 in cell  B8 column 2

       

      Yes data is always in same column/row.

       

      PP

      Good luck to everyone!!!

        Avatar

        United States
        Member #123200
        February 15, 2012
        52 Posts
        Offline
        Posted: May 8, 2016, 11:28 am - IP Logged

        https://www.dropbox.com/s/sugdeh824ubulh6/ColorMe.xlsm?dl=0

         

        The sheet contains one macro, so you will have "enable macros" when you open it.

        The static data in column N are the numbers 0 - 9 with unique background colors.

        The data in column O is a count of how many times a number appears in the range entered in Cell A2.

        The range entered in Cell A2 should be of the single digits in columns D through J you want ot color.

        Click the button to execute the macro which will color the cells in the range entered in Cell A2 to the background colors of the matching digits in column N.

        The cells in columns D through J breakout each digit from the data you already have in columns B and C.

        Change the range in Cell A2 to color more or less cells.  Change the background color of the numbers in column N to whatever color you want and then click the button.

          Avatar

          United States
          Member #41846
          June 23, 2006
          459 Posts
          Offline
          Posted: May 8, 2016, 12:57 pm - IP Logged

          Neat program. I have learned something today.   I think you missed part of the request.

          I want to be able to take the number #4 that is the 3rd digit in the 784 and the number #4 that is the 3rd digit in the 874 and highlight only those 4's. I want the code to highlight this anytime that 2 of the same digit are in the same position and also when they stack on top of each other.

           

          as I understood his request, only highlight numbers that are the same and in same digit position.  I had thought to do something along the same lines, so I will back out.

            powerplayer's avatar - Lottery-022.jpg

            United States
            Member #17834
            June 28, 2005
            2083 Posts
            Offline
            Posted: May 8, 2016, 2:39 pm - IP Logged

            https://www.dropbox.com/s/sugdeh824ubulh6/ColorMe.xlsm?dl=0

             

            The sheet contains one macro, so you will have "enable macros" when you open it.

            The static data in column N are the numbers 0 - 9 with unique background colors.

            The data in column O is a count of how many times a number appears in the range entered in Cell A2.

            The range entered in Cell A2 should be of the single digits in columns D through J you want ot color.

            Click the button to execute the macro which will color the cells in the range entered in Cell A2 to the background colors of the matching digits in column N.

            The cells in columns D through J breakout each digit from the data you already have in columns B and C.

            Change the range in Cell A2 to color more or less cells.  Change the background color of the numbers in column N to whatever color you want and then click the button.

            Thank you ithastobesaid

            I have to take my mother out since it's mother's day but, will try this out when I get back.

            WOW thank you again

             

            PP

            Good luck to everyone!!!

              powerplayer's avatar - Lottery-022.jpg

              United States
              Member #17834
              June 28, 2005
              2083 Posts
              Offline
              Posted: May 8, 2016, 5:37 pm - IP Logged

              Neat program. I have learned something today.   I think you missed part of the request.

              I want to be able to take the number #4 that is the 3rd digit in the 784 and the number #4 that is the 3rd digit in the 874 and highlight only those 4's. I want the code to highlight this anytime that 2 of the same digit are in the same position and also when they stack on top of each other.

               

              as I understood his request, only highlight numbers that are the same and in same digit position.  I had thought to do something along the same lines, so I will back out.

              Thank you again phiteight . Don't back out More input from more people is always a blessing. The more the merrier

               

              If you have any other suggestion or anything please post.

               

              Thank you

               

              PP

              Good luck to everyone!!!

                Avatar

                United States
                Member #41846
                June 23, 2006
                459 Posts
                Offline
                Posted: May 9, 2016, 10:01 am - IP Logged

                This version will color numbers only when there are 2 or more of the same number and are in the same digit position.  I gave it a slightly different name to prevent a conflict.

                 

                https://www.dropbox.com/s/9qyismez2r1bvf2/ColorMe_red.xlsm?dl=0

                  powerplayer's avatar - Lottery-022.jpg

                  United States
                  Member #17834
                  June 28, 2005
                  2083 Posts
                  Offline
                  Posted: May 9, 2016, 11:08 am - IP Logged

                  This version will color numbers only when there are 2 or more of the same number and are in the same digit position.  I gave it a slightly different name to prevent a conflict.

                   

                  https://www.dropbox.com/s/9qyismez2r1bvf2/ColorMe_red.xlsm?dl=0

                  awesome phileight ...

                   

                  Thank you

                   

                  PP

                  Good luck to everyone!!!