Turning a Vertical Table into a Horizontal one

Another way is to use dynamic Names to avoid CSE arrays, names are treated as arrays automatically.

Name:= "Department"
Refers to:=

Formula:

=Sheet14!$A$2:INDEX(Sheet14!$A:$A,MATCH(REPT("z",255),Sheet14!$A:$A,1))

Name:= "Position"
Refers to:=

Formula:

=INDEX(IF(Department=Sheet14!E$1,ROW(Department),""),,1)

Then for headers
In E1 Drag across as required.

Formula:

=LOOKUP(REPT("Z",255),CHOOSE({1;2},"",INDEX(Department,MATCH(TRUE,INDEX(ISNA(MATCH(Department,$D$1:D$1,0)),0),0))))

In E2, Drag Across and down

Formula:

=IF(ROWS($2:2)>COUNT(Position),"",INDEX($B:$B,SMALL(Position,ROWS($2:2))))