Here's a proposal:

tardy NCNS sick don't know
tardy agent 6 tardy2 agent 1 agent 2 agent 3 agent 5
don't k agent 5 don't k1 agent 6 agent 4
NCNS agent 4 NCNS2
sick agent 3 sick1
NCNS agent 2 NCNS1
tardy agent 1 tardy1


Arrange your data as follows:

Reason for absence in Column A
Agent name in column B
sort A:B by agent name descending
in C2 type this formula: =A2&COUNTIF(A2:A$7;"="&A2)
copy down to the last row of your data
In D1 type the first reason for absence and continue with the next in E
and further to the right
In D2 type this formula:
=IF(ISNA(MATCH(D$1&ROW(D2)-ROW($D$2)+1;$C$2:$C$7;0));"";OFFSET($B$2;MATCH(D$1 &ROW(D2)-ROW($D$2)+1;$C$2:$C$7;0)-1;0))
You may have to replace the semicolons with commas depending on your
local Windows settings for regional and language
Copy D2 down as many rows as you expect agentt names
Copy all formulas in D to the right as required

Hope this does it for you.

Hans