Excel formula help for repeat , +1 , +2 and -1 , -2.Prev TopicNext Topic

• frenchie

  

  Los Angeles
  United States
  Member #75,408
  June 2, 2009
  590 Posts
  Offline
  Jul 7, 2010, 5:44 pm

  ▲▼

  Hi,
  
  I'm looking for someone to help me with thoses formulas, thank you ?
  
  My data are for 5/56 and from C1 to G1 going down.
  
  1) I need formulas that will return the repeat numbers from only the previous combination draw but NOT FROM THE POSITION OF THE NUMBER ,I need to have 1 formula per number repeated.
  
  Ex : The first formula would look for the 1st repeated number in the previous draw.
  
         The second formula would look for the 2nd repeated number in the previous draw.
  
         Etc...
  
   Ex :   if the draws are :                                         
  
                                            previous :         5 - 34 - 38 - 51 - 56
  
                                                   New :         4 - 21 - 34 - 53 - 56
  
   
  
  The first formula would return the number :            34
  
  The second formula would return the number :       56
  
  The third formula would return the number :             0
  
  The fourth formula would return the number :           0
  
  The fifth formula would return the number :               0
  
  
  2 ) Same thing pretty much but thoses formulas would look for the number ( +1 ).
  
   Ex :   if the draws are :                                         
  
                                            previous :         2 - 15 - 27 - 53 - 55
  
                                                   New :        16 - 21 - 40 - 54 - 56
  
  
  The first formula would return the number :            16
  
  The second formula would return the number :       54
  
  The third formula would return the number :           56

  The fourth formula would return the number :           0

  The fifth formula would return the number :               0
  
  

   

   

  3 ) Same thing but thoses formulas would look for the number ( +2 ).
  
  4 ) Thoses formulas would look for the number ( -1 ).
  
  5 ) And to finish thoses formulas would look for the number ( -2 ).

   

           Thank you in advance for your help                               Serge.
• frenchie

  

  Los Angeles
  United States
  Member #75,408
  June 2, 2009
  590 Posts
  Offline
  Jul 10, 2010, 2:43 pm

  ▲▼

  No one can't help I'm surprise !!! with all the GURUS in Excel on this LP !!!, it would be hightly appreciated, we all have the same goal here " to find a way to hit the right numbers.

                                                                                                                                 Thanks.
• Raven62

  

  25

  New Jersey
  United States
  Member #17,842
  June 28, 2005
  180,983 Posts
  Offline
  Jul 10, 2010, 6:36 pm

  ▲▼
  In response to frenchie

  Quote: Originally posted by frenchie on Jul 7, 2010

  Hi,
  
  I'm looking for someone to help me with thoses formulas, thank you ?
  
  My data are for 5/56 and from C1 to G1 going down.
  
  1) I need formulas that will return the repeat numbers from only the previous combination draw but NOT FROM THE POSITION OF THE NUMBER ,I need to have 1 formula per number repeated.
  
  Ex : The first formula would look for the 1st repeated number in the previous draw.
  
         The second formula would look for the 2nd repeated number in the previous draw.
  
         Etc...
  
   Ex :   if the draws are :                                         
  
                                            previous :         5 - 34 - 38 - 51 - 56
  
                                                   New :         4 - 21 - 34 - 53 - 56
  
   
  
  The first formula would return the number :            34
  
  The second formula would return the number :       56
  
  The third formula would return the number :             0
  
  The fourth formula would return the number :           0
  
  The fifth formula would return the number :               0
  
  
  2 ) Same thing pretty much but thoses formulas would look for the number ( +1 ).
  
   Ex :   if the draws are :                                         
  
                                            previous :         2 - 15 - 27 - 53 - 55
  
                                                   New :        16 - 21 - 40 - 54 - 56
  
  
  The first formula would return the number :            16
  
  The second formula would return the number :       54
  
  The third formula would return the number :           56

  The fourth formula would return the number :           0

  The fifth formula would return the number :               0
  
  

   

   

  3 ) Same thing but thoses formulas would look for the number ( +2 ).
  
  4 ) Thoses formulas would look for the number ( -1 ).
  
  5 ) And to finish thoses formulas would look for the number ( -2 ).

   

           Thank you in advance for your help                               Serge.

  I2 thru M2 =(IF(COUNTIF($C1:$G1,C2)=1,C2," ")
  
  O2 thru S2 =(IF(COUNTIF($C1:$G1,C2-1)=1,C2," ")

  A mind once stretched by a new idea never returns to its original dimensions!
  
  Catch-22: A dilemma or difficult circumstance from which there is no escape because of mutually conflicting or dependent conditions.
  
  Corruptissima re publica plurimae leges: When the republic is at its most corrupt the laws are most numerous.
  
  The best way to learn is to never stop being an Experiential Student!
• frenchie

  

  Los Angeles
  United States
  Member #75,408
  June 2, 2009
  590 Posts
  Offline
  Jul 12, 2010, 5:16 am

  ▲▼

  Raven62,

                 Thank you very much those work perfect.

                                                                                                      Serge.
• frenchie

  

  Los Angeles
  United States
  Member #75,408
  June 2, 2009
  590 Posts
  Offline
  Jul 13, 2010, 5:52 am

  ▲▼

  Hello Raven62,

   

              I should have had asked on the first post but I forgot.

  What would be the formulas to do the same thing but in the conditioal formatting ? to highlight the numbers in the new draw when they match the criteria ?

   

                                                                                                                 Thank you Raven62