+ Reply to Thread
Results 1 to 19 of 19

Sort Data Range in Stepped using Formula

  1. #1
    Registered User
    Join Date
    09-09-2021
    Location
    London, Ontario, Canada
    MS-Off Ver
    Office 365
    Posts
    56

    Sort Data Range in Stepped using Formula

    Hello,

    I am looking for a solution that does not require a VBA code, if possible.
    I have a dataset with 300 rows and 40 columns of data (addressess and GPS points).
    What I was looking for was a way to use the sort function to organize it in a stepped fashion.
    I have no problems with using helper columns in the original data.
    I just can't seem to get my head around how to do this other than manually.
    The first couple columns will always have data. The gaps/blanks fall in after those.

    Original Data looks something like this:

    Please Login or Register  to view this content.
    Looking to organize it like this: I left the row number so you could see where it moved from. ( i realize the actual row numbers will not change)

    Please Login or Register  to view this content.
    I hope this makes sense.
    Gray
    Last edited by GrayWolf; 02-28-2022 at 09:00 AM.

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,378

    Re: Sort Data Range in Stepped using Formula

    I don't think what you have shared is going to be close enough to the real data for amyone to help you.

    There are instructions at the top of the page explaining how to attach your sample workbook.

    A good sample workbook has just 10-20 rows of representative data that has been desensitised. It also has expected results mocked up, relevant cells highlighted and a few explanatory notes.
    Ali


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

    Forum Rules (updated August 2023): please read them here.

  3. #3
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Sort Data Range in Stepped using Formula

    and you will have to explain the logic.

    First 1,6,8,9 because all columns are full...???

    Then 3,2,4,5,7 because?????
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  4. #4
    Registered User
    Join Date
    09-09-2021
    Location
    London, Ontario, Canada
    MS-Off Ver
    Office 365
    Posts
    56

    Re: Sort Data Range in Stepped using Formula

    Example file attached
    Edited - Newer file attached further down, Post #16
    Attached Files Attached Files
    Last edited by GrayWolf; 02-22-2022 at 02:04 PM.

  5. #5
    Registered User
    Join Date
    09-09-2021
    Location
    London, Ontario, Canada
    MS-Off Ver
    Office 365
    Posts
    56

    Re: Sort Data Range in Stepped using Formula

    Hi Glenn,
    That was a bad example in my original post.
    Basically I'm just looking for a way to group the like rows.

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,378

    Re: Sort Data Range in Stepped using Formula

    When you say that you are doing it manually, do you mean by using the SORT dialog?

    It seems Monitor Last Name, Monitor First Name and Monitor Address (each sorted small to large) will produce the results you want.

  7. #7
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,378

    Re: Sort Data Range in Stepped using Formula

    In T2 copied down:

    =AND(F2<>"",G2<>"",J2<>"",K2<>"")

    Sort Z to A on this column to get all the TRUE rows at the top.

    Is this the sort of thing you were thinking of?

  8. #8
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Sort Data Range in Stepped using Formula

    Lol. I still don't follow you. BUT, I will pitch in with a guess.

    K2, copied down:
    =IFERROR(MATCH(TRUE,ISBLANK($A2:$I2),0)+1,1)+ROW()/10^6

    and then this:

    =INDEX(A2:I9,MATCH(SMALL(K2:K10,SEQUENCE(ROWS(A2:A9))),K2:K10,0),SEQUENCE(,COLUMNS(A2:I2)))&""
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    09-09-2021
    Location
    London, Ontario, Canada
    MS-Off Ver
    Office 365
    Posts
    56

    Re: Sort Data Range in Stepped using Formula

    Quote Originally Posted by AliGW View Post
    In T2 copied down:

    =AND(F2<>"",G2<>"",J2<>"",K2<>"")

    Sort Z to A on this column to get all the TRUE rows at the top.

    Is this the sort of thing you were thinking of?
    Hello,
    Thank you for that example. This works on the small dataset I provided, but when I apply this to my full dataset they are not all grouped.
    Let me see if I can recreate and reattach my file.
    Plus I am currently trying to use the "index","sort" and "sequence" formula to do this also.
    Gray

  10. #10
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,378

    Re: Sort Data Range in Stepped using Formula

    I did warn you about samples not being representative enough!!!

  11. #11
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: Sort Data Range in Stepped using Formula

    Please try

    =LET(z,A2:S11,y,SORT(z,SEQUENCE(,COLUMNS(z)-5,6,)),IF(y="","",y))
    Attached Files Attached Files

  12. #12
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Sort Data Range in Stepped using Formula

    Hahaha. I had almost got the same result as Bo_Ry... but with a formula about 4 times longer!!

  13. #13
    Registered User
    Join Date
    09-09-2021
    Location
    London, Ontario, Canada
    MS-Off Ver
    Office 365
    Posts
    56

    Re: Sort Data Range in Stepped using Formula

    Quote Originally Posted by Glenn Kennedy View Post
    Lol. I still don't follow you. BUT, I will pitch in with a guess.

    K2, copied down:
    =IFERROR(MATCH(TRUE,ISBLANK($A2:$I2),0)+1,1)+ROW()/10^6

    and then this:

    =INDEX(A2:I9,MATCH(SMALL(K2:K10,SEQUENCE(ROWS(A2:A9))),K2:K10,0),SEQUENCE(,COLUMNS(A2:I2)))&""
    Hi Glenn,
    Thank you for your response. I think you followed quite well.
    Your example works on the small dataset in my example, however on the large dataset some records are mixed.
    I expect that it is because some of the rows has the same number of blank cells, just in different spots.

  14. #14
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Sort Data Range in Stepped using Formula

    Bo_Ry has cracked it... check out #11.

  15. #15
    Registered User
    Join Date
    09-09-2021
    Location
    London, Ontario, Canada
    MS-Off Ver
    Office 365
    Posts
    56

    Re: Sort Data Range in Stepped using Formula

    Quote Originally Posted by Bo_Ry View Post
    Please try

    =LET(z,A2:S11,y,SORT(z,SEQUENCE(,COLUMNS(z)-5,6,)),IF(y="","",y))
    Hello Bo_Ry,
    Thank you very much for your suggestion.
    This too did not help with my solution.
    I am adding an updated version of my dataset and you can see how some of the rows do not group as expected.
    Gray.

  16. #16
    Registered User
    Join Date
    09-09-2021
    Location
    London, Ontario, Canada
    MS-Off Ver
    Office 365
    Posts
    56

    Re: Sort Data Range in Stepped using Formula

    My deepest apologies. My original dataset did not include all the permutations of the data that I use and I feel like I wasted everyones time.
    I've just added an updated version of the dataset I use.
    I included the various examples to show how they work with my dataset.
    Attached Files Attached Files

  17. #17
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: Sort Data Range in Stepped using Formula

    The following Power Query Advanced Editor code groups the data similarly to that shown in the Expected sheet:
    Please Login or Register  to view this content.
    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  18. #18
    Registered User
    Join Date
    09-09-2021
    Location
    London, Ontario, Canada
    MS-Off Ver
    Office 365
    Posts
    56

    Re: Sort Data Range in Stepped using Formula

    Thank you very much. This seems to work.
    I just need to explore this Power Query thing. I've never used it.
    I appreciate the attachment. TY.
    Gray

    Administrative Note:

    Please don't quote whole posts, especially when you are responding to the one immediately preceding your own - it's just clutter. It's OK to quote if you are responding to a post out of sequence, but limit quoted content to a few relevant lines that makes clear to whom and what you are responding. Thanks!

    For normal conversational replies, try using the QUICK REPLY box below or the REPLY button instead of REPLY WITH QUOTE.
    Last edited by AliGW; 02-28-2022 at 09:08 AM. Reason: PLEASE don't quote unnecessarily!

  19. #19
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: Sort Data Range in Stepped using Formula

    You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.

+ 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. Stepped Formula Ranges
    By MikeBl1 in forum Excel General
    Replies: 1
    Last Post: 05-08-2021, 06:54 PM
  2. Formula to Calculate a Stepped Pay Incentive bonus
    By melissafr in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 04-27-2020, 08:20 AM
  3. [SOLVED] Formula help to calculate stepped commission percentage in a range
    By Thatguy99 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 05-22-2019, 03:49 AM
  4. Best formula to pull multiple range of data and sort alphabetical
    By Beh162 in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 09-14-2018, 12:50 PM
  5. Stepped loop through splintered range of cells
    By JasperD in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-07-2018, 02:12 PM
  6. [SOLVED] need a formula to sort data based on certain range and give it a new value
    By cnak in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 05-17-2017, 01:16 AM
  7. Formula for stepped rebate system
    By GeorgeW5.5mm in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-25-2016, 06:58 AM

Tags for this Thread

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