How to count name appearence but only once per given dates

[Miro H]

Hi all,

This is my first post, hope someone can help me.

I have inherited an excel sheet that tracks the activities on site, by date and personnel. It looks something like this.

[TABLE="class: grid, width: 100"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Site[/TD]
[TD]Person[/TD]
[/TR]
[TR]
[TD]21/4/2015[/TD]
[TD="align: center"]1[/TD]
[TD]A, B, C[/TD]
[/TR]
[TR]
[TD]21/4/2015[/TD]
[TD="align: center"]2[/TD]
[TD]D, E[/TD]
[/TR]
[TR]
[TD]22/4/2015[/TD]
[TD="align: center"]2[/TD]
[TD]A, B, C[/TD]
[/TR]
[TR]
[TD]22/4/2015[/TD]
[TD="align: center"]3[/TD]
[TD]D, E, F[/TD]
[/TR]
[TR]
[TD]22/4/2015[/TD]
[TD="align: center"]4[/TD]
[TD]A, C, F[/TD]
[/TR]
[TR]
[TD]22/4/2015[/TD]
[TD="align: center"]5[/TD]
[TD]B, E, F[/TD]
[/TR]
[TR]
[TD]23/4/2015[/TD]
[TD="align: center"]5[/TD]
[TD]A, B[/TD]
[/TR]
[TR]
[TD]23/4/2015[/TD]
[TD="align: center"]6[/TD]
[TD]A[/TD]
[/TR]
</tbody>[/TABLE]


I have to do the report how many days each person spend on sites (out of office).

Based on the table above this would be the result:

[TABLE="class: grid, width: 128"]
<tbody>[TR]
[TD]Person[/TD]
[TD]Days[/TD]
[/TR]
[TR]
[TD="align: center"]A[/TD]
[TD="align: center"]3[/TD]
[/TR]
[TR]
[TD="align: center"]B[/TD]
[TD="align: center"]3[/TD]
[/TR]
[TR]
[TD="align: center"]C[/TD]
[TD="align: center"]2[/TD]
[/TR]
[TR]
[TD="align: center"]D[/TD]
[TD="align: center"]2[/TD]
[/TR]
[TR]
[TD="align: center"]E[/TD]
[TD="align: center"]2[/TD]
[/TR]
[TR]
[TD="align: center"]F[/TD]
[TD="align: center"]1[/TD]
[/TR]
</tbody>[/TABLE]


I've tried with COUNTIF, but simple COUNTIF function would not help as I need to count each person only once per given date. For example person A appears 5 times in total, but only on 3 different days. Or Person F appears 3 times, but only on 1 day so it was 1 day out of office.
So I'm stuck now.

One more thing, I can't change the appearance of original excel sheet.


Could anyone suggest how to make a formula that would automatically count this?

 

[JoeMo]

Assuming that all the sites you list are "offsite" sites, the site is not relevant only the first and third columns are relevant to what you want to calculate. Here's a UDF that you can use to do that. It's used just like any worksheet function as in the example below (copy formula in G2 down to cover all persons).

Excel Workbook
	A	B	C	D	E	F	G
1	Date	Site	Person			Person	Days Offsite
2	21/4/2015	1	A, B, C			A	3
3	21/4/2015	2	D, E			B	3
4	22/4/2015	2	A, B, C			C	2
5	22/4/2015	3	D, E, F			D	2
6	22/4/2015	4	A, C, F			E	2
7	22/4/2015	5	B, E, F			F	1
8	23/4/2015	5	A, B
9	23/4/2015	6	A
Sheet6

To install the code:
1. With your workbook active press Alt and F11 keys. This will open the VBE window.
2. In the project tree on the left of the VBE window, find your project and click on it.
3. On the VBE menu: Insert>Module
4. Copy the code from your browser window and paste it into the white space in the VBE window.
5. Close the VBE window and Save the workbook. If you are using Excel 2007 or a later version do a SaveAs and save it as a macro-enabled workbook (.xlsm file extension).
6. Press Alt+F8 keys to run the code

Function DaysOffSite(S As String, Dtes As Range) As Variant
Dim d As Object, Ct As Long, c As Range, i As Long
Set d = CreateObject("Scripting.dictionary")
For Each c In Dtes
    If InStr(c.Offset(0, 2).Value, S) > 0 Then
        If Not d.exists(c.Value) Then
            Ct = Ct + 1
            d.Add c.Value, Ct
        End If
    End If
Next c
DaysOffSite = Ct
End Function

 

[Domenic]

Are the dates true date values? If so, try...

A1:F9

[TABLE="width: 384"]
<tbody>[TR]
[TD="class: xl63"]Date[/TD]
[TD="class: xl63, width: 64"]Site[/TD]
[TD="class: xl63, width: 64"]Person[/TD]
[TD="class: xl63, width: 64"][/TD]
[TD="class: xl63, width: 64"]Person[/TD]
[TD="class: xl63, width: 64"]Days[/TD]
[/TR]
[TR]
[TD="class: xl64, align: right"]4/21/15[/TD]
[TD="class: xl63, align: right"]1[/TD]
[TD="class: xl63"]A, B, C[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"]A[/TD]
[TD="class: xl63, align: right"]3[/TD]
[/TR]
[TR]
[TD="class: xl64, align: right"]4/21/15[/TD]
[TD="class: xl63, align: right"]2[/TD]
[TD="class: xl63"]D, E[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"]B[/TD]
[TD="class: xl63, align: right"]3[/TD]
[/TR]
[TR]
[TD="class: xl64, align: right"]4/22/15[/TD]
[TD="class: xl63, align: right"]2[/TD]
[TD="class: xl63"]A, B, C[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"]C[/TD]
[TD="class: xl63, align: right"]2[/TD]
[/TR]
[TR]
[TD="class: xl64, align: right"]4/22/15[/TD]
[TD="class: xl63, align: right"]3[/TD]
[TD="class: xl63"]D, E, F[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"]D[/TD]
[TD="class: xl63, align: right"]2[/TD]
[/TR]
[TR]
[TD="class: xl64, align: right"]4/22/15[/TD]
[TD="class: xl63, align: right"]4[/TD]
[TD="class: xl63"]A, C, F[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"]E[/TD]
[TD="class: xl63, align: right"]2[/TD]
[/TR]
[TR]
[TD="class: xl64, align: right"]4/22/15[/TD]
[TD="class: xl63, align: right"]5[/TD]
[TD="class: xl63"]B, E, F[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"]F[/TD]
[TD="class: xl63, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl64, align: right"]4/23/15[/TD]
[TD="class: xl63, align: right"]5[/TD]
[TD="class: xl63"]A, B[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl64, align: right"]4/23/15[/TD]
[TD="class: xl63, align: right"]6[/TD]
[TD="class: xl63"]A[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[/TR]
</tbody>[/TABLE]

F2, confirmed with CONTROL+SHIFT+ENTER, and copied down:

=SUM(IF(FREQUENCY(IF(ISNUMBER(SEARCH(", "&E2&",",", "&$C$2:$C$9&",")),IF(ISNUMBER($A$2:$A$9),$A$2:$A$9)),$A$2:$A$9)>0,1))

Hope this helps!

 

[lrobbo314]

Nice Domenic. I was trying to figure out a formula solution to this but couldn't quite get there. I was using Transpose and countif, but I couldn't figure it out. Frequency is still one that I need to look into more. Cool solution.

 

[Domenic]

Nice Domenic. I was trying to figure out a formula solution to this but couldn't quite get there. I was using Transpose and countif, but I couldn't figure it out. Frequency is still one that I need to look into more. Cool solution.

Thanks! Much appreciated!

Cheers!