Can you help me sort my data correctly?

[JRRyan]

Hi all,

so, I've created a set of arrays (sheet 2) to filter, sort, and remove blanks from across two columns (first name, last name) of a larger data set (level 4) and I'm outputting the results into a single column sorted alphabetically by last name.

I have used a set of four helper columns in sheet 2 to achieve this, but at the point where I sort the filtered data from columns B and C into column D I find that if there are two unique records with the same last names, it will enter the first instance twice instead of once each for both.

I have made a sample spreadsheet here's a download link:

http://aubilltech.weebly.com/uploads/4/1/1/8/4118723/filter_and_sort_test.xlsx

Also, I have a unique record ID column that could come in handy in column A, but I'd prefer to avoid using even more helper columns.

 

[Eric W]

Wow, your D2 formula is . . . incredible? I'm not even going to try to figure that out. Let's just jump to what you want as a final output. Your A/B/C columns simply extract the data from the 'Level 4' sheet that match the city and have a Y in column A. Then you have your D and E formulas, just designed to create a sorted list from A:C?

So do you want columns A:D, or are you happy just with the final list? Are all the IDs 7 digits? Would you be happy with different helper columns? I believe this can be done with formulas, but what are your feelings toward VBA?

 

[JRRyan]

Wow, your D2 formula is . . . incredible?

Thanks...? haha:D

You've described it pretty accurately. The formula in D2:DX is unwieldy, partly because of the error checking.

I would love to do this all in one column, but I found that it would break the sort order if the original data set was changed or refiltered in any way (which it is, frequently). Unfortunately, I can't use VBA as the network restricts macro-enabled spreadsheets. So, if you could do all that in a single formula, I would be very impressed!

In the meantime, just getting the alphabetical sort to work properly is my main priority. The more I think about it, the more I believe that the ID will have to be used somehow.

 

[Eric W]

Consider this:

	A	B	C	D
1		ILL4_Madrid	Sorted	First/Last
2	29	Mumduya,Wilma	Annatoo,Juan	Juan Annatoo
3	30	Hyman,Buster	Anthropist ,Phil	Phil Anthropist
4	31	Cherry,Poppa	Appeno ,Hal	Hal Appeno
5	32	Lee,Zack	Balls,Saul	Saul Balls
6	33	Stairs,Don	Cade,Barry	Barry Cade
7	34	Balls,Saul	Cherry,Poppa	Poppa Cherry
8	35	Pants,Peter	*******,Graham	Graham *******
9	36	Appeno ,Hal	Dente,Al	Al Dente
10	37	Matic,Otto	Ficial,Arty	Arty Ficial
11	38	Fugga,Moe	Foolery,Tom	Tom Foolery
12	39	*******,Graham	Fugga,Moe	Moe Fugga
13	40	Foolery,Tom	Gardens,Marvin	Marvin Gardens
14	41	Dente,Al	Graham,Holly	Holly Graham
15	42	Wiser,Bud	Harmonic ,Phil	Phil Harmonic
16	43	Graham,Holly	Hyman,Buster	Buster Hyman
17	44	Stein,Frank	Lee,Zack	Zack Lee
18	45	Toe,Cam	Matic,Otto	Otto Matic
19	46	Zona,Tara	Mumduya,Wilma	Wilma Mumduya
20	47	Cade,Barry	Nobatti,Donatella	Donatella Nobatti
21	48	Anthropist ,Phil	Pants,Peter	Peter Pants
22	49	Gardens,Marvin	Stairs,Don	Don Stairs
23	50	Harmonic ,Phil	Stein,Frank	Frank Stein
24	51	Ficial,Arty	Toe,Cam	Cam Toe
25	53	Nobatti,Donatella	Wiser,Bud	Bud Wiser
26	54	Annatoo,Juan	Zona,Tara	Tara Zona
27

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula B2 =IF(A2="","",INDEX('Level 4'!$E$2:$E$126,A2)&","&INDEX('Level 4'!$D$2:$D$126,A2)) D2 =IFERROR(MID(C2,FIND(",",C2)+1,99)&" "&LEFT(C2,FIND(",",C2)-1),"") <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Array Formulas Cell Formula A2 {=IFERROR(SMALL(IF('Level 4'!$G$2:$G$126=$B$1,IF('Level 4'!$A$2:$A$126="Y",ROW('Level 4'!$C$2:$C$126)-ROW('Level 4'!C$2)+1)),ROWS($A$2:$A2)),"")} C2 {=IFERROR(INDEX($B$2:$B$126,MATCH(SMALL(COUNTIF($B$2:$B$126,"<"&$B$2:$B$126),ROWS($C$2:$C2)+COUNTBLANK($B$2:$B$126)),COUNTIF($B$2:$B$126,"<"&$B$2:$B$126),0)),"")} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

Column A is just the row number of matches from the 'Level 4' sheet. Column B just uses that index to create a last name/first name list. Column C sorts column B. And finally, column D just rearranges the last/first names.

As far as doing it in a single column? Well, it would be really long and horribly inefficient, but I think it would be possible. But see how this works first.

 

[JRRyan]

That's a really interesting way of looking at it! Very elegant solution. I don't use character/cell instructions much. It never occurred to me to sort by last name first and split the order mid cell. Nice way of avoiding the blanks as well. Thank you!

 

[Eric W]

Glad to help!

 

[Fazza]

If I'm understanding, the requirement is for a sorted list of unique [Distinct] names for a particular City. A query can do this without formulas, without VBA.

A parameterised query can automatically update each time the cell containing the city value changes. Here is a good example, http://dailydoseofexcel.com/archives/2004/12/13/parameters-in-excel-external-data-queries/

For the specific data, the SQL could be,

SELECT DISTINCT [First Name] & ' ' & [Last Name]
FROM [Level 4$]
WHERE City = [?]

 

[Fazza]

I belatedly noticed that the sort order wanted was by last name & then first name. The solution I offered earlier did not address that.

Simple tweak but returning "last name, first name" would be

SELECT DISTINCT [Last Name] & ', ' & [First Name] AS [Full Name]
FROM [Level 4$]
WHERE City = [?]]

Or, untested (I'm assuming the parameterised value is OK down one level)), to sort as was asked (by last name & then first name with names returned 'first name last name')

SELECT [First Name] & ' ' & [Last Name] AS [Full Name]
FROM (
SELECT DISTINCT [Last Name], [First Name]
FROM [Level 4$]
WHERE City = [?])

PS Being queries, this approach will be efficient with large datasets.