I am working on an attendance tracker and we need to provide the names
of specific types of absences from the data entry fields on demand.
what we have is like this
A B C
1 * Agent name Reason Shift*
2 Agent 1 Tardy 0700-1530
3 Agent 2 NCNS 0700-1530
4 Agent 3 Sick 0800-1630
5 Agent 5 NCNS 0800-1630

What I need is to be able to have a result such as...

NCNS
Agent 2
Agent 5

Tardy
Agent 1

Of course we are doing it by hand now, but is there a way to get this
to do it for us?


--
NJM
------------------------------------------------------------------------
NJM's Profile: http://www.excelforum.com/member.php...o&userid=31436
View this thread: http://www.excelforum.com/showthread...hreadid=511277

Sounds like you could just sort as First Key on the Reason column and Second
Key on the AgentName column.....

Vaya con Dios,
Chuck, CABGx3


"NJM" wrote in message
...

I am working on an attendance tracker and we need to provide the names
of specific types of absences from the data entry fields on demand.
what we have is like this
A B C
1 * Agent name Reason Shift*
2 Agent 1 Tardy 0700-1530
3 Agent 2 NCNS 0700-1530
4 Agent 3 Sick 0800-1630
5 Agent 5 NCNS 0800-1630

What I need is to be able to have a result such as...

NCNS
Agent 2
Agent 5

Tardy
Agent 1

Of course we are doing it by hand now, but is there a way to get this
to do it for us?


--
NJM
------------------------------------------------------------------------
NJM's Profile:
http://www.excelforum.com/member.php...o&userid=31436
View this thread: http://www.excelforum.com/showthread...hreadid=511277

Actually, I need more than just a sort. I need this to populate to a
seperate set of cells/worksheet. HR makes us leave our input fields
alone once they are entered, but we need to coalate the data for
department reports.


--
NJM
------------------------------------------------------------------------
NJM's Profile: http://www.excelforum.com/member.php...o&userid=31436
View this thread: http://www.excelforum.com/showthread...hreadid=511277

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

Then perhaps just sort or AutoFilter the main database, and then just copy
it over to another worksheet....then you can manipulate that new sheet at
will without distrubing the main database.....this can be done either by
hand, or by macro if the sorting/filtering requirements are always the
same............

Vaya con Dios,
Chuck, CABGx3


"NJM" wrote in message
...

Actually, I need more than just a sort. I need this to populate to a
seperate set of cells/worksheet. HR makes us leave our input fields
alone once they are entered, but we need to coalate the data for
department reports.


--
NJM
------------------------------------------------------------------------
NJM's Profile:
http://www.excelforum.com/member.php...o&userid=31436
View this thread: http://www.excelforum.com/showthread...hreadid=511277