Unique List and Associated Item Count

jaysonsperling

New Member
Joined
Dec 19, 2004
Messages
8
Greetings all.

I know that this topic has been handled in bits and chunks before, and I've browsed all the topics I could, but I'm curious about how to tie this all together.

I have a list of people and the logs of calls they've taken throughout a day.
I have the persons name, and thier employee ID. Each employee takes multiple calls, and each call shows up on its own line. Something like this:

CALL_TIME, AGENT_NAME, AGENT_ID
11:00, John Doe, Agent1
11:02, Jane Smith, Agent2
11:09, John Doe, Agent1
11:11, Rob Lowe, Agent3
11:13, John Doe, Agent1
11:14, Rob Lowe, Agent3

I am looking to produce something like this in a different worksheet:
AGENT_ID, CALLS_TAKEN
Agent1, 3
Agent2, 1
Agent3, 2

If anyone would be able to help, or point me in the right direction, it would be awesome, and greatly appreciated.

Thanks all for any help :rolleyes:
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

fairwinds

MrExcel MVP
Joined
May 15, 2003
Messages
8,638
Hi,

Pivot table:
Book1
ABCDEF
1CALL_TIMEAGENT_NAMEAGENT_IDCountofAGENT_ID
211:00JohnDoeAgent1AGENT_IDTotal
311:02JaneSmithAgent2Agent13
411:09JohnDoeAgent1Agent21
511:11RobLoweAgent3Agent32
611:13JohnDoeAgent1GrandTotal6
711:14RobLoweAgent3
Sheet1
 

jaysonsperling

New Member
Joined
Dec 19, 2004
Messages
8
Greets!

Thanks for the fast reply, you rock!

Is there anyway that this could also be accomplished via a function or script? I ask this because I have two sets of the data above, for different call types, and I've never figured out how the heck to mesh both sets of data together via a pivottable.

Thanks again! :)
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
jaysonsperling said:
...

Is there anyway that this could also be accomplished via a function or script?...

Keywords: Extracting a unique list or a list of distinct items and conditional count. [ ISNUMBER, COUNTIF, LOOKUP, MATCH, ROW ]
Book1
ABCDEFGH
13
2CALL_TIMEAGENT_NAMEAGENT_ID0AGENT_IDCALLS_TAKEN
311:00JohnDoeAgent11Agent13
411:02JaneSmithAgent22Agent21
511:09JohnDoeAgent1 Agent32
611:11RobLoweAgent33 
711:13JohnDoeAgent1  
811:14RobLoweAgent3  
9
Sheet1


Formulas...

D2 must house a 0.

D3, copied down:

=IF(ISNUMBER(MATCH(C3,$C$2:C2,0)),"",LOOKUP(9.99999999999999E+307,$D$2:D2)+1)

F1:

=LOOKUP(9.99999999999999E+307,D:D)

F3, copied down:

=IF(ROW()-ROW(F$3)+1<=$F$1,LOOKUP(ROW()-ROW(F$3)+1,D:D,C:C),"")

G3, copied down:

=COUNTIF(C:C,F3)
 

jaysonsperling

New Member
Joined
Dec 19, 2004
Messages
8
Aladin,

Your solution worked like a charm!

Thank you again very much. The data now comes together quite nicely and without errors.

One thing I did to tweak your last formula for the G3 series of cells is changed it to: "=IF(F3="","",COUNTIF(C:C,F3))". It looks like everything is working fine with that tiny change, I hope. :)

Thanks again, you guys are awesome and very helpful :)
 

Forum statistics

Threads
1,147,622
Messages
5,742,197
Members
423,711
Latest member
luisfreitas

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top