Find repeating numbers in a column

will20fitz

New Member
Joined
Dec 5, 2012
Messages
11
Hi there,

I have a series of planetary degree data for about 50 years.

What i would like to do is to take the first degree data for each planet and then calc the period of days which each planet takes to come back round to the same degree.

For example, Venus starts at 296.17 degrees and then takes 325 days to come back round to 296.86 degrees then 410 days to come back round to 296.39 degrees.

Obviously each planet degree does not conform to an exact decimal degree. How do i take acount of this in any calculations?!

Can anyone help?

Many thanks,

WIll

Enter DateSun DegreeMoon DegreeMercury DegreeVenus Degree
13/2/1974324.7670263229.0303842341.7334639296.1668176
14/2/1974325.7775648241.3656592342.0482524296.1945601
15/2/1974326.7877808253.428917342.1915536296.2620978
16/2/1974327.7976617265.3102858342.1606254296.3685934
17/2/1974328.8071934277.0961467341.9562621296.5131229
18/2/1974329.8163601288.8642333341.5832378296.6946953
19/2/1974330.8251452300.6803714341.0506086296.9122691
20/2/1974331.8335311312.5965815340.3718125297.1647672
21/2/1974332.8414995324.6505167339.5645124297.4510887
22/2/1974333.8490313336.8663429338.6501495297.7701202
23/2/1974334.8561064349.2571807337.6532015298.1207451
24/2/1974335.86270381.829003221336.6001883298.5018526
25/2/1974336.868802214.58543061335.5185062298.9123458
26/2/1974337.874380527.53243142334.4352065299.3511487
27/2/1974338.879418840.68175608333.3758432299.8172128
28/2/1974339.883899354.05196531332.363505300.3095218
1/3/1974340.887807967.66612523331.4181096300.8270948
2/3/1974341.891134581.54572587330.5559969301.3689885
3/3/1974342.893874595.70120258329.7898086301.9342964
4/3/1974343.8960285110.1205374329.1286135302.5221482
5/3/1974344.8976024124.7587778328.5782131303.1317065
6/3/1974345.8986066139.5326373328.1415605303.7621637
7/3/1974346.899055154.3244275327.8192336304.4127386
8/3/1974347.8989639168.9969664327.6099111305.0826731
9/3/1974348.8983497183.4162169327.5108205305.7712299
10/3/1974349.8972284197.4742888327.5181363306.4776906
11/3/1974350.8956138211.1052853327.6273182307.2013552
12/3/1974351.8935169224.2903905327.833389307.9415423
13/3/1974352.8909451237.0534898328.1311553308.69759
14/3/1974353.8879025249.4513682328.5153759309.4688569
15/3/1974354.8843898261.5624787328.9808885310.2547238
16/3/1974355.8804048273.4767492329.5226991311.054594
17/3/1974356.8759429285.2873103330.1360439311.8678945
18/3/1974357.8709976297.0840271330.8164283312.6940754
19/3/1974358.8655605308.9483362331.5596485313.5326102
20/3/1974359.8596219320.9489831332.3617996314.3829944
21/3/19740.8531703289333.1385775333.2192752315.2447453
22/3/19741.846192952345.5512486334.1287595316.1174009
23/3/19742.83867513358.2019764335.087216317.0005194
24/3/19743.83060070911.0882284336.091874317.8936793
25/3/19744.82195229124.19415205337.140214318.7964795
26/3/19745.81271184337.49678012338.2299536319.7085397
27/3/19746.8028615650.97285848339.3590324320.6295007
28/3/19747.79238495764.60443076340.5255984321.5590252
29/3/19748.78126805578.38144904341.7279931322.4967973
30/3/19749.76950052192.30038583342.9647376323.4425221
31/3/197410.75707661106.3588888344.234519324.3959245
1/4/197411.74399577120.5476644345.5361771325.3567474
2/4/197412.7302628134.8417858346.8686925326.3247494
3/4/197413.71588759149.1943221348.2311752327.2997023
4/4/197414.70088432163.5352014349.6228546328.2813881
5/4/197415.68527045177.7770417351.0430706329.2695968
6/4/197416.66906541191.8272507352.4912645330.2641239
7/4/197417.65228929205.603059353.9669715331.2647692
8/4/197418.6349616219.0449129355.4698124332.2713354
9/4/197419.61710028232.1246423356.999487333.2836283
10/4/197420.59872107244.8472869358.5557674334.3014559
11/4/197421.57983711257.24777920.138491725335.3246298
12/4/197422.56045886269.3847261.747558869336.3529654
13/4/197423.54059428281.33330783.382923342337.3862821
14/4/197424.52024897293.17849325.044589759338.4244044
15/4/197425.49942642305.0089786.732606762339.4671616
16/4/197426.47812812316.91173938.447059765340.5143878
17/4/197427.45635351328.96688110.18806222341.5659223
18/4/197428.4340999341.242518311.95574525342.6216086
19/4/197429.41136225353.789776913.75024556343.6812951
20/4/197430.388133036.63846588415.57169154344.7448347
21/4/197431.364402219.7944259417.42018752345.8120853
22/4/197432.3401574833.2396858319.29579556346.8829105
23/4/197433.3153848446.9361400821.19851449347.9571803
24/4/197434.2900693860.8324372623.12825536349.0347722
25/4/197435.2641964174.8725171825.0848129350.115572
26/4/197436.2377526989.0034417427.0678328351.1994743
27/4/197437.21072778103.180359529.07677485352.2863834
28/4/197438.1831151117.367552231.11087281353.3762124
29/4/197439.15491285131.535934633.16909207354.4688833
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
The only thing I can think of is defining a fuzziness factor and compare the data against that factor:

