How to count name appearence but only once per given dates

Miro H

New Member
Joined
Mar 25, 2015
Messages
6
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.

DateSitePerson
21/4/20151A, B, C
21/4/20152D, E
22/4/20152A, B, C
22/4/20153D, E, F
22/4/20154A, C, F
22/4/20155B, E, F
23/4/20155A, B
23/4/20156A

<tbody>
</tbody>


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:

PersonDays
A3
B3
C2
D2
E2
F1

<tbody>
</tbody>


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

MrExcel MVP
Joined
May 26, 2009
Messages
16,666
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
ABCDEFG
1DateSitePersonPersonDays Offsite
221/4/20151A, B, CA
321/4/20152D, EB3
422/4/20152A, B, CC2
522/4/20153D, E, FD2
622/4/20154A, C, FE2
722/4/20155B, E, FF1
823/4/20155A, B
923/4/20156A
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
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

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,005
Are the dates true date values? If so, try...

A1:F9

DateSitePersonPersonDays
4/21/151A, B, CA3
4/21/152D, EB3
4/22/152A, B, CC2
4/22/153D, E, FD2
4/22/154A, C, FE2
4/22/155B, E, FF1
4/23/155A, B
4/23/156A

<tbody>
</tbody>

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

Well-known Member
Joined
Jul 14, 2008
Messages
2,588
Office Version
365, 2019, 2016
Platform
Windows
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

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,005
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!
 

Forum statistics

Threads
1,081,849
Messages
5,361,681
Members
400,645
Latest member
Zeak

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top