Count unique individuals by day?

[Gingertrees]

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!!!

 

[vavs]

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

 

[Gingertrees]

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.

 

[vavs]

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.

 

[Aladin Akyurek]

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!!!

Try something like:

Control+shift+enter, not just enter...

=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.

 

[Gingertrees]

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:

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,,,,,,

 

[Aladin Akyurek]

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:

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,,,,,,

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?

 

[Gingertrees]

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.

 

[Aladin Akyurek]

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.

22-Oct	23-Oct	24-Oct	25-Oct	26-Oct		Day	22-Oct	23-Oct	24-Oct	25-Oct	26-Oct
20	19	20	36	41		Cum Distinct Item Count	6	11	14	15	19
21	20	27	37	42		Novel Distinct Item Count	6	5	3	1	4
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

<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>
</TBODY>

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:

=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:

=H2-N(G2)

 

[Gingertrees]

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

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