Count unique individuals by day?

Gingertrees

Gingertrees

Well-known Member
Joined
Sep 21, 2009
Messages
697
This is driving me up a wall. I'm calling people, and need to get a unique list of callers by day. Reports weekly (or monthly). I'm actually tracking this in Access, but producing reports in Excel, so Excel is where I need to count it (I think).

1 call per line.

So if Monday I can Bob 3 times, Sue, Amy, and Jan, I've got 4 unique people.
Tuesday I call Bob again, Amy again, and Thomas twice and Wendy, I've got 2 unique people.
Wednesday I call Bob, Jan, Thomas, and Frederick, I've only got 1 unique person.

You see it's a quandary, because Bob is unique on Monday, but he's not on Tuesday or Wednesday. Also if I call Bob 3 times on Monday, that first call still marks him as a unique call.

I cannot imagine an excel formula to do this. If you can, please help me!!!
 
Sort by date Sort by votes
If you have it in Access you can actually count distinct where date = whatever. You can do a simple loop to start on say today() - x and then increment x by -1 until = today() .

In the loop you simply count distinct Names from the table where the date = whatever your today() - x value is. You can play games with weekdays etcetera.

HTH
 
Upvote 0
I don't quite understand that. I tend to miswrite loops such that they crash my computer. Also, if I called Bob yesterday or 2 days ago, I don't want him counted in "today's" unique values. I get the impression that your loop would just count all the values (without multiple calls) within a day. I am still at a loss.
 
Upvote 0
Lets say today is 10/29 - btw it is. And you have a field called Name and one called Date.

My SQL in Access would be Count Distinct Name from PhoneCalls where Date = Today()

This will give you a count of all of the unique names (only 1 per name) where the Date field is = to 10/29.

It has been a while since I have done Access VBA so I do not want to steer you wrong on the syntax to do the loop.

Check with the Access side of Mr. Excel for samples of looping in Access or check out UtterAccess for code snippets.

It is pretty straight forward.
 
Upvote 0
Gingertrees said:
This is driving me up a wall. I'm calling people, and need to get a unique list of callers by day. Reports weekly (or monthly). I'm actually tracking this in Access, but producing reports in Excel, so Excel is where I need to count it (I think).

1 call per line.

So if Monday I can Bob 3 times, Sue, Amy, and Jan, I've got 4 unique people.
Tuesday I call Bob again, Amy again, and Thomas twice and Wendy, I've got 2 unique people.
Wednesday I call Bob, Jan, Thomas, and Frederick, I've only got 1 unique person.

You see it's a quandary, because Bob is unique on Monday, but he's not on Tuesday or Wednesday. Also if I call Bob 3 times on Monday, that first call still marks him as a unique call.

I cannot imagine an excel formula to do this. If you can, please help me!!!
Click to expand...

Try something like:

Control+shift+enter, not just enter...
Rich (BB code): 
=SUM(IF(FREQUENCY(IF(NameRange<>"",IF(DateRange=Date,
  MATCH(NameRange,NameRange,0))),
   ROW(DateRange)-ROW(INDEX(NameRange,1,1))+1),1))

Another option is to use SQL to the same end.
 
Upvote 0
OK, two more things: I'm having trouble identifying if NameRange and DateRange change throughout that CSE formula. Also, I used names for simplicity of explanation; it's actually ID numbers representing Bob, Amy, Sue, etc. So I'm afraid that SUM at the beginning will not so much count ID #s 24601 and 1000, but come up with 25601.

Here's a CSV of some sample data:
Code: 
22-Oct,23-Oct,24-Oct,25-Oct,26-Oct,,,,,,
20,19,20,36,41,,,,,,
21,20,27,37,42,,,,,,
22,21,28,38,45,,,,,,
22,25,36,40,46,,,,,,
20,26,37,21,47,,,,,,
23,27,38,40,48,,,,,,
23,28,,41,49,,,,,,
24,33,,42,50,,,,,,
25,22,,,51,,,,,,
,23,,,52,,,,,,
 
