Color conditioning in Ecel to pin point patternsPrev TopicNext Topic

• powerplayer

  

  United States
  Member #17,833
  June 28, 2005
  2,087 Posts
  Offline
  May 7, 2016, 8:35 pm

  ▲▼

  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!!!
• phileight

  

  United States
  Member #41,845
  June 23, 2006
  713 Posts
  Offline
  May 8, 2016, 7:54 am

  ▲▼

  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

  

  United States
  Member #17,833
  June 28, 2005
  2,087 Posts
  Offline
  May 8, 2016, 8:23 am

  ▲▼
  In response to phileight

  Quote: Originally posted by phileight on May 8, 2016

  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!!!
• ithastobesaid

  

  United States
  Member #123,194
  February 15, 2012
  54 Posts
  Offline
  May 8, 2016, 11:28 am

  ▲▼

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

  

  United States
  Member #41,845
  June 23, 2006
  713 Posts
  Offline
  May 8, 2016, 12:57 pm

  ▲▼

  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

  

  United States
  Member #17,833
  June 28, 2005
  2,087 Posts
  Offline
  May 8, 2016, 2:39 pm

  ▲▼
  In response to ithastobesaid

  Quote: Originally posted by ithastobesaid on May 8, 2016

  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

  

  United States
  Member #17,833
  June 28, 2005
  2,087 Posts
  Offline
  May 8, 2016, 5:37 pm

  ▲▼
  In response to phileight

  Quote: Originally posted by phileight on May 8, 2016

  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!!!
• phileight

  

  United States
  Member #41,845
  June 23, 2006
  713 Posts
  Offline
  May 9, 2016, 10:01 am

  ▲▼

  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

  

  United States
  Member #17,833
  June 28, 2005
  2,087 Posts
  Offline
  May 9, 2016, 11:08 am

  ▲▼
  In response to phileight

  Quote: Originally posted by phileight on May 9, 2016

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