Book001.xlsx
ABCDE
11.23456TRUEValue1.23456
21.23467TRUEFuzziness0.02
31.34567FALSE
41.35678FALSE
51.45678FALSE
61.45789FALSE
71.56789FALSE
81.56891FALSE
91.67891FALSE
101.68901FALSE
111.78901FALSE
121.78012FALSE
131.89012FALSE
141.89123FALSE
151.90123FALSE
161.91234FALSE
171.12345FALSE
181.13456FALSE
191.24567TRUE
201.25678FALSE
Sheet1
Cell Formulas
RangeFormula
B1:B20B1=ABS($E$1-A1)<=$E$2
Cells with Conditional Formatting
CellConditionCell FormatStop If True
B1:B20Cell Valuecontains "true"textNO
 
Upvote 0
Solution
Since Mercury repeats faster than any other planet, I worked with it. Check this -

Book1
ABCDEFGH
1Enter DateSun DegreeMoon DegreeMercury DegreeMercuryRepeatRepeat DateVenus Degree
22/13/74324.7670263229.0303842341.73346393411 296.1668176
32/14/74325.7775648241.3656592342.04825243421 296.1945601
42/15/74326.7877808253.428917342.191553634222/15/74296.2620978
52/16/74327.7976617265.3102858342.160625434232/16/74296.3685934
62/17/74328.8071934277.0961467341.956262134122/17/74296.5131229
72/18/74329.8163601288.8642333341.583237834132/18/74296.6946953
82/19/74330.8251452300.6803714341.050608634142/19/74296.9122691
92/20/74331.8335311312.5965815340.37181253401 297.1647672
102/21/74332.8414995324.6505167339.56451243391 297.4510887
112/22/74333.8490313336.8663429338.65014953381 297.7701202
122/23/74334.8561064349.2571807337.65320153371 298.1207451
132/24/74335.86270381.829003221336.60018833361 298.5018526
142/25/74336.868802214.58543061335.51850623351 298.9123458
152/26/74337.874380527.53243142334.43520653341 299.3511487
162/27/74338.879418840.68175608333.37584323331 299.8172128
172/28/74339.883899354.05196531332.3635053321 300.3095218
183/1/74340.887807967.66612523331.41810963311 300.8270948
193/2/74341.891134581.54572587330.55599693301 301.3689885
203/3/74342.893874595.70120258329.78980863291 301.9342964
213/4/74343.8960285110.1205374329.128613532923/4/74302.5221482
223/5/74344.8976024124.7587778328.57821313281 303.1317065
233/6/74345.8986066139.5326373328.141560532823/6/74303.7621637
243/7/74346.899055154.3244275327.81923363271 304.4127386
253/8/74347.8989639168.9969664327.609911132723/8/74305.0826731
263/9/74348.8983497183.4162169327.510820532733/9/74305.7712299
273/10/74349.8972284197.4742888327.518136332743/10/74306.4776906
283/11/74350.8956138211.1052853327.627318232753/11/74307.2013552
293/12/74351.8935169224.2903905327.83338932763/12/74307.9415423
303/13/74352.8909451237.0534898328.131155332833/13/74308.69759
313/14/74353.8879025249.4513682328.515375932843/14/74309.4688569
323/15/74354.8843898261.5624787328.980888532853/15/74310.2547238
333/16/74355.8804048273.4767492329.522699132933/16/74311.054594
343/17/74356.8759429285.2873103330.136043933023/17/74311.8678945
353/18/74357.8709976297.0840271330.816428333033/18/74312.6940754
363/19/74358.8655605308.9483362331.559648533123/19/74313.5326102
373/20/74359.8596219320.9489831332.361799633223/20/74314.3829944
383/21/740.853170329333.1385775333.219275233323/21/74315.2447453
393/22/741.846192952345.5512486334.128759533423/22/74316.1174009
403/23/742.83867513358.2019764335.08721633523/23/74317.0005194
413/24/743.83060070911.0882284336.09187433623/24/74317.8936793
423/25/744.82195229124.19415205337.14021433723/25/74318.7964795
433/26/745.81271184337.49678012338.229953633823/26/74319.7085397
443/27/746.8028615650.97285848339.359032433923/27/74320.6295007
453/28/747.79238495764.60443076340.525598434023/28/74321.5590252
463/29/748.78126805578.38144904341.727993134153/29/74322.4967973
473/30/749.76950052192.30038583342.964737634243/30/74323.4425221
483/31/7410.75707661106.3588888344.2345193441 324.3959245
494/1/7411.74399577120.5476644345.53617713451 325.3567474
504/2/7412.7302628134.8417858346.86869253461 326.3247494
514/3/7413.71588759149.1943221348.23117523481 327.2997023
524/4/7414.70088432163.5352014349.62285463491 328.2813881
534/5/7415.68527045177.7770417351.04307063511 329.2695968
544/6/7416.66906541191.8272507352.49126453521 330.2641239
554/7/7417.65228929205.603059353.96697153531 331.2647692
564/8/7418.6349616219.0449129355.46981243551 332.2713354
574/9/7419.61710028232.1246423356.9994873561 333.2836283
584/10/7420.59872107244.8472869358.55576743581 334.3014559
594/11/7421.57983711257.24777920.13849172501 335.3246298
604/12/7422.56045886269.3847261.74755886911 336.3529654
614/13/7423.54059428281.33330783.38292334231 337.3862821
624/14/7424.52024897293.17849325.04458975951 338.4244044
634/15/7425.49942642305.0089786.73260676261 339.4671616
644/16/7426.47812812316.91173938.44705976581 340.5143878
654/17/7427.45635351328.96688110.18806222101 341.5659223
664/18/7428.4340999341.242518311.95574525111 342.6216086
674/19/7429.41136225353.789776913.75024556131 343.6812951
684/20/7430.388133036.63846588415.57169154151 344.7448347
694/21/7431.364402219.7944259417.42018752171 345.8120853
704/22/7432.3401574833.2396858319.29579556191 346.8829105
714/23/7433.3153848446.9361400821.19851449211 347.9571803
724/24/7434.2900693860.8324372623.12825536231 349.0347722
734/25/7435.2641964174.8725171825.0848129251 350.115572
744/26/7436.2377526989.0034417427.0678328271 351.1994743
754/27/7437.21072778103.180359529.07677485291 352.2863834
764/28/7438.1831151117.367552231.11087281311 353.3762124
774/29/7439.15491285131.535934633.16909207331 354.4688833
Sheet1
Cell Formulas
RangeFormula
E2:E77E2=TRUNC(D2)
F2:F77F2=COUNTIFS($E$2:E2,E2)
G2:G77G2=IFS(F2>1,LOOKUP(2,1/(D:D=D2)*(F:F=F2-1),A:A),TRUE,"")
 
