Alphabetize names with a dynamic range

[Stuepef]

I have a formula that makes a list of unique employee names that I am trying to alphabetize. The problem is there can be 2-50 (range A1:A50) employees within the list causing the alphabetize rank formula to count blank or 0 cells due to having a formula in that cell. Is there a way to use a dynamic range formula to not include the cells that contain 0 or blanks? So would it be possible to have the COUNTIF dynamically rank A1:A10 since A11:A50 are blank?

Current formula in B1:

=COUNTIF($A$1:$A$50,"<="&A1)

Desired Result:
[TABLE="class: outer_border, width: 500"]
<tbody>[TR]
[TD]Employee name[/TD]
[TD]Alphabetize rank[/TD]
[/TR]
[TR]
[TD]GRAY,BOBBY F[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]PARK,AMY G[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]JOHNSON,BONNIE[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]HARTFORD,ERIN Q[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]STEEL,ASHLEY[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]DOWEL,LOUIS[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]WORD,LARRY F[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD]DUNCAN,HARRY W[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]YOUNG,RICK R[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]KNIGHT,MELISSA R[/TD]
[TD]6[/TD]
[/TR]
</tbody>[/TABLE]

 

[James006]

Hello,

Why don't you modify your initial formula which creates the Unique Names ... to also sort the results alphabetically ...

Say your initial list is located in A2:A50 ... you could use in cell B2 the following Array Formula :

=INDEX(yourlist,MATCH(0,countif(yourlist,"<"&yourlist)-sum(countif(yourlist,B$1:B1))

Hope this will help

 

[Stuepef]

@James006 - The formula results in #N/A error.

{=INDEX($A$2:$A$11,MATCH(0,COUNTIF($A$2:$A$11,"<="&A2)-SUM(COUNTIF($A$2:$A$11,B$1:B1))))}

 

[James006]

Sorry ...

But just tested it ... and it works fine ...

We do agree this is an Array Formula ...

Instead of the Enter key ... use simultaneously the three keys : Control Shift Enter

=IFERROR(INDEX($A$2:$A$11,MATCH(0,COUNTIF($A$2:$A$11,"<"&$A$2:$A$11)-SUM(COUNTIF($A$2:$A$11,B$1:B1)),0)),"")

 

[Stuepef]

@James006 - the formula works great for the range A2:A11, but the true range is A2:A50. This is causing the issue as the formula is trying to rank the cells that have formulas in them, but no names. Thats why I was trying to figure out a way to use a dynamic range that only uses cells with names in them. The desired formula would use range A2:A50, but only rank A2:A11 since they have names populated in the cells.

 

[Stuepef]

This was solved:

https://www.excelforum.com/excel-fo...formula-to-alphabetize-names-not-working.html

Thank you @James006!