+ Reply to Thread
Results 1 to 8 of 8

Excel - non vba code - Combine 2 lists and sort the combined list using a formula

  1. #1
    Registered User
    Join Date
    01-13-2016
    Location
    Grafton
    MS-Off Ver
    2013 Office Pro
    Posts
    59

    Excel - non vba code - Combine 2 lists and sort the combined list using a formula

    Please see the attached sample worksheet. I am attempting to combine 2 lists of names and then sort the new list alphabetically (formula only - no vba code).

    Thanks in advance for your help.
    Attached Files Attached Files

  2. #2
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Excel - non vba code - Combine 2 lists and sort the combined list using a formula

    Hey CRJ,

    Why don't you simply copy the second list under the first list and sort them in a single list. No VBA needed. You will need about 6 mouse clicks to accomplish the task.
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  3. #3
    Registered User
    Join Date
    01-13-2016
    Location
    Grafton
    MS-Off Ver
    2013 Office Pro
    Posts
    59

    Re: Excel - non vba code - Combine 2 lists and sort the combined list using a formula

    The individuals that will be using this workbook are not computer savvy...they just want a single list in "drop-down" form. The problem seems to stem from the combined list of name that was derived via formula. If the combined list was "hand entered" there would not be a problem (in the past the technique I used in the attached sample worked perfectly - except names were hand entered).

  4. #4
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Excel - non vba code - Combine 2 lists and sort the combined list using a formula

    It looks like Kultools has a method but I still think it involves some VBA. I'd teach the end user how to copy and paste.

    https://www.extendoffice.com/documen...-one-list.html

  5. #5
    Registered User
    Join Date
    01-13-2016
    Location
    Grafton
    MS-Off Ver
    2013 Office Pro
    Posts
    59

    Re: Excel - non vba code - Combine 2 lists and sort the combined list using a formula

    Marvin...thanks - both options in the above would be fine; however, I wish to stay away from vba code, and since I have 6+users, I can't justify the cost of Kultools... we are a volunteer organization - simple tasks move from "person to person" on a regular basis, so training is out of the question.

  6. #6
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Excel - non vba code - Combine 2 lists and sort the combined list using a formula

    Hi CRJ,

    If there is a formula to do this with different length lists, I don't know how to do it. In the latest version of excel there are newer functions of Unique() and Sort() that might help but I expect your group doesn't have those either. Perhaps one of the smart Guru's will know how to solve this problem.

    Good luck. I hope there is an answer with your constraints.

  7. #7
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,409

    Re: Excel - non vba code - Combine 2 lists and sort the combined list using a formula

    Try in E9:
    Please Login or Register  to view this content.
    ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
    F9:
    Please Login or Register  to view this content.
    G9:
    Please Login or Register  to view this content.
    Quang PT

  8. #8
    Registered User
    Join Date
    01-13-2016
    Location
    Grafton
    MS-Off Ver
    2013 Office Pro
    Posts
    59

    Re: Excel - non vba code - Combine 2 lists and sort the combined list using a formula

    Works perfectly...thanks so much for your help.

+ 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. Replies: 19
    Last Post: 02-29-2020, 12:12 PM
  2. Replies: 3
    Last Post: 04-10-2019, 09:09 AM
  3. [SOLVED] Combine 5 lists into one big list
    By leprince2007 in forum Excel Formulas & Functions
    Replies: 14
    Last Post: 12-23-2016, 01:28 PM
  4. [SOLVED] Rearranging data from combined list to separate lists
    By VBADoofus in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-21-2014, 01:32 PM
  5. Import data from two lists into one combined list
    By ghladik in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-07-2012, 02:53 PM
  6. [SOLVED] A Macro to sort list after it has combined rows
    By JTM1200 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-26-2012, 10:27 PM
  7. Combined list from two lists
    By kumawat_s in forum Excel General
    Replies: 4
    Last Post: 06-09-2010, 12:00 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