# Serpentine/Snake ordering

1. ## Serpentine/Snake ordering

I have a number of spreadsheets where I need to create a serpentine order. Each sheet has a column headed "Range" & next to it a column headed "Row". Each sheet has a different number of rows for each range. In the attached spreadsheet I need the Serp column for the "Early wheat" & TT canola to number 1-12, 24-13, 25-36, 48-37, 49-60, 72-61 but the GT canola has 8 rows so needs to number 1-8, 16-9, 17-24, 32-25, 33-40, 48-41.
The number of rows in each spreadsheet is at H1

Is there a formula to create this ordering?

2. ## Re: Serpentine/Snake ordering

Try this in A8:

=IF(ISODD(B8),COUNT(\$B\$8:B8),B8*\$H\$1-COUNTIF(\$B\$8:\$B8,B8)+1)

.

3. ## Re: Serpentine/Snake ordering

Brilliant it works perfectly. Thank you so much you've saved me oodles of time.

4. ## Re: Serpentine/Snake ordering

Welcome to the forum.

A bit cumbersome, but in A8 copied down:

=IF(ISTEXT(A7),1,IF(AND(AND(MOD(ROWS(\$1:1),\$H\$1)-1=0,ISEVEN(ROUNDUP(ROWS(\$1:1)/\$H\$1,0))),ISEVEN(ROUNDUP(ROWS(\$1:1)/\$H\$1,0))),ROUNDUP(ROWS(\$1:1)/\$H\$1,0)*\$H\$1,IF(ISEVEN(ROUNDUP(ROWS(\$1:1)/\$H\$1,0)),A7-1,IF(MOD(ROWS(\$1:1),\$H\$1)-1=0,(\$H\$1*ROUNDUP(ROWS(\$1:1)/\$H\$1,0))-\$H\$1+1,A7+1))))

5. ## Re: Serpentine/Snake ordering

LOL! Go with the easy option!!!

6. ## Re: Serpentine/Snake ordering

For completeness, here's a way that needs no helper columns:

=IF(ISODD(ROUNDUP(ROWS(\$1:1)/\$H\$1,0)),ROWS(\$1:1),IF(MOD(ROWS(\$1:1),\$H\$1)-1=0,ROUNDUP(ROWS(\$1:1)/\$H\$1,0)*\$H\$1,A7-1))

7. ## Re: Serpentine/Snake ordering

Thank you very much, I can use this to create a stand alone spreadsheet without needing to download the original.

8. ## Re: Serpentine/Snake ordering

No worries.

If you have not already done so, you may not be aware that you can thank those who have helped you by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.

9. ## Re: Serpentine/Snake ordering

Yes have done so, I'm amazed at how knowledgeable people are & are so willing to help a novice. Thank you all.

10. ## Re: Serpentine/Snake ordering

I've just noticed you've updated your profile to Office 365, so we can further shorten it to this:

=LET(r,ROWS(\$1:1),rr,ROUNDUP(r/\$H\$1,0),IF(ISODD(rr),r,IF(MOD(r,\$H\$1)-1=0,rr*\$H\$1,A7-1)))

11. ## Re: Serpentine/Snake ordering

Thank you again, you've given me lots to work with

12. ## Re: Serpentine/Snake ordering

Just shout if you need any explanation.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1