Automatic Alphabetize by last name

[Letto4135]

Why not just use the sort function? Because I work with idiots and they'll be using this too.

Problem, I can't get blanks spaces to stop appearing and I can't differentiate between two people with the same last name. When I can, I can't tell it to then look at first name to see who goes first.

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]"Alphabetized"[/TD]
[TD]Numbers in case of 0s[/TD]
[TD]Converted to Numbers[/TD]
[TD]Last only[/TD]
[TD]Full Name[/TD]
[/TR]
[TR]
[TD]=IFERROR(VLOOKUP(1,$O$2:$T$6,4,FALSE),"")[/TD]
[TD]=COUNTIF($Q$2:$Q$16,"<="&Q2)-COUNTIF(P2:P5,"0")[/TD]
[TD]=COUNTIF($Q$2:$Q$6,"<="&Q2)[/TD]
[TD]=TRIM(RIGHT(SUBSTITUTE(R2," ",REPT(" ",100)),100))[/TD]
[TD]Tyler Bailey[/TD]
[/TR]
[TR]
[TD]=IFERROR(VLOOKUP(2,$O$1:$T$6,4,FALSE),"")[/TD]
[TD]=COUNTIF($Q$2:$Q$16,"<="&Q3)-COUNTIF(P3:P6,"0")[/TD]
[TD]=COUNTIF($Q$2:$Q$6,"<="&Q3)[/TD]
[TD]=TRIM(RIGHT(SUBSTITUTE(R3," ",REPT(" ",100)),100))[/TD]
[TD]Bill Bailey[/TD]
[/TR]
[TR]
[TD]=IFERROR(VLOOKUP(3,$O$1:$T$6,4,FALSE),"")[/TD]
[TD]=COUNTIF($Q$2:$Q$16,"<="&Q1)-COUNTIF(P4:P7,"0")[/TD]
[TD]=COUNTIF($Q$2:$Q$6,"<="&Q4)[/TD]
[TD]=TRIM(RIGHT(SUBSTITUTE(R4," ",REPT(" ",100)),100))[/TD]
[TD]Christian Tanner[/TD]
[/TR]
[TR]
[TD]=IFERROR(VLOOKUP(4,$O$1:$T$6,4,FALSE),"")[/TD]
[TD]=COUNTIF($Q$1:$Q$16,"<="&Q1)-COUNTIF(P5:P8,"0")[/TD]
[TD]=COUNTIF($Q$2:$Q$6,"<="&Q5)[/TD]
[TD]=TRIM(RIGHT(SUBSTITUTE(R5," ",REPT(" ",100)),100))[/TD]
[TD]Kyle West[/TD]
[/TR]
[TR]
[TD]=IFERROR(VLOOKUP(5,$O$1:$T$6,4,FALSE),"")[/TD]
[TD]=COUNTIF($Q$1:$Q$16,"<="&Q1)-COUNTIF(P6:P9,"0")[/TD]
[TD]=COUNTIF($Q$2:$Q$6,"<="&Q6)[/TD]
[TD]=TRIM(RIGHT(SUBSTITUTE(R6," ",REPT(" ",100)),100))[/TD]
[TD]LOL FARM[/TD]
[/TR]
</tbody>[/TABLE]


When it does countif in either line, if there are multiple same last names it skips a number and has two of the next number instead so the "alphabetized" cloumn looks like this...

[TABLE="class: grid, width: 150, align: center"]
<tbody>[TR]
[TD]Blank space[/TD]
[/TR]
[TR]
[TD]Tyler Bailey[/TD]
[/TR]
[TR]
[TD]LOL FARM[/TD]
[/TR]
[TR]
[TD]Christian Tanner[/TD]
[/TR]
[TR]
[TD]Kyle West[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[oldbrewer]

[TABLE="width: 1103"]
<colgroup><col><col span="16"></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]a[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]fred green[/TD]
[TD="align: right"]5[/TD]
[TD]fred[/TD]
[TD]green[/TD]
[TD="align: right"]47[/TD]
[TD="align: right"]405[/TD]
[TD="align: right"]19[/TD]
[TD="align: right"]361665[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]b[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]harry black[/TD]
[TD="align: right"]6[/TD]
[TD]harry[/TD]
[TD]black[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]167[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]501[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]c[/TD]
[TD="align: right"]7[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]joe white[/TD]
[TD="align: right"]4[/TD]
[TD]joe[/TD]
[TD]white[/TD]
[TD="align: right"]715[/TD]
[TD="align: right"]61[/TD]
[TD="align: right"]79[/TD]
[TD="align: right"]3445585[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]d[/TD]
[TD="align: right"]11[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ann brown[/TD]
[TD="align: right"]4[/TD]
[TD]ann[/TD]
[TD]brown[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]405[/TD]
[TD="align: right"]279[/TD]
[TD="align: right"]338985[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]e[/TD]
[TD="align: right"]19[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]f[/TD]
[TD="align: right"]29[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]g[/TD]
[TD="align: right"]47[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]h[/TD]
[TD="align: right"]61[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]I[/TD]
[TD="align: right"]79[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]j[/TD]
[TD="align: right"]97[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]k[/TD]
[TD="align: right"]131[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]501[/TD]
[TD]harry[/TD]
[TD]black[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]l[/TD]
[TD="align: right"]167[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]338985[/TD]
[TD]ann[/TD]
[TD]brown[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]m[/TD]
[TD="align: right"]203[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]361665[/TD]
[TD]fred[/TD]
[TD]green[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]n[/TD]
[TD="align: right"]235[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]3445585[/TD]
[TD]joe[/TD]
[TD]white[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]o[/TD]
[TD="align: right"]279[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]p[/TD]
[TD="align: right"]311[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]q[/TD]
[TD="align: right"]353[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]r[/TD]
[TD="align: right"]405[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]s[/TD]
[TD="align: right"]467[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]t[/TD]
[TD="align: right"]519[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]u[/TD]
[TD="align: right"]561[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]v[/TD]
[TD="align: right"]613[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 5"]use search for " " to find the first name and surname[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]w[/TD]
[TD="align: right"]715[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]x[/TD]
[TD="align: right"]907[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 5"]assign numbers to each letter of the alphabet[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]y[/TD]
[TD="align: right"]1129[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]z[/TD]
[TD="align: right"]1471[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 4"]find the numbers for first 3 letters[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]multiply them[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 5"]use small function to place numbers in order[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 5"]use offset match to get the names in the bottom table[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]