+ Reply to Thread
Results 1 to 7 of 7

Tricks to sorting data in multiple rows?

  1. #1
    Forum Contributor
    Join Date
    09-13-2012
    Location
    Midwest
    MS-Off Ver
    Excel 365
    Posts
    124

    Tricks to sorting data in multiple rows?

    So I have this nifty lil scheduler I made to be compatible and easy to use on a phone. Because itd be used on a phone, I kept information about one load on multiple lines so the user could see all the info without scrolling right. I really like how it is setup now except that I cannot sort by the load status due to it taking up multiple lines. I was thinking of turning the color key at the top into hyperlinks to different tables where loads of that particular status are, but I'm not sure how I could populate those loads into that table using only formulas (no vba due to phone compatibility). If anyone has any tricks that could help me sort by load status, please help. Thank you!

    (The attachment button isnt working so I attached a picture instead. All the fields have drop downs. When the user changes the status, it changes the color of that load.)

    Capture.JPG
    Luke

  2. #2
    Forum Expert kersplash's Avatar
    Join Date
    11-22-2016
    Location
    Perth
    MS-Off Ver
    Home 2016 (Windows 10)/Work 2013 Pro Plus (Windows 10)
    Posts
    2,012

    Re: Tricks to sorting data in multiple rows?

    To attach a file.

    Go Advanced -> Manage Attachments -> Upload

  3. #3
    Forum Contributor
    Join Date
    09-13-2012
    Location
    Midwest
    MS-Off Ver
    Excel 365
    Posts
    124

    Re: Tricks to sorting data in multiple rows?

    Thanks. Should be attached.
    Attached Files Attached Files

  4. #4
    Forum Expert
    Join Date
    10-15-2018
    Location
    MA, USA
    MS-Off Ver
    2010, 2019
    Posts
    1,605

    Re: Tricks to sorting data in multiple rows?

    First, you need to unmerge all cells in the range A3:L122. I think you can actually do this without any real damage to your formatting.

    I have shifted your data one column to the right and added a formula in column-A that I think allows you to sort while keeping the four associated lines per load together.

    The formula in column-A generates a sort key. The formula needs to be sensitive to the row on which it appears as the relative position of the status field and load number field changes depending on which row in the group of 4 rows per load that we are trying to reference it from. We also need to keep the four rows per load together and in the right order when sorting. These constraints drive the relative complexity of the sort key formula.

    Here's the formula in A3. It is copied down to A122
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    When sorting your sort range needs to be exactly A3:L122

    See if this approach is on the right track. If it is then the final step would be to decide what to do with the sort field - I'm sure you don't want it cluttering up your user interface.

    Attached is your modified workbook implementing the above.
    Attached Files Attached Files

  5. #5
    Forum Contributor
    Join Date
    09-13-2012
    Location
    Midwest
    MS-Off Ver
    Excel 365
    Posts
    124

    Re: Tricks to sorting data in multiple rows?

    *posts baby fist pump meme*
    Your example was great! Sparked the idea that each row of a load needs to be labeled with its Load No. and Status. Then I can turn those two rows into a table, and only sort by status which will keep the Load No in numerical order so the 4 rows of a load will always stay together! If I want it to look nice, I'll need to conditionally format the fonts to the same color of the background as status changes which will be a tedious task... If I could find a way to be able to easily sort the table while those two table columns are hidden, then I dont need to spend 8 hours conditionally formatting those fonts. Thanks a ton GW!!
    Attached Files Attached Files

  6. #6
    Forum Expert
    Join Date
    10-15-2018
    Location
    MA, USA
    MS-Off Ver
    2010, 2019
    Posts
    1,605

    Re: Tricks to sorting data in multiple rows?

    I'm not sure I understand the bit about "turn those two rows into a table", but certainly putting load & status on every line vastly simplifies the sorting. It probably doesn't matter to you at this point but note that the new row-3 you have added has broken my sort keys.

    You have a bit of work to do with your status drop-down list - somehow that has to propagate to the other 3 rows of the load.

    Another thought - instead of conditional formatting for the hidden text you could simply directly apply the following custom format to the cells in question: ;;;
    That will hide all cell contents.

    That all said, if you're all set then please take a moment to mark the thread as 'Solved' by using the thread tools menu above your first post.

    And thanks for the feedback and reputation points

  7. #7
    Forum Contributor
    Join Date
    09-13-2012
    Location
    Midwest
    MS-Off Ver
    Excel 365
    Posts
    124

    Re: Tricks to sorting data in multiple rows?

    Not sure if this is the final, but the sorting feature works pretty good, and looks nice on a phone. Sorry for pretty much ignoring your formula. I'll take a closer look at it and probably use it elsewhere. But thanks for the epiphany assist.
    Attached Files Attached Files

+ 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. Sorting One Row that Has Multiple Rows Data
    By Thox851441 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-05-2018, 09:31 AM
  2. [SOLVED] Sorting data from rows (multiple sheets) in columns (one sheet)
    By ANA M in forum Excel General
    Replies: 2
    Last Post: 03-16-2015, 08:54 AM
  3. Replies: 2
    Last Post: 03-16-2014, 01:40 PM
  4. Help needed sorting multiple columns/rows of data
    By missyhol in forum Excel General
    Replies: 0
    Last Post: 12-21-2012, 10:47 AM
  5. Sorting multiple rows
    By ript73 in forum Excel General
    Replies: 3
    Last Post: 09-06-2009, 02:42 AM
  6. Sorting Data - Multiple coluns and rows into an ordered list
    By summerfresh in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-10-2008, 10:33 AM
  7. sorting multiple rows
    By CCOD in forum Excel General
    Replies: 1
    Last Post: 07-13-2006, 01:55 PM

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