...
Aladin - I don't mind it being slower! As it is, the spreadsheet, with all of its functionality, takes around 5 seconds to calculate, and speed isn't critical here - it's just a solution that will work for all entries up to around 10,000 that I require.
...
I assume you would want to try...
<TABLE style="WIDTH: 400pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=533 x:str><COLGROUP><COL style="WIDTH: 59pt; mso-width-source: userset; mso-width-alt: 2889" width=79><COL style="WIDTH: 107pt; mso-width-source: userset; mso-width-alt: 5229" width=143><COL style="WIDTH: 21pt; mso-width-source: userset; mso-width-alt: 1024" width=28><COL style="WIDTH: 19pt; mso-width-source: userset; mso-width-alt: 914" width=25><COL style="WIDTH: 90pt; mso-width-source: userset; mso-width-alt: 4388" width=120><COL style="WIDTH: 104pt; mso-width-source: userset; mso-width-alt: 5046" width=138><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 59pt; HEIGHT: 12.75pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 height=17 width=79>
Job Title</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 107pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 width=143>
Occupational Level</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 21pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 width=28> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 19pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 width=25> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 90pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 width=120 align=right x:num x:fmla='=SUM(IF(FREQUENCY(IF(B2:B6<>"",MATCH(B2:B6,B2:B6,0)),(ROW($A$2:$A$6)-ROW($A$2)+1)),1))' x:arrayrange="E1">
2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 104pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 width=138> </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl23 height=20>
Director</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24>
Top Management</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 align=right x:num x:fmla='=SUM(IF(FREQUENCY(IF($A$2:$A$6<>"",IF($B$2:$B$6=E$3,MATCH($A$2:$A$6,$A$2:$A$6,0))),(ROW($A$2:$A$6)-ROW($A$2)+1)),1))' x:arrayrange="E2">
1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 align=right x:num x:fmla='=SUM(IF(FREQUENCY(IF($A$2:$A$6<>"",IF($B$2:$B$6=F$3,MATCH($A$2:$A$6,$A$2:$A$6,0))),(ROW($A$2:$A$6)-ROW($A$2)+1)),1))' x:arrayrange="F2">
2</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl23 height=20>
HR Manager</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24>
Senior Management</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 x:fmla='=IF(COLUMNS($E$3:E3)<=$E$1,INDEX($B$2:$B$6,SMALL(IF(FREQUENCY(IF($B$2:$B$6<>"",MATCH("~"&$B$2:$B$6&"",$B$2:$B$6&"",0)),ROW($B$2:$B$6)-ROW($B$2)+1),ROW($B$2:$B$6)-ROW($B$2)+1),COLUMNS($E$3:E3))),"")' x:arrayrange="E3">
Top Management</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 x:fmla='=IF(COLUMNS($E$3:F3)<=$E$1,INDEX($B$2:$B$6,SMALL(IF(FREQUENCY(IF($B$2:$B$6<>"",MATCH("~"&$B$2:$B$6&"",$B$2:$B$6&"",0)),ROW($B$2:$B$6)-ROW($B$2)+1),ROW($B$2:$B$6)-ROW($B$2)+1),COLUMNS($E$3:F3))),"")' x:arrayrange="F3">
Senior Management</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl23 height=20>
Director</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24>
Senior Management</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 x:fmla='=IF(ROWS(E$4:E4)<=E$2,INDEX($A$2:$A$6,SMALL(IF(FREQUENCY(IF($A$2:$A$6<>"",IF($B$2:$B$6=E$3,MATCH($A$2:$A$6,$A$2:$A$6,0))),(ROW($A$2:$A$6)-ROW($A$2)+1)),(ROW($A$2:$A$6)-ROW($A$2)+1)),ROWS(E$4:E4))),"")' x:arrayrange="E4">
Director</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 x:fmla='=IF(ROWS(F$4:F4)<=F$2,INDEX($A$2:$A$6,SMALL(IF(FREQUENCY(IF($A$2:$A$6<>"",IF($B$2:$B$6=F$3,MATCH($A$2:$A$6,$A$2:$A$6,0))),(ROW($A$2:$A$6)-ROW($A$2)+1)),(ROW($A$2:$A$6)-ROW($A$2)+1)),ROWS(F$4:F4))),"")' x:arrayrange="F4">
Director</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl23 height=20>
Director</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24>
Senior Management</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 x:str="" x:fmla='=IF(ROWS(E$4:E5)<=E$2,INDEX($A$2:$A$6,SMALL(IF(FREQUENCY(IF($A$2:$A$6<>"",IF($B$2:$B$6=E$3,MATCH($A$2:$A$6,$A$2:$A$6,0))),(ROW($A$2:$A$6)-ROW($A$2)+1)),(ROW($A$2:$A$6)-ROW($A$2)+1)),ROWS(E$4:E5))),"")' x:arrayrange="E5"> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 x:fmla='=IF(ROWS(F$4:F5)<=F$2,INDEX($A$2:$A$6,SMALL(IF(FREQUENCY(IF($A$2:$A$6<>"",IF($B$2:$B$6=F$3,MATCH($A$2:$A$6,$A$2:$A$6,0))),(ROW($A$2:$A$6)-ROW($A$2)+1)),(ROW($A$2:$A$6)-ROW($A$2)+1)),ROWS(F$4:F5))),"")' x:arrayrange="F5">
HR Manager</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25 height=20>
Director</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24>
Top Management</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 x:str="" x:fmla='=IF(ROWS(E$4:E6)<=E$2,INDEX($A$2:$A$6,SMALL(IF(FREQUENCY(IF($A$2:$A$6<>"",IF($B$2:$B$6=E$3,MATCH($A$2:$A$6,$A$2:$A$6,0))),(ROW($A$2:$A$6)-ROW($A$2)+1)),(ROW($A$2:$A$6)-ROW($A$2)+1)),ROWS(E$4:E6))),"")' x:arrayrange="E6"> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 x:str="" x:fmla='=IF(ROWS(F$4:F6)<=F$2,INDEX($A$2:$A$6,SMALL(IF(FREQUENCY(IF($A$2:$A$6<>"",IF($B$2:$B$6=F$3,MATCH($A$2:$A$6,$A$2:$A$6,0))),(ROW($A$2:$A$6)-ROW($A$2)+1)),(ROW($A$2:$A$6)-ROW($A$2)+1)),ROWS(F$4:F6))),"")' x:arrayrange="F6"> </TD></TR></TBODY></TABLE>
A1:B6 (on Sheet1) houses the sample you provided, the headers included.
Define
Rvec by means of Insert|Name|Define as referring to:
=ROW(Sheet1!$A$2:$A$6)-ROW(Sheet1!$A$2)+1
In what follows, all special char features from the formulas are removed for it's expected that there aren't any special chars like <, *, etc. around the entries.
E1, control+shift+enter, not just enter:
=SUM(IF(FREQUENCY(IF(B2:B6<>"",MATCH(B2:B6,B2:B6,0)),Rvec),1))
E2, control+shift+enter and copy across:
Code:
=SUM(IF(FREQUENCY(IF($A$2:$A$6<>"",IF($B$2:$B$6=E$3,
MATCH($A$2:$A$6,$A$2:$A$6,0))),Rvec),1))
E4, control+shift+enter, copy across, and then down:
Code:
=IF(ROWS(E$4:E4)<=E$2,INDEX($A$2:$A$6,
SMALL(IF(FREQUENCY(IF($A$2:$A$6<>"",IF($B$2:$B$6=E$3,
MATCH($A$2:$A$6,$A$2:$A$6,0))),Rvec),Rvec),
ROWS(E$4:E4))),"")