+ Reply to Thread
Results 1 to 15 of 15

Create a unique distinct sorted list while removing blanks

  1. #1
    Registered User
    Join Date
    04-26-2018
    Location
    Montreal
    MS-Off Ver
    2016
    Posts
    15

    Create a unique distinct sorted list while removing blanks

    Hi and thanks in advance for trying to help.

    I am creating a training schedule for a large number of staff from various departments. In one column, I will be listing the staff names, but this list will not be alphabetically sorted because it will be organized by department. I want to create another list based on the first that will be sorted and will ignore any blank rows (and duplicates).

    In searching the web, I found the following instructions that seemed to be doing what I want (even though their list accounts for both text and numerical values, whereas mine has no numbers). (I cannot post the link... the tutorial is called "create-a-unique-distinct-sorted-list-containing-both-numbers-text-removing-blanks-in-excel" and comes from a website called get-digital-help .com.)

    Try as I may, whenever I paste the formula, it returns an error that I cannot correct. I tried replacing "List" with an array (A2:A500, for example), I tried defining a "name" for the cells that I want used and resorted (and naming those cells "List"), but I might have done that wrong since I never did such a thing before... Is there something wrong with the instructions, the formula, or the person trying to paste it (me!)?

    =INDEX(List, MATCH(MIN(IF(ISBLANK(List)+COUNTIF(B1:$B$1, List), "", IF(ISNUMBER(List), COUNTIF(List, "<"&List), COUNTIF(List, "<"&List)+SUM(IF(ISNUMBER(List), 1, 0))+1))), IF(ISBLANK(List)+COUNTIF(B1:$B$1, List), "", IF(ISNUMBER(List), COUNTIF(List, "<"&List), COUNTIF(List, "<"&List)+SUM(IF(ISNUMBER(List), 1, 0))+1)), 0))

    Besides, is there an easier way to do this?

    In case this is pertinent, once the list is sorted, I will be making a drop-down list with it in another sheet using a "data validation" list. If it's possible to make the drop-down list alphabetically ordered when the data it references is unsorted, that would work too!

    Thank you again!
    foreignlady

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

    Re: Create a unique distinct sorted list while removing blanks

    Is it required to be finished by Ctrl-Shift-Enter combination, instead of Enter?
    ...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.
    Quang PT

  3. #3
    Registered User
    Join Date
    04-26-2018
    Location
    Montreal
    MS-Off Ver
    2016
    Posts
    15

    Re: Create a unique distinct sorted list while removing blanks

    Hi and thanks for answering.

    I did CTRL-SHIFT-ENTER without any success... But I am not clear on what the "array" is or how to define it. Should I be replacing List everywhere in the formula with the range where the data appears (e.g., A1:A500)?

    I apologize for my "beginner" level of understanding of these complicated functions.

    Thanks again!
    foreignlady

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

    Re: Create a unique distinct sorted list while removing blanks

    Just guess:
    1/ Make sure your define name "List" is an absolute address like $A$1:$A$500. (Not A1:A500)
    2/ Make sure row 1 is title and your result starts from row 2 (cell B2)
    If it does not help, try to post a piece of your sample (spreadsheet, not image)

  5. #5
    Registered User
    Join Date
    04-26-2018
    Location
    Montreal
    MS-Off Ver
    2016
    Posts
    15

    Re: Create a unique distinct sorted list while removing blanks

    Hi

    Just can't get this to work. I created a dummy list to fiddle with, but I don't even know what to try at this point.

    Thanks,
    foreignlady
    Attached Files Attached Files

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

    Re: Create a unique distinct sorted list while removing blanks

    I found that you are using COUNTIF on the origin list (B1:B1) instead of COUNTIF on destination (In attachment I place result in column D,from D2)

    In D2:
    Please Login or Register  to view this content.

  7. #7
    Registered User
    Join Date
    04-26-2018
    Location
    Montreal
    MS-Off Ver
    2016
    Posts
    15

    Re: Create a unique distinct sorted list while removing blanks

    Did you get that to work? Because it is not working for me...

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

    Re: Create a unique distinct sorted list while removing blanks

    Quote Originally Posted by Foreignlady222 View Post
    Did you get that to work? Because it is not working for me...
    Sure it is working for me.
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    04-26-2018
    Location
    Montreal
    MS-Off Ver
    2016
    Posts
    15

    Re: Create a unique distinct sorted list while removing blanks

    Well, I simply cannot figure out what I am doing wrong, but thanks to your attachment, I was able to copy the sheet and replace the dummy data with real data and make it work. I can't seem to copy the formula into another sheet and get it functional.

    So thanks a million!

    My only issue now is that I would like blanks to appear hidden, but instead the formula returns a "value not available error". Is there some way to prevent that?

  10. #10
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Cool Re: Create a unique distinct sorted list while removing blanks

    You can use PowerQuery aka Get&Transform without any formula. This is included in Excel 2016
    Attached Files Attached Files

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

    Re: Create a unique distinct sorted list while removing blanks

    Re: "the formula returns a "value not available error". Is there some way to prevent that?"
    Wrap the formula with the IFERROR function as in:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Remember to simulataneously press the Ctrl, Shift and Enter keys after you amend the formula and before you copy it down the column.
    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.

  12. #12
    Registered User
    Join Date
    04-26-2018
    Location
    Montreal
    MS-Off Ver
    2016
    Posts
    15

    Re: Create a unique distinct sorted list while removing blanks

    JeteMC, thanks for your "IFERROR" advice, and sorry for the delay in responding... Unfortunately, I still cannot enter the array formula and cannot figure out why. The only reason I have it set up is because I copied the sheet that bebo created and entered my data into it. When I try to add the IFERROR function, it still results in an error, and then if I remove the changes I made and return the function to what it was before, I get an error too. Yes, I am pressing CTRL-SHIFT-ENTER. I have tried both left and right CTRL keys, left and right SHIFT keys, just in case that might make a difference. I think I will just leave it alone...

    As for Power query "Get and transform", that seems to be super user friendly compared to this tricky array formula thing... I am fiddling with that option to see if I can get it to work properly for me, so far so good... Thanks Sandy!

  13. #13
    Registered User
    Join Date
    04-26-2018
    Location
    Montreal
    MS-Off Ver
    2016
    Posts
    15

    Re: Create a unique distinct sorted list while removing blanks

    RE: Power Query
    Unfortunately, while I have Excel 2016 and can make it work there, the rest of my colleagues work with 2010 and it does not work when opened in there (the data connection gets discarded, for some reason, so the query cannot be updated). Too bad, looked very promising...

  14. #14
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Create a unique distinct sorted list while removing blanks

    Maybe PivotTable will be enough, add Participants field to Rows Area.

    PowerQuery needs PowerQuery add-in for 2010/2013.

    Power Query for

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

    Re: Create a unique distinct sorted list while removing blanks

    In reference to amending the formula with IFERROR here is a copy of Bebo's file with the modified formula applied. Sorry but I can't replicate the issue of selecting D2 and confirming the array entered formula by simultaneously pressing the Ctrl, Shift and Enter keys. I have used both the Ctrl and Shift keys on the left and right along with both the Enter key next to the keyboard and the one next to the numeric keypad and it works with all combinations.
    Let us know if you have any questions.
    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. [SOLVED] How to deal with blank cells to get unique distinct alphabetically sorted list?
    By keyantkarthi in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 12-28-2015, 09:13 AM
  2. Replies: 8
    Last Post: 11-05-2014, 10:27 PM
  3. Create a Distinct Unique list for 2 Columns
    By boldcode in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 08-06-2014, 05:10 PM
  4. Replies: 12
    Last Post: 05-26-2014, 09:04 PM
  5. [SOLVED] Unique distinct alphabetically sorted list
    By atlant15 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-06-2013, 10:03 AM
  6. Replies: 3
    Last Post: 12-13-2011, 08:00 AM

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