Sort data with calculated fields gives unexpected results

[wpryan]

Hi All, I have a real head-scratcher here...
I need to graph the output of an optical system that has a rotational axis from 0 to 720 degrees. I have to work with a logfile that considers the starting point to be 0 degrees, when in fact, it can be anywhere between 0 and 720. There are "pairs" of data to be sorted, in the example below "Rot Park" and "Park Power", where the sort order should be by "Rot Park" (column 1). To calculate the "rollover" position if the start position is >720 (which it normally is), I have the calculation

=IF(R[-1]C+1=721,0,R[-1]C+1)

(I'm doing it in VBA). The data in column 2 will be always 20 more than the previous value (e.g., if the rollover point is 3, the next expected value is 23).
Before sorting, the data are in the expected places (or rather, intervals). However, after sorting, I have two rows of consecutive data, which is not what I expect.
...I appreciate any help!

Rot Park	Park Power
124​	1005​
125​
126​
127​
128​
129​
130​
131​
132​
133​
134​
135​
136​
137​
138​
139​
140​
141​
142​
143​
144​	1002​
145​
146​
147​
148​
149​
150​
151​
152​
153​
154​
155​
156​
157​
158​
159​
160​
161​
162​
163​
164​	1002​
165​
166​
167​
168​
169​
170​
171​
172​
173​
174​
175​
176​
177​
178​
179​
180​
181​
182​
183​
184​	1002​
185​
186​
187​
188​
189​
190​
191​
192​
193​
194​
195​
196​
197​
198​
199​
200​
201​
202​
203​
204​	1000​
205​
206​
207​
208​
209​
210​
211​
212​
213​
214​
215​
216​
217​
218​
219​
220​
221​
222​
223​
224​	1000​
225​
226​
227​
228​
229​
230​
231​
232​
233​
234​
235​
236​
237​
238​
239​
240​
241​
242​
243​
244​	1000​
245​
246​
247​
248​
249​
250​
251​
252​
253​
254​
255​
256​
257​
258​
259​
260​
261​
262​
263​
264​	1000​
265​
266​
267​
268​
269​
270​
271​
272​
273​
274​
275​
276​
277​
278​
279​
280​
281​
282​
283​
284​	1000​
285​
286​
287​
288​
289​
290​
291​
292​
293​
294​
295​
296​
297​
298​
299​
300​
301​
302​
303​
304​	1000​
305​
306​
307​
308​
309​
310​
311​
312​
313​
314​
315​
316​
317​
318​
319​
320​
321​
322​
323​
324​	997​
325​
326​
327​
328​
329​
330​
331​
332​
333​
334​
335​
336​
337​
338​
339​
340​
341​
342​
343​
344​	998​
345​
346​
347​
348​
349​
350​
351​
352​
353​
354​
355​
356​
357​
358​
359​
360​
361​
362​
363​
364​	996​
365​
366​
367​
368​
369​
370​
371​
372​
373​
374​
375​
376​
377​
378​
379​
380​
381​
382​
383​
384​	1000​
385​
386​
387​
388​
389​
390​
391​
392​
393​
394​
395​
396​
397​
398​
399​
400​
401​
402​
403​
404​	999​
405​
406​
407​
408​
409​
410​
411​
412​
413​
414​
415​
416​
417​
418​
419​
420​
421​
422​
423​
424​	1000​
425​
426​
427​
428​
429​
430​
431​
432​
433​
434​
435​
436​
437​
438​
439​
440​
441​
442​
443​
444​	1001​
445​
446​
447​
448​
449​
450​
451​
452​
453​
454​
455​
456​
457​
458​
459​
460​
461​
462​
463​
464​	1006​
465​
466​
467​
468​
469​
470​
471​
472​
473​
474​
475​
476​
477​
478​
479​
480​
481​
482​
483​
484​	1004​
485​
486​
487​
488​
489​
490​
491​
492​
493​
494​
495​
496​
497​
498​
499​
500​
501​
502​
503​
504​	1001​
505​
506​
507​
508​
509​
510​
511​
512​
513​
514​
515​
516​
517​
518​
519​
520​
521​
522​
523​
524​	1002​
525​
526​
527​
528​
529​
530​
531​
532​
533​
534​
535​
536​
537​
538​
539​
540​
541​
542​
543​
544​	1006​
545​
546​
547​
548​
549​
550​
551​
552​
553​
554​
555​
556​
557​
558​
559​
560​
561​
562​
563​
564​	1001​
565​
566​
567​
568​
569​
570​
571​
572​
573​
574​
575​
576​
577​
578​
579​
580​
581​
582​
583​
584​	1000​
585​
586​
587​
588​
589​
590​
591​
592​
593​
594​
595​
596​
597​
598​
599​
600​
601​
602​
603​
604​	999​
605​
606​
607​
608​
609​
610​
611​
612​
613​
614​
615​
616​
617​
618​
619​
620​
621​
622​
623​
624​	1001​
625​
626​
627​
628​
629​
630​
631​
632​
633​
634​
635​
636​
637​
638​
639​
640​
641​
642​
643​
644​	1000​
645​
646​
647​
648​
649​
650​
651​
652​
653​
654​
655​
656​
657​
658​
659​
660​
661​
662​
663​
664​	1000​
665​
666​
667​
668​
669​
670​
671​
672​
673​
674​
675​
676​
677​
678​
679​
680​
681​
682​
683​
684​	998​
685​
686​
687​
688​
689​
690​
691​
692​
693​
694​
695​
696​
697​
698​
699​
700​
701​
702​
703​
704​	998​
705​
706​
707​
708​
709​
710​
711​
712​
713​
714​
715​
716​
717​
718​
719​
720​
0​
1​
2​
3​	995​
4​
5​
6​
7​
8​
9​
10​
11​
12​
13​
14​
15​
16​
17​
18​
19​
20​
21​
22​
23​	1001​
24​
25​
26​
27​
28​
29​
30​
31​
32​
33​
34​
35​
36​
37​
38​
39​
40​
41​
42​
43​	999​
44​
45​
46​
47​
48​
49​
50​
51​
52​
53​
54​
55​
56​
57​
58​
59​
60​
61​
62​
63​	1000​
64​
65​
66​
67​
68​
69​
70​
71​
72​
73​
74​
75​
76​
77​
78​
79​
80​
81​
82​
83​	1000​
84​
85​
86​
87​
88​
89​
90​
91​
92​
93​
94​
95​
96​
97​
98​
99​
100​
101​
102​
103​	1006​
104​
105​
106​
107​
108​
109​
110​
111​
112​
113​
114​
115​
116​
117​
118​
119​
120​
121​
122​
123​	1003​

 

[Alex Blakenburg]

Sorting rows that are dependent on other rows is never going to work. It will simply recalculate based on their new "relative" position.
You would need to do a copy paste values before sorting.

 

[wpryan]

Hi, and thanks for your reply. Actually, I do that before the Sort lines in my code, with the following:

shDataAnalysis.Cells.Range("A725").CurrentRegion.Value = shDataAnalysis.Cells.Range("B725").CurrentRegion.Value

...the result is as previously described...

 

[Alex Blakenburg]

Is what you are showing before after the sort ? Need before sort and expected result.

 

[wpryan]

Is what you are showing before after the sort ? Need before sort and expected result.

Hi there... It seems that my supposition about the data is incorrect... for all intents and purposes, 0 and 720 are the same in the optical system, so the repeated data is expected. When I filter, I will just have to omit the last line. Thanks for your efforts on my behalf...