Upvote 0
Though I didn't get any feedback from your side. Yet, I was worried with the accuracy of results. Try this and revert -

Book1
ABCDEFGHI
1Enter DateSun DegreeMoon DegreeMercury DegreeMercuryRepeatRepeat DateDiffVenus Degree
22/13/74324.7670263229.0303842341.73346393410  296.1668176
32/14/74325.7775648241.3656592342.04825243420  296.1945601
42/15/74326.7877808253.428917342.191553634212/14/741296.2620978
52/16/74327.7976617265.3102858342.160625434222/15/741296.3685934
62/17/74328.8071934277.0961467341.956262134112/13/744296.5131229
72/18/74329.8163601288.8642333341.583237834122/17/741296.6946953
82/19/74330.8251452300.6803714341.050608634132/18/741296.9122691
92/20/74331.8335311312.5965815340.37181253400  297.1647672
102/21/74332.8414995324.6505167339.56451243390  297.4510887
112/22/74333.8490313336.8663429338.65014953380  297.7701202
122/23/74334.8561064349.2571807337.65320153370  298.1207451
132/24/74335.86270381.829003221336.60018833360  298.5018526
142/25/74336.868802214.58543061335.51850623350  298.9123458
152/26/74337.874380527.53243142334.43520653340  299.3511487
162/27/74338.879418840.68175608333.37584323330  299.8172128
172/28/74339.883899354.05196531332.3635053320  300.3095218
183/1/74340.887807967.66612523331.41810963310  300.8270948
193/2/74341.891134581.54572587330.55599693300  301.3689885
203/3/74342.893874595.70120258329.78980863290  301.9342964
213/4/74343.8960285110.1205374329.128613532913/3/741302.5221482
223/5/74344.8976024124.7587778328.57821313280  303.1317065
233/6/74345.8986066139.5326373328.141560532813/5/741303.7621637
243/7/74346.899055154.3244275327.81923363270  304.4127386
253/8/74347.8989639168.9969664327.609911132713/7/741305.0826731
263/9/74348.8983497183.4162169327.510820532723/8/741305.7712299
273/10/74349.8972284197.4742888327.518136332733/9/741306.4776906
283/11/74350.8956138211.1052853327.627318232743/10/741307.2013552
293/12/74351.8935169224.2903905327.83338932753/11/741307.9415423
303/13/74352.8909451237.0534898328.131155332823/6/747308.69759
313/14/74353.8879025249.4513682328.515375932833/13/741309.4688569
323/15/74354.8843898261.5624787328.980888532843/14/741310.2547238
333/16/74355.8804048273.4767492329.522699132923/4/7412311.054594
343/17/74356.8759429285.2873103330.136043933013/2/7415311.8678945
353/18/74357.8709976297.0840271330.816428333023/17/741312.6940754
363/19/74358.8655605308.9483362331.559648533113/1/7418313.5326102
373/20/74359.8596219320.9489831332.361799633212/28/7420314.3829944
383/21/740.853170329333.1385775333.219275233312/27/7422315.2447453
393/22/741.846192952345.5512486334.128759533412/26/7424316.1174009
403/23/742.83867513358.2019764335.08721633512/25/7426317.0005194
413/24/743.83060070911.0882284336.09187433612/24/7428317.8936793
423/25/744.82195229124.19415205337.14021433712/23/7430318.7964795
433/26/745.81271184337.49678012338.229953633812/22/7432319.7085397
443/27/746.8028615650.97285848339.359032433912/21/7434320.6295007
453/28/747.79238495764.60443076340.525598434012/20/7436321.5590252
463/29/748.78126805578.38144904341.727993134142/19/7438322.4967973
473/30/749.76950052192.30038583342.964737634232/16/7442323.4425221
483/31/7410.75707661106.3588888344.2345193440  324.3959245
494/1/7411.74399577120.5476644345.53617713450  325.3567474
504/2/7412.7302628134.8417858346.86869253460  326.3247494
514/3/7413.71588759149.1943221348.23117523480  327.2997023
524/4/7414.70088432163.5352014349.62285463490  328.2813881
534/5/7415.68527045177.7770417351.04307063510  329.2695968
544/6/7416.66906541191.8272507352.49126453520  330.2641239
554/7/7417.65228929205.603059353.96697153530  331.2647692
564/8/7418.6349616219.0449129355.46981243550  332.2713354
574/9/7419.61710028232.1246423356.9994873560  333.2836283
584/10/7420.59872107244.8472869358.55576743580  334.3014559
594/11/7421.57983711257.24777920.13849172500  335.3246298
604/12/7422.56045886269.3847261.74755886910  336.3529654
614/13/7423.54059428281.33330783.38292334230  337.3862821
624/14/7424.52024897293.17849325.04458975950  338.4244044
634/15/7425.49942642305.0089786.73260676260  339.4671616
644/16/7426.47812812316.91173938.44705976580  340.5143878
654/17/7427.45635351328.96688110.18806222100  341.5659223
664/18/7428.4340999341.242518311.95574525110  342.6216086
674/19/7429.41136225353.789776913.75024556130  343.6812951
684/20/7430.388133036.63846588415.57169154150  344.7448347
694/21/7431.364402219.7944259417.42018752170  345.8120853
704/22/7432.3401574833.2396858319.29579556190  346.8829105
714/23/7433.3153848446.9361400821.19851449210  347.9571803
724/24/7434.2900693860.8324372623.12825536230  349.0347722
734/25/7435.2641964174.8725171825.0848129250  350.115572
744/26/7436.2377526989.0034417427.0678328270  351.1994743
754/27/7437.21072778103.180359529.07677485290  352.2863834
764/28/7438.1831151117.367552231.11087281310  353.3762124
774/29/7439.15491285131.535934633.16909207330  354.4688833
Sheet1
Cell Formulas
RangeFormula
E2:E77E2=TRUNC(D2)
F2:F77F2=COUNTIFS(E$2:E2,E2)-1
G2:G77G2=IFS(F2>=1,XLOOKUP(1,(E$2:E2=E2)*(F$2:F2=F2-1),$A$2:$A2),TRUE,"")
H2H2=IFS(G2="","",TRUE,$A2-G2)
H3:H77H3=IFS(G3="","",TRUE,A3-G3)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
H2:H77,F2:F77Expression=$F2<>0textNO


