I have a list of employee last names that I would like to alphabetize based on the plan chosen. I created the alphabetize formula and the criteria formula, but need assistance combining them if possible:
Alphabetize formula:
Criteria Formula (plan 1):
Employee List & Plan:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Employee[/TD]
[TD]Plan Selection[/TD]
[/TR]
[TR]
[TD][TABLE="width: 68"]
<tbody>[TR]
[TD="width: 68"]Richards[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD][TABLE="width: 68"]
<tbody>[TR]
[TD="width: 68"]Cone[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD][TABLE="width: 68"]
<tbody>[TR]
[TD="width: 68"]Flairty[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD][TABLE="width: 68"]
<tbody>[TR]
[TD="width: 68"]Hector[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD][TABLE="width: 68"]
<tbody>[TR]
[TD="width: 68"]James[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD][TABLE="width: 68"]
<tbody>[TR]
[TD="width: 68"]Paige[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD][TABLE="width: 68"]
<tbody>[TR]
[TD="width: 68"]William[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD][TABLE="width: 68"]
<tbody>[TR]
[TD="width: 68"]Wood[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD][TABLE="width: 68"]
<tbody>[TR]
[TD="width: 68"]Rose[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD][TABLE="width: 68"]
<tbody>[TR]
[TD="width: 68"]Maggie[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD][TABLE="width: 68"]
<tbody>[TR]
[TD="width: 68"]Sharon[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD][TABLE="width: 68"]
<tbody>[TR]
[TD="width: 68"]Jesse[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD][TABLE="width: 68"]
<tbody>[TR]
[TD="width: 68"]Lori[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD][TABLE="width: 68"]
<tbody>[TR]
[TD="width: 68"]Kim[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD][TABLE="width: 68"]
<tbody>[TR]
[TD="width: 68"]Jason[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]3[/TD]
[/TR]
</tbody>[/TABLE]
The sample workbook and employee information was posted at Excel Help Forum at: https://www.excelforum.com/excel-fo...lphabetize-based-on-criteria.html#post5082582
Alphabetize formula:
Code:
[COLOR=#333333]=IFERROR(INDEX(A$2:A$96,MATCH(ROWS(A$2:A2),INDEX(COUNTIFS(A$2:A$96,"> ",A$2:A$96,"<="&A$2:A$96),0),0)),"")[/COLOR]
Criteria Formula (plan 1):
Code:
[COLOR=#333333]{=IFERROR(INDEX($A$2:$A$16,SMALL(IF($B$2:$B$16=1+0,ROW($A$2:$A$16)-(ROW($A$2)-1)),ROWS($2:2))),"")}[/COLOR]
Employee List & Plan:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Employee[/TD]
[TD]Plan Selection[/TD]
[/TR]
[TR]
[TD][TABLE="width: 68"]
<tbody>[TR]
[TD="width: 68"]Richards[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD][TABLE="width: 68"]
<tbody>[TR]
[TD="width: 68"]Cone[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD][TABLE="width: 68"]
<tbody>[TR]
[TD="width: 68"]Flairty[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD][TABLE="width: 68"]
<tbody>[TR]
[TD="width: 68"]Hector[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD][TABLE="width: 68"]
<tbody>[TR]
[TD="width: 68"]James[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD][TABLE="width: 68"]
<tbody>[TR]
[TD="width: 68"]Paige[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD][TABLE="width: 68"]
<tbody>[TR]
[TD="width: 68"]William[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD][TABLE="width: 68"]
<tbody>[TR]
[TD="width: 68"]Wood[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD][TABLE="width: 68"]
<tbody>[TR]
[TD="width: 68"]Rose[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD][TABLE="width: 68"]
<tbody>[TR]
[TD="width: 68"]Maggie[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD][TABLE="width: 68"]
<tbody>[TR]
[TD="width: 68"]Sharon[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD][TABLE="width: 68"]
<tbody>[TR]
[TD="width: 68"]Jesse[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD][TABLE="width: 68"]
<tbody>[TR]
[TD="width: 68"]Lori[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD][TABLE="width: 68"]
<tbody>[TR]
[TD="width: 68"]Kim[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD][TABLE="width: 68"]
<tbody>[TR]
[TD="width: 68"]Jason[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]3[/TD]
[/TR]
</tbody>[/TABLE]
The sample workbook and employee information was posted at Excel Help Forum at: https://www.excelforum.com/excel-fo...lphabetize-based-on-criteria.html#post5082582