+ Reply to Thread
Results 1 to 8 of 8

Sorting on multiple keys/multiple tables/multiple sheets

  1. 11-25-2020, 05:19 PM #1
    BoomerN
    BoomerN is offline
    Registered User
    Join Date
    11-24-2020
    Location
    East TN
    MS-Off Ver
    2010
    Posts
    4

    Sorting on multiple keys/multiple tables/multiple sheets

    I'm a long-time Excel user but by no-means an expert. Our state has been maintaining a dashboard with Covid-19 stats by county. I had been using this to populate my spreadsheet for tracking & general personal info results then passing along to family. The state also publishes complete datasets for downloading which has more criteria than I really need. Now the state has added so much additional info to the dashboard that my prior capture method no longer makes sense. I can easily pare the downloadable dataset to my needs but have a major sorting issue. This is purely personal... I'm a retired/disabled IT guy so use things like this to keep some skills up.

    There is nothing confidential in this data and anyone can download the data. That being said, I've created a small sample set but want to make sure where I should create a post and upload for my problem. Thanks all!!
    Last edited by 6StringJazzer; 11-27-2020 at 11:44 PM. Reason: Title changed to reflect question
    Reply With QuoteRegister To Reply
  2. 11-25-2020, 06:01 PM #2
    6StringJazzer
    6StringJazzer is offline
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,696

    Re: New Member here. Which sub-forum should I post an Excel advanced sorting question?

    Welcome to the Forum BoomerN!

    You can post it right here. We don't have a subforum that is specialized for that. Note that we support file uploads so you can attach the file in your post (see yellow banner at the top of the page).
    Reply With QuoteRegister To Reply
  3. 11-26-2020, 12:27 PM #3
    BoomerN
    BoomerN is offline
    Registered User
    Join Date
    11-24-2020
    Location
    East TN
    MS-Off Ver
    2010
    Posts
    4

    Re: New Member here. Which sub-forum should I post an Excel advanced sorting question?

    I've added notes in the file. Thanks again for help.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  4. 11-27-2020, 02:03 AM #4
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,926

    Re: New Member here. Which sub-forum should I post an Excel advanced sorting question?

    OK so what (and where) are you trying to sort?

    (once we know exactly what you are trying to do, we can adjust your thread title accordingly)
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford
    Reply With QuoteRegister To Reply
  5. 11-27-2020, 11:13 PM #5
    BoomerN
    BoomerN is offline
    Registered User
    Join Date
    11-24-2020
    Location
    East TN
    MS-Off Ver
    2010
    Posts
    4

    Re: New Member here. Which sub-forum should I post an Excel advanced sorting question?

    To give you an idea:
    Sorting data relating to state Covid-19 cases... four columns (COUNTY, CASES, DEATH, HOSP) repeated for each day.
    Tab 1
    By County
    11-Jul-2020
    COUNTY CASES DEATH HOSP

    By Cases-County
    12-Jul-2020
    COUNTY CASES DEATH HOSP

    By Deaths-Cases-County
    13-Jul-2020
    COUNTY CASES DEATH HOSP

    By Hosp-County
    14-Jul-2020
    COUNTY CASES DEATH HOSP


    Tab 1
    County_Sort
    Sort by County (A->Z)

    Tab 2
    Case_Sort
    Sort by 1. Cases (Large->Small) then 2. County (A->Z)

    Tab 3
    Deaths_Sort
    Sort by 1. Death (Large->Small), 2. Cases (Large->Small) then 3. County (A->Z)


    Tab 4
    Deaths_Sort
    Sort by 1. Hosp (Large->Small) then 2. County (A->Z)


    Trying to figure a way to replicate sort criteria to each set of columns. There are 95 rows per set x 4 columns.

    Thanks!
    Reply With QuoteRegister To Reply
  6. 11-28-2020, 03:55 PM #6
    6StringJazzer
    6StringJazzer is offline
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,696

    Re: Sorting on multiple keys/multiple tables/multiple sheets

    I have provided a file with macro that sorts all sheets. It assumes the number of counties never changes. I removed your merged cells (usually causes problems) but reformatted so the appearance is the same.

    Tab 2: The number of cases per county will almost certainly be unique so the second sort key probably never comes into play

    New sheet called Sort has a button. Click the button. You will get a pop-up message when sorting is complete. Took about 1 second on my machine.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  7. 11-28-2020, 04:24 PM #7
    BoomerN
    BoomerN is offline
    Registered User
    Join Date
    11-24-2020
    Location
    East TN
    MS-Off Ver
    2010
    Posts
    4

    Re: Sorting on multiple keys/multiple tables/multiple sheets

    Thanks! I'll make a copy of my live file and see if I can copy & fit your macro. Each tab grows each day as health dept data is copied in. With this data & my charts I've been able to prove to friends and family how face masks, etc have been helpful deterrent for Covid-19. Helps me add & improve my Excel skills in retirement too
    Reply With QuoteRegister To Reply
  8. 11-28-2020, 06:55 PM #8
    6StringJazzer
    6StringJazzer is offline
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,696

    Re: Sorting on multiple keys/multiple tables/multiple sheets

    The ranges are hard-coded in the VBA. There are at least two ways to make it more dynamic. One way is to convert your data tables to actual Excel Tables then you can use the table names in the code. After you settle in with it let me know how it works and if you needs revisions.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. New to forum.. Want to do an advanced sorting option keeping row data together.
    By Big_D_in_Va in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 09-14-2015, 03:17 PM
  2. New member, 1st post in any forum since 2008
    By Henryofchang in forum Hello..Introduce yourself
    Replies: 3
    Last Post: 05-20-2014, 01:04 AM
  3. Hello from a new forum member and someone that "thought" they were an advanced excel user
    By FJ1200SC in forum Hello..Introduce yourself
    Replies: 1
    Last Post: 11-15-2012, 01:53 PM
  4. hey! didnt know there was an excel forum! happy 2 find it. where do i post a question?
    By BONES302 in forum Hello..Introduce yourself
    Replies: 1
    Last Post: 06-21-2012, 05:39 AM
  5. Ethical question re: conduct of a forum member
    By theDude in forum Excel General
    Replies: 1
    Last Post: 02-25-2005, 02:50 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1