Tried same on Venus too -

Book1
IJKLMN
1Venus DegreeVenusRepeatRepeat DateDiffEnter Date
2296.16681762960  2/13/74
3296.194560129612/13/7412/14/74
4296.262097829622/14/7412/15/74
5296.368593429632/15/7412/16/74
6296.513122929642/16/7412/17/74
7296.694695329652/17/7412/18/74
8296.912269129662/18/7412/19/74
9297.16476722970  2/20/74
10297.451088729712/20/7412/21/74
11297.770120229722/21/7412/22/74
12298.12074512980  2/23/74
13298.501852629812/23/7412/24/74
14298.912345829822/24/7412/25/74
15299.35114872990  2/26/74
16299.817212829912/26/7412/27/74
17300.30952183000  2/28/74
18300.827094830012/28/7413/1/74
19301.36898853010  3/2/74
20301.934296430113/2/7413/3/74
21302.52214823020  3/4/74
22303.13170653030  3/5/74
23303.762163730313/5/7413/6/74
24304.41273863040  3/7/74
25305.08267313050  3/8/74
26305.771229930513/8/7413/9/74
27306.47769063060  3/10/74
28307.20135523070  3/11/74
29307.941542330713/11/7413/12/74
30308.697593080  3/13/74
31309.46885693090  3/14/74
32310.25472383100  3/15/74
33311.0545943110  3/16/74
34311.867894531113/16/7413/17/74
35312.69407543120  3/18/74
36313.53261023130  3/19/74
37314.38299443140  3/20/74
38315.24474533150  3/21/74
39316.11740093160  3/22/74
40317.00051943170  3/23/74
41317.893679331713/23/7413/24/74
42318.79647953180  3/25/74
43319.70853973190  3/26/74
44320.62950073200  3/27/74
45321.55902523210  3/28/74
46322.49679733220  3/29/74
47323.44252213230  3/30/74
48324.39592453240  3/31/74
49325.35674743250  4/1/74
50326.32474943260  4/2/74
51327.29970233270  4/3/74
52328.28138813280  4/4/74
53329.26959683290  4/5/74
54330.26412393300  4/6/74
55331.26476923310  4/7/74
56332.27133543320  4/8/74
57333.28362833330  4/9/74
58334.30145593340  4/10/74
59335.32462983350  4/11/74
60336.35296543360  4/12/74
61337.38628213370  4/13/74
62338.42440443380  4/14/74
63339.46716163390  4/15/74
64340.51438783400  4/16/74
65341.56592233410  4/17/74
66342.62160863420  4/18/74
67343.68129513430  4/19/74
68344.74483473440  4/20/74
69345.81208533450  4/21/74
70346.88291053460  4/22/74
71347.95718033470  4/23/74
72349.03477223490  4/24/74
73350.1155723500  4/25/74
74351.19947433510  4/26/74
75352.28638343520  4/27/74
76353.37621243530  4/28/74
77354.46888333540  4/29/74
Sheet1
Cell Formulas
RangeFormula
N1:N77N1=A1:A77
J2:J77J2=TRUNC(I2)
K2:K77K2=COUNTIFS(J$2:J2,J2)-1
L2:L77L2=IFS(K2>=1,XLOOKUP(1,(J$2:J2=J2)*(K$2:K2=K2-1),$A$2:$A2),TRUE,"")
M2:M77M2=IFS(L2="","",TRUE,$A2-L2)
Dynamic array formulas.
Cells with Conditional Formatting
CellConditionCell FormatStop If True
K2:K77,M2:M77Expression=$K2<>0textNO
 
Upvote 0
The only thing I can think of is defining a fuzziness factor and compare the data against that factor:

Book001.xlsx
ABCDE
11.23456TRUEValue1.23456
21.23467TRUEFuzziness0.02
31.34567FALSE
41.35678FALSE
51.45678FALSE
61.45789FALSE
71.56789FALSE
81.56891FALSE
91.67891FALSE
101.68901FALSE
111.78901FALSE
121.78012FALSE
131.89012FALSE
141.89123FALSE
151.90123FALSE
161.91234FALSE
171.12345FALSE
181.13456FALSE
191.24567TRUE
201.25678FALSE
Sheet1
Cell Formulas
RangeFormula
B1:B20B1=ABS($E$1-A1)<=$E$2
Cells with Conditional Formatting
CellConditionCell FormatStop If True
B1:B20Cell Valuecontains "true"textNO
thanks for this.... you got me close enough to then calc fairly quickly by hand! Cheers, Will
 
Upvote 0

Forum statistics

Threads
1,223,238
Messages
6,170,939
Members
452,368
Latest member
jayp2104

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top