Welcome Guest
You last visited December 8, 2016, 6:57 am
All times shown are
Eastern Time (GMT-5:00)

# Excel formula help for repeat , +1 , +2 and -1 , -2.

Topic closed. 4 replies. Last post 6 years ago by frenchie.

 Page 1 of 1
Los Angeles
United States
Member #75410
June 2, 2009
479 Posts
Offline
 Posted: July 7, 2010, 5:44 pm - IP Logged

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

Los Angeles
United States
Member #75410
June 2, 2009
479 Posts
Offline
 Posted: July 10, 2010, 2:43 pm - IP Logged

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.

New Jersey
United States
Member #17843
June 28, 2005
49767 Posts
Offline
 Posted: July 10, 2010, 6:36 pm - IP Logged

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

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!

Los Angeles
United States
Member #75410
June 2, 2009
479 Posts
Offline
 Posted: July 12, 2010, 5:16 am - IP Logged

Raven62,

Thank you very much those work perfect.

Serge.

Los Angeles
United States
Member #75410
June 2, 2009
479 Posts
Offline
 Posted: July 13, 2010, 5:52 am - IP Logged

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

 Page 1 of 1