+ Reply to Thread
Results 1 to 12 of 12

Serpentine/Snake ordering

  1. #1
    Registered User
    Join Date
    11-21-2018
    Location
    Northam, Australia
    MS-Off Ver
    Office 365
    Posts
    5

    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?
    Attached Files Attached Files
    Last edited by pip6566; 05-02-2022 at 02:50 AM. Reason: Solved

  2. #2
    Forum Guru
    Join Date
    02-27-2016
    Location
    Vietnam
    MS-Off Ver
    2016
    Posts
    5,449

    Re: Serpentine/Snake ordering

    Try this in A8:

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

    .
    Last edited by Phuocam; 05-02-2022 at 02:46 AM.

  3. #3
    Registered User
    Join Date
    11-21-2018
    Location
    Northam, Australia
    MS-Off Ver
    Office 365
    Posts
    5

    Re: Serpentine/Snake ordering

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

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    62,198

    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))))
    Attached Files Attached Files
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" to anyone who has offered you help in your thread. You can reward them by clicking on * Add Reputation below theur user name on the left, if you wish.


    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.

  5. #5
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    62,198

    Re: Serpentine/Snake ordering

    LOL! Go with the easy option!!!

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    62,198

    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))
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    11-21-2018
    Location
    Northam, Australia
    MS-Off Ver
    Office 365
    Posts
    5

    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. #8
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    62,198

    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. #9
    Registered User
    Join Date
    11-21-2018
    Location
    Northam, Australia
    MS-Off Ver
    Office 365
    Posts
    5

    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. #10
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    62,198

    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)))
    Attached Files Attached Files
    Last edited by AliGW; 05-02-2022 at 03:25 AM. Reason: Workbook attached.

  11. #11
    Registered User
    Join Date
    11-21-2018
    Location
    Northam, Australia
    MS-Off Ver
    Office 365
    Posts
    5

    Re: Serpentine/Snake ordering

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

  12. #12
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    62,198

    Re: Serpentine/Snake ordering

    Just shout if you need any explanation.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Serpentine numbers in a table formula
    By bdbart in forum Excel Formulas & Functions
    Replies: 14
    Last Post: 04-22-2022, 02:18 PM
  2. Sorting snake fashion
    By okanagan in forum Excel General
    Replies: 5
    Last Post: 08-16-2021, 04:52 AM
  3. snake long rows in one page
    By antonioch1228 in forum Excel General
    Replies: 3
    Last Post: 01-24-2018, 01:00 PM
  4. [SOLVED] Excel Function to number in a snake order
    By Shudodger in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 02-11-2014, 06:47 PM
  5. VBA Ordering User form for Purchase Ordering.
    By caf20012 in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 01-10-2014, 01:50 PM
  6. Snake columns according to cell value instead of row number
    By jassybunny in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-29-2012, 08:13 AM
  7. Snake Count for Fantasy Football
    By Pauleyb in forum Excel General
    Replies: 3
    Last Post: 08-30-2011, 05:43 PM

Bookmarks

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