Sorting extracted data with array formula that includes data duplicates with no helper column

[Silent_John]

So through combining several formulas I now have this amazing formula that allows the user to have a criteria for extracting data. When they put this in, the formula pops out a sorted list of the data including the data from other columns that go along with item (Last, First, DOB). Which field the data is sorted by can be chosen by the user. My only problem is that duplicates ruin the whole thing. So, I am asking how I could make this formula work with duplicates (or a new formula altogether if it accomplished the same thing).

So if you open my linked example sheet everything works grand. However, that's because one name is Xavier and one is Xavier2. If you change "Xavier2" to just "Xavier" this creates a duplicate and if we are sorting by first name this breaks the formula. Not only is one Xavier missing from the list, also the information (last name and DOB) for the other people is no longer accurate.

I have the formula below, a link to the example sheet, an explanation of key cells below, and a screenshot below. Thank you so much for any help.

So, here is my array formula (from F6 and copied into F6:H11):

{=INDEX(INDIRECT(F$5), MATCH(MIN(IF((COUNTIF(F$5:F5, INDIRECT(F$5))=0)*(COUNTIF($F$2,Division)), 1, MAX((COUNTIF(INDIRECT($G$2), "<"&INDIRECT($G$2))+1)*2))*(COUNTIF(INDIRECT($G$2), "<"&INDIRECT($G$2))+1)), IF(COUNTIF($F$2,Division),COUNTIF(INDIRECT($G$2), "<"&INDIRECT($G$2))+1,""), 0))}

F$5 (G$5 and H$5) : Contains the title of the extracted data which, through indirect is able to reference the named range om the left (First, Last, DOB respectively in our case)

$F$2 : The criteria used to extract certain data. In this case the user puts in a certain division

"Division": Named range containing the field that contains all the divisions.

$G$2: Contains in text the field the user wants to sort the data by. With indirect it is used to get the named range of the chosen sort range.




Some constraints:
-Excel 2007
-No macros
-The solution needs to be contained to one formula as it currently is. A helper column cannot be created.
-Of course the usual is true: I am needing to do this in excel and not a database. Also "autosort" and filters are not an option.

 

[JoeMo]

Haven't read all of your post, but if this is true: "My only problem is that duplicates ruin the whole thing.", have you considered removing duplicates as a solution?

 

[Silent_John]

Joe,
Hmmm. My real data is much bigger as it contains the names and birthdays of all of the people in an organization (hundreds upon hundreds) along with other fields that are not in the example. Some people simply have the same first name and the same birthday, etc. So if I understand your question, I think the answer is that I cannot get rid of duplicates as that would mean getting rid of records for certain people. Also, the raw data will be changed regularly (people coming and going) and this formula is not one that is run once but will be used in a sheet that is accessed by several people day after day to get the latest info.

 

[JoeMo]

If there is duplication of the fields Division, First, Last and DOB would that constitute the kind of duplication that is causing you a problem? If yes, would duplication of those specific fields be sufficient to ensure that the entire record is a duplicate?

 

[Silent_John]

Joe,
There is necessarily going to be duplication of Division and the formula does not have a problem with that. I might be in the 1HG group and you might be in the 1HG group. The problem comes when any one of the other fields (that is the field chosen to sort based on...eg "first", "last", "DOB") is duplicated (along with being in the same division of course).

For example, say you and I are in the 1HG division. Your name is Joe Mo and my name is Joe Silent. If we are sorting based on first name, this causes a break in the formula (the "Xavier" "Xavier2" example above). Another example would be if we are sorting by last name and in the 1HG group (which you and I are a part of), and your name is Al Ramirez and my name is Jaun Ramirez. Again this causes a break because there are two last names the same. So, to answer your question, the duplication of these fields would not be enough to ensure that we had a superfluous duplication as obviously you and I are different people; we just happen to be on the same team with the same last name, DOB, or first name. And obviously in my real data there are plenty of people with the same first or last names, some of whom happen to be in the same division (having the same birthday would break it too but that would happen less frequently). So duplication of specific fields would not be sufficient to ensure that the entire record was a duplicate.

 

[Silent_John]

So I did some more research and I think I might be close but I cannot figure it out. I find a method that accomplishes seeing duplicate data differently by adding the Row() value divided by 100000000 (providing some miniscule difference. I cannot seem to incorporate this idea but I think it might doable with this method. Any ideas?

His method can be found here: excel how to sort table using function/equation only - Stack Overflow

Any ideas for my formula? Would this method work?