Counting number of Times "name" appears across multiple sheets

[underpar]

Hello:
This is a tough question to verbalize but I'll try my best. I will have a workbook with multiple sheets...one for each day. The sheets will show the names of people visiting a website. What I'm trying to do, is have a count of the number of times each "name" hit the website, but I won't know the names in advance. Maybe the question will be clearer after looking at the photos attached.

DAY 1 Sheet: shows the names (which I don't know in advance) who visited the website.
DAY 2 Sheet: shows the names (which I don't know in advance) who visited the website. It's the same people as Day 1.
DAY 3 Sheet: shows the names (which I don't know in advance) who visited the website.This time, 2 names are the same as previous days but one is different.
SUMMARY Sheet: Is there a way to show each name and the number of times they appear in the "day sheets". Since I don't know the names in advance, I can't populate the Summary Sheet with names.

Sorry for the rambling and I appreciate your help.
Cheers!

 

[Eric W]

Got it. Change this line:

If s.Name Like "Day *" Then

to

If LCase(s.Name) Like "day *" Then

I was checking for upper case "Day 1", and your sheets were named "day 1". The line I changed will now check for either.

 

[underpar]

Hi Eric: This is AWESOME and does exactly what I need. Thank you very much for the work you put into this and your prompt replies!!

 

[Eric W]

Glad it works for you! Thanks for the feedback!

 

[underpar]

Glad it works for you! Thanks for the feedback!

Hi Eric:
Could I ask you for one more version of the macro? I promise, this will be the last request !
This time, the sheet will be setup the same way with "day 1", "day 2", etc and a summary sheet.
The change will be that the names are 4 across in the format of "last name, first name".
The summary sheet will be the same as the previous macro....just a count of how many times each name appeared.
If you have some time to have a look, I'd sure appreciate it.

Cheers,
Mike

 

[Eric W]

Try:

Sub CountNames2()
Dim d As Object, s As Worksheet, ar1 As Variant, i As Long, c As Long
Dim d1 As Variant, d2 As Variant, op() As Variant, lr As Long

    Set d = CreateObject("Scripting.Dictionary")
    For Each s In Worksheets
        If LCase(s.Name) Like "day *" Then
            For c = 2 To 5
                lr = s.Cells(Rows.Count, c).End(xlUp).Row
                Select Case lr
                    Case 1, 2
                    Case 3
                        ar1 = s.Range(s.Cells(3, c), s.Cells(lr, c)).Value
                        d(LCase(Trim(ar1))) = d(LCase(Trim(ar1))) + 1
                    Case Else
                        ar1 = s.Range(s.Cells(3, c), s.Cells(lr, c)).Value
                        For i = 1 To UBound(ar1)
                            d(LCase(Trim(ar1(i, 1)))) = d(LCase(Trim(ar1(i, 1)))) + 1
                        Next i
                End Select
            Next c
        End If
    Next s
   
    ReDim op(1 To d.Count, 1 To 2)
    d1 = d.keys
    d2 = d.items
    For i = 0 To d.Count - 1
        op(i + 1, 1) = WorksheetFunction.Proper(d1(i))
        op(i + 1, 2) = d2(i)
    Next i
    Sheets("Summary").Range("B2:C2") = Array("Name", "Count")
    Sheets("Summary").Range("B3").Resize(d.Count, 2) = op
   
End Sub

In my testing, I ran into cases where it counted the same name as different, just because of a stray space, or capitalization difference. So I added the parts in red to handle that. If that's not an issue for you, or if you don't like the way Proper capitalizes the results, then just remove the characters in red.

 

[underpar]

In my testing, I ran into cases where it counted the same name as different, just because of a stray space, or capitalization difference

Hi Eric:
Once again, I have to say WOW and thank you. It's perfect!!!
Cheers and all the best,
Mike

 

[Eric W]

?