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.
<tbody>
</tbody>
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...
<tbody>
</tbody>
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.
"Alphabetized" | Numbers in case of 0s | Converted to Numbers | Last only | Full Name |
=IFERROR(VLOOKUP(1,$O$2:$T$6,4,FALSE),"") | =COUNTIF($Q$2:$Q$16,"<="&Q2)-COUNTIF(P2:P5,"0") | =COUNTIF($Q$2:$Q$6,"<="&Q2) | =TRIM(RIGHT(SUBSTITUTE(R2," ",REPT(" ",100)),100)) | Tyler Bailey |
=IFERROR(VLOOKUP(2,$O$1:$T$6,4,FALSE),"") | =COUNTIF($Q$2:$Q$16,"<="&Q3)-COUNTIF(P3:P6,"0") | =COUNTIF($Q$2:$Q$6,"<="&Q3) | =TRIM(RIGHT(SUBSTITUTE(R3," ",REPT(" ",100)),100)) | Bill Bailey |
=IFERROR(VLOOKUP(3,$O$1:$T$6,4,FALSE),"") | =COUNTIF($Q$2:$Q$16,"<="&Q1)-COUNTIF(P4:P7,"0") | =COUNTIF($Q$2:$Q$6,"<="&Q4) | =TRIM(RIGHT(SUBSTITUTE(R4," ",REPT(" ",100)),100)) | Christian Tanner |
=IFERROR(VLOOKUP(4,$O$1:$T$6,4,FALSE),"") | =COUNTIF($Q$1:$Q$16,"<="&Q1)-COUNTIF(P5:P8,"0") | =COUNTIF($Q$2:$Q$6,"<="&Q5) | =TRIM(RIGHT(SUBSTITUTE(R5," ",REPT(" ",100)),100)) | Kyle West |
=IFERROR(VLOOKUP(5,$O$1:$T$6,4,FALSE),"") | =COUNTIF($Q$1:$Q$16,"<="&Q1)-COUNTIF(P6:P9,"0") | =COUNTIF($Q$2:$Q$6,"<="&Q6) | =TRIM(RIGHT(SUBSTITUTE(R6," ",REPT(" ",100)),100)) | LOL FARM |
<tbody>
</tbody>
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...
Blank space |
Tyler Bailey |
LOL FARM |
Christian Tanner |
Kyle West |
<tbody>
</tbody>