INDEX names in alphabetical order. Horizontally.

I had gotten this answer done before using numbers but now need to change it to text. Please see my prevously solved post for the numbered issue:

http://www.excelforum.com/excel-form...ue-values.html

=IFERROR(INDEX('Data-EmployeeStatus'!$A2:$A10000,SMALL(IF('Data-EmployeeStatus'!$E2:$E10000="Active",IF('Data-EmployeeStatus'!$C2:$C10000='Department Summary'!$A3,ROW('Data-EmployeeStatus'!$C2:$C10000)-MIN(ROW('Data-EmployeeStatus'!$C2:$C10000))+1)),COLUMNS($A:A))),"")

Now instead of this formula I will need to replace the EmployeeID number sheet with the EmployeeName in alphabetical order.

Please see example in "Department Summary". I did the two different groups by hand but i'm looking to pull from the "Data-EmployeeStatus" sheet all the information horizontally; with the critia that it only be active people and to display just their unique name in alphabetical order.

Thanks in advance for all your help!

Hi benishiryo,

Works perfectly!

Added an IFERROR to it to remove the #num when no values were found.

=IFERROR(INDEX('Data-EmployeeStatus'!$B$2:$B$20,MATCH(SMALL(IF(('Data-EmployeeStatus'!$B$2:$B$20="")+('Data-EmployeeStatus'!$C$2:$C$20<>$A2)+('Data-EmployeeStatus'!$E$2:$E$20<>"Active")>0,"",COUNTIF('Data-EmployeeStatus'!$B$2:$B$20,"<"&'Data-EmployeeStatus'!$B$2:$B$20)+1),COLUMNS($B2:B2)),IF(('Data-EmployeeStatus'!$B$2:$B$20="")+('Data-EmployeeStatus'!$C$2:$C$20<>$A2)+('Data-EmployeeStatus'!$E$2:$E$20<>"Active")>0,"",COUNTIF('Data-EmployeeStatus'!$B$2:$B$20,"<"&'Data-EmployeeStatus'!$B$2:$B$20)+1),0)),"")

Thanks much!