Last edited:
Upvote 0
Gingertrees said:
OK, two more things: I'm having trouble identifying if NameRange and DateRange change throughout that CSE formula. Also, I used names for simplicity of explanation; it's actually ID numbers representing Bob, Amy, Sue, etc. So I'm afraid that SUM at the beginning will not so much count ID #s 24601 and 1000, but come up with 25601.

Here's a CSV of some sample data:
Code: 
22-Oct,23-Oct,24-Oct,25-Oct,26-Oct,,,,,,
20,19,20,36,41,,,,,,
21,20,27,37,42,,,,,,
22,21,28,38,45,,,,,,
22,25,36,40,46,,,,,,
20,26,37,21,47,,,,,,
23,27,38,40,48,,,,,,
23,28,,41,49,,,,,,
24,33,,42,50,,,,,,
25,22,,,51,,,,,,
,23,,,52,,,,,,
Click to expand...

Looks like you have dates in a header row and you have id's under each date. Would you specify the whole range including the header row?
 
Upvote 0
Aladdin: correct. Header is 1st row, IDs are in rows 2:11. Entire range (I only have data for the 1st week so far) is actually about 400 rows x 5 columns. For the purposes here, range is A1:E11.
 
Upvote 0
Gingertrees said:
Aladdin: correct. Header is 1st row, IDs are in rows 2:11. Entire range (I only have data for the 1st week so far) is actually about 400 rows x 5 columns. For the purposes here, range is A1:E11.
Click to expand...

