Help to create a Index / Match list of unique values.

Hello All,

Please see the attached spreadsheet... "Data-EmployeeStatus" i've had to empty a number of fields to confidenuality but generally you can get teh idea that I'm training people's EmployeeID numbers by department. As we add new people they will be assigned different departments and their status will become active (and departed when they leave).

"Department Summary" is what i want to do. I did the first ACC 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 EmployeeID number.

I think this will need to be an ARRAY forumla but prefer it not be.

Thanks in advance for all your help!

I found something that someone else wrote that seems to do the sorting horizontally... but not sure how to make this work for me because i need the EmployeeIDs to be listed and not the department...

Let A1:N1 house a relevant sample on Sheet1...

Qq Qp Qs Fad Kad Mad Pp Pq Ps Gad Jad


Sheet2

11 Qq Qp Qs Fad Kad Mad Pp Pq Ps Gad Jad


A1:

Code:
=SUMPRODUCT(1-(TRIM(Sheet1!A1:N1)=""))B1, control+shift+enter, not just enter, and copy across:

Code:
=IF(COLUMNS($A2:A2)<=$A1,INDEX(Sheet1!$A$1:$N$1,
SMALL(IF(LEN(TRIM(Sheet1!$A$1:$N$1)),
COLUMN(Sheet1!$A$1:$N$1)-COLUMN(Sheet1!$A$1)+1),
COLUMNS($A2:A2))),"")

Any help is apprecaited!

Perfect! Wish i could give you more 3x *...