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
Bookmarks