+ Reply to Thread
Results 1 to 7 of 7

Change Formula to VBA Sort List alphabetically with 2 criteria

  1. 03-14-2018, 06:12 AM #1
    annazet
    annazet is offline
    Forum Contributor
    Join Date
    02-07-2013
    Location
    London
    MS-Off Ver
    Excel 2019
    Posts
    105

    Change Formula to VBA Sort List alphabetically with 2 criteria

    Hello everyone,

    I am about to start a new tax year file and wanted to improve my workbook.
    I have a sheet called "Hours" on which i analyze staff hours and other bits.

    I would like to:
    Add second criteria to existing formula
    If Holiday!$d$4:$d70 has a date then omitt

    Sort the outcome alphabetically
    Use small?

    The below array formula sits on Hours sheet in cell c6:c70
    Existing criteria sits in cell $c$3 = department
    C6:c70 is a list of staff in given business
    holiday!c:c contains staff names
    holiday!b:b contains departments they work in

    Formula: copy to clipboard 
    Please Login or Register  to view this content.


    Finally i would like to find out how to convert the above into VBA.
    I know it's much to ask but to me it's another stepping stone in my excel and vba journey.
    I learn every day and i am loving it!

    Thank you everyone.
    Attached Files Attached Files
    Last edited by annazet; 03-17-2018 at 04:39 AM.
    Reply With QuoteRegister To Reply
  2. 03-14-2018, 10:34 PM #2
    LeoTaxi
    LeoTaxi is offline
    Forum Expert
    Join Date
    08-16-2015
    Location
    Antwerpen, Belgium
    MS-Off Ver
    2007-2016
    Posts
    2,380

    Re: Change Formula to VBA Sort List alphabetically with 2 criteria

    with change in sheet Holiday

    first Column A, First Row 1

    this code for worksheet

    Please Login or Register  to view this content.
    Kind regards
    Leo
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  3. 03-19-2018, 05:46 AM #3
    annazet
    annazet is offline
    Forum Contributor
    Join Date
    02-07-2013
    Location
    London
    MS-Off Ver
    Excel 2019
    Posts
    105

    Re: Change Formula to VBA Sort List alphabetically with 2 criteria

    Thank you LeoTaxi. It works great and is what i was looking for.
    Unfortunately i don't know how to change the references to adapt it to my needs.

    I need:
    Holiday Sheet: Names stored in D8:D700
    Holiday Sheet: 1st Criteria / Departments stored in B8:B700
    Holiday Sheet: 2nd Criteria / End Date stored in AA8:AA700
    Holiday Sheet: Headers in B7

    Hours Sheet: Names to populate in B8:B700
    Hours Sheet: Target.Value is in C3 as before


    Hope you can help.
    Thank you!
    Reply With QuoteRegister To Reply
  4. 03-19-2018, 06:50 AM #4
    LeoTaxi
    LeoTaxi is offline
    Forum Expert
    Join Date
    08-16-2015
    Location
    Antwerpen, Belgium
    MS-Off Ver
    2007-2016
    Posts
    2,380

    Re: Change Formula to VBA Sort List alphabetically with 2 criteria

    What you have in Col A and Col C ?,and what should be the second criteria ?

    Kind regards
    Leo
    Reply With QuoteRegister To Reply
  5. 03-19-2018, 07:30 AM #5
    annazet
    annazet is offline
    Forum Contributor
    Join Date
    02-07-2013
    Location
    London
    MS-Off Ver
    Excel 2019
    Posts
    105

    Re: Change Formula to VBA Sort List alphabetically with 2 criteria

    See attached.
    I am hoping it will be easier than text.
    This is exactly how my rows and columns are distributed.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  6. 03-19-2018, 09:02 AM #6
    LeoTaxi
    LeoTaxi is offline
    Forum Expert
    Join Date
    08-16-2015
    Location
    Antwerpen, Belgium
    MS-Off Ver
    2007-2016
    Posts
    2,380

    Re: Change Formula to VBA Sort List alphabetically with 2 criteria

    With modifications

    Please Login or Register  to view this content.
    Kind regards
    Leo
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  7. 03-19-2018, 10:21 AM #7
    annazet
    annazet is offline
    Forum Contributor
    Join Date
    02-07-2013
    Location
    London
    MS-Off Ver
    Excel 2019
    Posts
    105

    Re: Change Formula to VBA Sort List alphabetically with 2 criteria

    Perfect! So helpful! Thank you.
    Tried to use a combo box to select the department so it looks nicer than data validation.. but it then doesn't run the vba You have created.
    Any ideas how to walk this one around?
    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. [SOLVED] Formula to get unique and alphabetically sorted list after matching a criteria
    By Saranya A in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 10-20-2016, 06:50 AM
  2. Need to sort a list alphabetically ignoring blanks
    By john dalton in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-02-2016, 10:30 AM
  3. [SOLVED] Dynamic Formula for Getting Unique Items from a list and sort them alphabetically
    By tuckertheguy in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 09-18-2015, 03:57 AM
  4. [SOLVED] Sort Combobox list alphabetically
    By plans in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-13-2015, 01:51 PM
  5. [SOLVED] Macro to list data from a table and sort it alphabetically
    By Siglen in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 07-16-2012, 11:06 AM
  6. How do I sort my excel list alphabetically by last name?
    By JIMC in forum Excel General
    Replies: 2
    Last Post: 02-21-2006, 06:45 PM
  7. Sort text list alphabetically using a formula
    By paddyyates in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-20-2005, 11:15 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