[TABLE="width: 814"]
<COLGROUP><COL style="WIDTH: 66pt; mso-width-source: userset; mso-width-alt: 3128" width=88><COL style="WIDTH: 65pt; mso-width-source: userset; mso-width-alt: 3072" width=86><COL style="WIDTH: 52pt; mso-width-source: userset; mso-width-alt: 2474" width=70><COL style="WIDTH: 50pt; mso-width-source: userset; mso-width-alt: 2389" width=67><COL style="WIDTH: 58pt; mso-width-source: userset; mso-width-alt: 2730" width=77><COL style="WIDTH: 15pt; mso-width-source: userset; mso-width-alt: 711" width=20><COL style="WIDTH: 199pt; mso-width-source: userset; mso-width-alt: 9443" width=266><COL style="WIDTH: 62pt; mso-width-source: userset; mso-width-alt: 2958" width=83><COL style="WIDTH: 65pt; mso-width-source: userset; mso-width-alt: 3100" width=87><COL style="WIDTH: 61pt; mso-width-source: userset; mso-width-alt: 2872" width=81><COL style="WIDTH: 62pt; mso-width-source: userset; mso-width-alt: 2929" width=82><COL style="WIDTH: 59pt; mso-width-source: userset; mso-width-alt: 2787" width=78><TBODY>[TR]
[TD="class: xl67, width: 88, bgcolor: transparent"]22-Oct[/TD]
[TD="class: xl68, width: 86, bgcolor: transparent"]23-Oct[/TD]
[TD="class: xl68, width: 70, bgcolor: transparent"]24-Oct[/TD]
[TD="class: xl68, width: 67, bgcolor: transparent"]25-Oct[/TD]
[TD="class: xl68, width: 77, bgcolor: transparent"]26-Oct[/TD]
[TD="class: xl65, width: 20, bgcolor: transparent"] [/TD]
[TD="class: xl66, width: 266, bgcolor: transparent"]Day[/TD]
[TD="class: xl67, width: 83, bgcolor: transparent"]22-Oct[/TD]
[TD="class: xl68, width: 87, bgcolor: transparent"]23-Oct[/TD]
[TD="class: xl68, width: 81, bgcolor: transparent"]24-Oct[/TD]
[TD="class: xl68, width: 82, bgcolor: transparent"]25-Oct[/TD]
[TD="class: xl68, width: 78, bgcolor: transparent"]26-Oct[/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: transparent"]20[/TD]
[TD="class: xl70, bgcolor: transparent"]19[/TD]
[TD="class: xl70, bgcolor: transparent"]20[/TD]
[TD="class: xl70, bgcolor: transparent"]36[/TD]
[TD="class: xl70, bgcolor: transparent"]41[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl66, bgcolor: transparent"]Cum Distinct Item Count[/TD]
[TD="class: xl71, bgcolor: transparent"]6[/TD]
[TD="class: xl71, bgcolor: transparent"]11[/TD]
[TD="class: xl71, bgcolor: transparent"]14[/TD]
[TD="class: xl71, bgcolor: transparent"]15[/TD]
[TD="class: xl71, bgcolor: transparent"]19[/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: transparent"]21[/TD]
[TD="class: xl70, bgcolor: transparent"]20[/TD]
[TD="class: xl70, bgcolor: transparent"]27[/TD]
[TD="class: xl70, bgcolor: transparent"]37[/TD]
[TD="class: xl70, bgcolor: transparent"]42[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl66, bgcolor: transparent"]Novel Distinct Item Count[/TD]
[TD="class: xl71, bgcolor: transparent"]6[/TD]
[TD="class: xl71, bgcolor: transparent"]5[/TD]
[TD="class: xl71, bgcolor: transparent"]3[/TD]
[TD="class: xl71, bgcolor: transparent"]1[/TD]
[TD="class: xl71, bgcolor: transparent"]4[/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: transparent"]22[/TD]
[TD="class: xl70, bgcolor: transparent"]21[/TD]
[TD="class: xl70, bgcolor: transparent"]28[/TD]
[TD="class: xl70, bgcolor: transparent"]38[/TD]
[TD="class: xl70, bgcolor: transparent"]45[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: transparent"]22[/TD]
[TD="class: xl70, bgcolor: transparent"]25[/TD]
[TD="class: xl70, bgcolor: transparent"]36[/TD]
[TD="class: xl70, bgcolor: transparent"]40[/TD]
[TD="class: xl70, bgcolor: transparent"]46[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: transparent"]20[/TD]
[TD="class: xl70, bgcolor: transparent"]26[/TD]
[TD="class: xl70, bgcolor: transparent"]37[/TD]
[TD="class: xl70, bgcolor: transparent"]21[/TD]
[TD="class: xl70, bgcolor: transparent"]47[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: transparent"]23[/TD]
[TD="class: xl70, bgcolor: transparent"]27[/TD]
[TD="class: xl70, bgcolor: transparent"]38[/TD]
[TD="class: xl70, bgcolor: transparent"]40[/TD]
[TD="class: xl70, bgcolor: transparent"]48[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: transparent"]23[/TD]
[TD="class: xl70, bgcolor: transparent"]28[/TD]
[TD="class: xl70, bgcolor: transparent"] [/TD]
[TD="class: xl70, bgcolor: transparent"]41[/TD]
[TD="class: xl70, bgcolor: transparent"]49[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: transparent"]24[/TD]
[TD="class: xl70, bgcolor: transparent"]33[/TD]
[TD="class: xl70, bgcolor: transparent"] [/TD]
[TD="class: xl70, bgcolor: transparent"]42[/TD]
[TD="class: xl70, bgcolor: transparent"]50[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: transparent"]25[/TD]
[TD="class: xl70, bgcolor: transparent"]22[/TD]
[TD="class: xl70, bgcolor: transparent"] [/TD]
[TD="class: xl70, bgcolor: transparent"] [/TD]
[TD="class: xl70, bgcolor: transparent"]51[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl70, bgcolor: transparent"]23[/TD]
[TD="class: xl70, bgcolor: transparent"] [/TD]
[TD="class: xl70, bgcolor: transparent"] [/TD]
[TD="class: xl70, bgcolor: transparent"]52[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[/TR]
</TBODY>[/TABLE]

Add the following code to you workbook using Alt+F11...
______________________
Function ArrayUnion(ParamArray Arg() As Variant) As Variant
' Code: Juan Pablo González
' Spec: Aladin Akyurek
' May 4, 2003
' Ref: TinyURL.com - shorten that long URL into a tiny URL
Dim TempUnion() As Variant
Dim i As Long, Itm As Variant, Ctr As Long
For i = LBound(Arg) To UBound(Arg)
Arg(i) = Arg(i)
If IsArray(Arg(i)) Then
For Each Itm In Arg(i)
Ctr = Ctr + 1
ReDim Preserve TempUnion(1 To Ctr) As Variant
TempUnion(Ctr) = Itm
Next Itm
Else
Ctr = Ctr + 1
ReDim Preserve TempUnion(1 To Ctr) As Variant
TempUnion(Ctr) = Arg(i)
End If
Next i
ArrayUnion = TempUnion
End Function</SPAN></SPAN>
________________________

Now we can invoke:

H2, control+shift+enter, not just enter, and copy across:
Rich (BB code): 
=SUM(IF(FREQUENCY(IF(arrayunion($A$2:A11)<>"",
  MATCH(arrayunion($A$2:A11),arrayunion($A$2:A11),0)),
   ROW(INDIRECT("1:"&COUNT(arrayunion($A$2:A11))))),1))

H3, just enter and copy across:
Rich (BB code): 
=H2-N(G2)
 
Last edited:
Upvote 0
OK, in retrospect, I somehow thought it'd be easy to get my data into this sort of simple spreadsheet format. It's not. Or I'm too stupid to realize an easy way to get it like that. Is there a variant on this formula I can use with data like the following?

The data comes out of my access db like this:
CallID/PersonID/Name/City/State/DateofCall
Code: 
CallID,PeopleID,CName,City,State,Date1,1,Bob,Allentown,PA,10/22/2012
2,1,Bob,Allentown,PA,10/29/2012
3,1,Bob,Allentown,PA,10/30/2012
4,2,Adam,Detroit,MI,10/22/2012
5,2,Adam,Detroit,MI,10/23/2012
6,3,Jenn,Fowlerville,MI,10/29/2012
7,3,Jenn,Fowlerville,MI,10/31/2012
8,4,Amy,Austin,TX,10/29/2012
9,5,James,Seward,AK,10/29/2012
10,5,James,Seward,AK,10/30/2012
11,5,James,Seward,AK,10/31/2012
12,6,Danny,Seward,AK,10/30/2012
13,6,Danny,Seward,AK,10/31/2012
14,7,Kathy,Anchorage,AK,10/23/2012
15,7,Kathy,Anchorage,AK,10/25/2012
16,7,Kathy,Anchorage,AK,10/29/2012
17,8,Anna,New York,NY,10/23/2012
18,8,Anna,New York,NY,10/24/2012
19,8,Anna,New York,NY,10/25/2012
20,9,Sherman,Wales,AK,10/25/2012
21,9,Sherman,Wales,AK,10/26/2012
22,9,Sherman,Wales,AK,10/31/2012
23,10,Crystal,Fairbanks,AK,11/1/2012
24,10,Crystal,Fairbanks,AK,11/1/2012
25,11,Joshua,Fairbanks,AK,10/29/2012
26,11,Joshua,Fairbanks,AK,10/30/2012
27,12,Theresa,North Pole,AK,10/26/2012
28,12,Theresa,North Pole,AK,10/29/2012
29,12,Theresa,North Pole,AK,10/31/2012
32,14,Miki,Delray Beach,FL,10/29/2012
33,14,Miki,Delray Beach,FL,10/30/2012
34,14,Miki,Delray Beach,FL,10/31/2012
35,15,Rik,Palm Springs,FL,10/26/2012
36,16,Ronald,Boynton Beach,FL,10/29/2012
37,16,Ronald,Boynton Beach,FL,10/30/2012
41,18,Track,Virginia Beach,VA,10/25/2012
42,18,Track,Virginia Beach,VA,11/1/2012
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Question Question
Excel VBA count unique values in a column within a named range
Replies
6
Views
388
Peter_SSs
Peter_SSs
S
  • Question Question
count number of unique Mondays in a date column
Replies
4
Views
1K
Dave Patton
D
I
  • Solved
Formula help - Index, Match, SumProduct/SumIFs? Which one?
Replies
4
Views
269
Eric W
Eric W
M
  • Question Question
Unique entries across multiple worksheets
Replies
4
Views
333
m_smith_solihull
M
B
  • Question Question
Insert Julian day in excel cell
Replies
1
Views
432
Joe4
Joe4

Share this page

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
Back
Top