Find reoccuring text string based off date & how many times it appears

[jamesraywebber]

I have a workbook with 7 worksheets (2 are data connections).
Worksheet = TSA Daily
Worksheet = TSA Support (Data Connection)

I need on TSA Daily in cell A23 to search through the Associate column on TSA Support on the date on TSA Daily cell B1 and if the associates name appears more than twice to put their name. I have the formula in B23 to count how many times that name appears.

This is Worksheet = TSA Support (Data Connection)

Date/Time	Time of Day	Team Support Associate	Customer Number	Associate
12/12/2017 10:00	Morning (8a - 130p)	Ashley Velez-Garcia	133,445,404	Kristen Cooper
12/12/2017 10:00	Morning (8a - 130p)	Ashley Velez-Garcia	133,605,163	Shaudia McCloud
12/12/2017 10:00	Morning (8a - 130p)	Ashley Velez-Garcia	132,793,214	Edouard Morcos
12/12/2017 10:00	Morning (8a - 130p)	Ashley Velez-Garcia	133,722,217	Travis Chase
12/12/2017 10:00	Morning (8a - 130p)	Ashley Velez-Garcia	120,000,000	Rick Poppe
12/12/2017 10:00	Morning (8a - 130p)	Ashley Velez-Garcia	134,286,159	Laura Robertson
12/12/2017 10:00	Morning (8a - 130p)	Ashley Velez-Garcia	133,887,853	Kathleen Pearson
12/11/2017 21:00	Evening (6p - 10p)	Kenya Brown	120,031,452	Mya Frazier
12/11/2017 21:00	Evening (6p - 10p)	Dialo Lee	134,560,721	Diane LHeureux
12/11/2017 20:00	Morning (8a - 130p)	Paul Toth	120,001,801	Roger Ogden
12/11/2017 20:00	Evening (6p - 10p)	Kenya Brown	133,551,836	Memory Johnson
12/11/2017 20:00	Evening (6p - 10p)	Kenya Brown	132,628,134	Marc Giordani

<tbody>
</tbody>

And this is my Worksheet = TSA Daily

Date	12/12/2017
Day	Tuesday
Time of Day	Total Service Calls Handled	Triages Tickets Created by TSA	Percentage of calls affected by Triage	Triages Tickets Created by ART	Triages Tciekts Created by Others	Total Triage Tickets Created	System/Citrix Issue - No Triage Ticket Created
8:00 - 9:00	139	1	0.72%	3	0	4	0
9:00 - 10:00	246	0	0.00%	19	0	19	0
10:00 - 11:00	315	0	0.00%	0	0	0	0
11:00 - 12:00	341	0	0.00%	0	0	0	0
12:00 - 1:00	343	0	0.00%	0	0	0	0
1:00 - 2:00	0	0	#DIV/0!	0	0	0	0
2:00 - 3:00	0	0	#DIV/0!	0	0	0	0
3:00 - 4:00	0	0	#DIV/0!	0	0	0	0
4:00 - 5:00	0	0	#DIV/0!	0	0	0	0
5:00 - 6:00	0	0	#DIV/0!	0	0	0	0
6:00 - 7:00	0	0	#DIV/0!	0	0	0	0
7:00 - 8:00	0	0	#DIV/0!	0	0	0	0
8:00 - 9:00	0	0	#DIV/0!	0	0	0	0
9:00 - 10:00	0	0	#DIV/0!	0	0	0	0
10:00 - 11:00	0	0	#DIV/0!	0	0	0	0
Current Total	1384	1	0.07%	22	0	23	0
RTA Support
Top Associates (Triage Tickets & System Issues)		Daily Observations
(Formula)	=Countif(Associate,A23)

<tbody>
</tbody>

 

[AFPathfinder]

So, you want it to generate a new table of the top associates with over 2 tickets or the single highest associate? If it's generating more than one associate, it will have to be a VBA solution. Otherwise, I think that can be accomplished with an IF and COUNTIFS function.

 

[jamesraywebber]

So, you want it to generate a new table of the top associates with over 2 tickets or the single highest associate? If it's generating more than one associate, it will have to be a VBA solution. Otherwise, I think that can be accomplished with an IF and COUNTIFS function.

Correct, I need for it to create a table on worksheet TSA Daily in Cell A23 for all the associate that appear more than twice within the date that is on TSA Daily Cell B1! VBA or formula will be fine, I just need guidance!

 

[AFPathfinder]

MrExcel MVP Fluff gave me some new tools to learn in VBA and I thought I'd try using them in your solution. This macro should do as you request with the exception of the count. I have it pasting the count for each associate in column B when it adds the associate names. I figure this would be best as you won't know how many names will be posted at each run of the macro. If you would like to change it, I'll highlight the syntax that needs changed in red.

Sub AssociateRank()
    Dim i As Integer
    Dim n As Integer
    Dim k As Variant
    Dim finalRow As Integer
    Dim oDict As New Scripting.Dictionary
    Dim oDate As Date
    
    oDate = Sheets("TSA Daily").Range("B1").Value
    
    Sheets("TSA Support").Activate
    finalRow = Cells(Rows.Count, 1).End(xlUp).Row
    
    'Loop through the TSA Support sheet and add all associates and their row counts for the date in B1 on the TSA Daily sheet.
    For i = 2 To finalRow
        If DateSerial(Year(Cells(i, 1).Value), Month(Cells(i, 1).Value), Day(Cells(i, 1).Value)) = oDate Then
            oDict(Cells(i, 3).Value) = oDict(Cells(i, 3).Value) + 1
        End If
    Next i
    
    Sheets("TSA Daily").Activate
    n = 23
    
    'If dictionary is empty, exit sub.
    If oDict.Count = 0 Then
        MsgBox "No associate entries for " & oDate & " were found."
        Exit Sub
        
    'Remove old data from the fill range.
    Else
        Range("A23:[COLOR=#ff0000][B]B[/B][/COLOR]" & Cells(Rows.Count, 1).End(xlUp).Row).ClearContents
    End If
    
    'Loop through and add keys and items to the TSA Daily sheet if count is at least 2.
    For Each k In oDict.Keys
        If oDict(k) >= 2 Then
            Cells(n, 1).Value = k
            [COLOR=#ff0000][B]Cells(n, 2).Value = oDict(k)[/B][/COLOR]
            n = n + 1
        End If
    Next k
    
    finalRow = Cells(Rows.Count, 1).End(xlUp).Row
    
    'If at least one key was added to the sheet, sort highest to lowest and inform user of macro completion.
    If finalRow > 22 Then
        Range("A23:B" & finalRow).Sort Range("A23:B" & finalRow).Columns("B"), xlDescending
        MsgBox "Done."
    End If
End Sub

The macro will remove old data sitting in the A23 area before posting the new data, so you should be able to rerun this when you wish.

EDIT: Make sure to have the "Microsoft Scripting Runtime" reference enabled to run this VBA.

 

[jamesraywebber]

The only problem is that its looking through the entire page to find who's name appears the most. I just it to look in column E to see who appears more than twice. When I ran that code it gives me the people that appear only in column C which is the person that helped the one in column E. Any help?

 

[jamesraywebber]

Ok works like a charm!!! Thank you so much, I have 2 more issues I am working on, not sure if you may be able to help.

[h=2]Running totals based off monthly[/h]

In my workbook I have a sheet named "TSA Dashboard"

In Cells C4:J10 it the total for our team. In Cells A13:J49 is each persons individual stats for the week. The dates automatically change based on the date. In Cell L1:R11, I have a month to date chart. I would like for at the end of the week, it will add all of the weekly data to the totals in L1:R11. If I need a button to do that I can but it is a running total till the end of the month, then i want it to reset for the next month! Is this possible?​

 

[jamesraywebber]

Works perfectly! Another problem I need resolved is I am trying to copy frozen panes into an email. I have this code but it does not copy the correct column:

<code style="margin: 0px; padding: 0px; font-style: inherit; font-weight: inherit; line-height: 12px;">Sheets("TSA Schedule").Activate
Set rng = Union(Range(Cells(1, 1), Cells(Rows.Count, 1).End(xlUp)), Range(Cells(1, ActiveWindow.Panes(2).VisibleRange.Columns(1).Column), Cells(Cells(Rows.Count, 1).End(xlUp).Row, ActiveWindow.Panes(2).VisibleRange.Columns(1).Column)))</code>


Does it matter that I use a code to hide the cells based off the value in cell A28? This the code that I am using

Private Sub Worksheet_Change(ByVal Target As Range)
'Updateby Extendoffice 20160725
    Dim xCell As Range
    If Target.Address <> Range("A28").Address Then Exit Sub
    Application.ScreenUpdating = False
    For Each xCell In Range("B13:NC13")
        xCell.EntireColumn.Hidden = (xCell.Value < Target.Value)
    Next
    Application.ScreenUpdating = True
End Sub

 

[AFPathfinder]

The only problem is that its looking through the entire page to find who's name appears the most. I just it to look in column E to see who appears more than twice. When I ran that code it gives me the people that appear only in column C which is the person that helped the one in column E. Any help?

You just need to swap out the column "3" references for column "5" in the first loop:

    For i = 2 To finalRow
        If DateSerial(Year(Cells(i, 1).Value), Month(Cells(i, 1).Value), Day(Cells(i, 1).Value)) = oDate Then
            oDict(Cells(i, [COLOR=#0000ff][B]5[/B][/COLOR]).Value) = oDict(Cells(i, [COLOR=#0000ff][B]5[/B][/COLOR]).Value) + 1
        End If
    Next i

Running totals based off monthly

In my workbook I have a sheet named "TSA Dashboard"


In Cells C4:J10 it the total for our team. In Cells A13:J49 is each persons individual stats for the week. The dates automatically change based on the date. In Cell L1:R11, I have a month to date chart. I would like for at the end of the week, it will add all of the weekly data to the totals in L1:R11. If I need a button to do that I can but it is a running total till the end of the month, then i want it to reset for the next month! Is this possible?​

It definitely sounds doable, but there is a lot of specifics that would need identified before a macro can be made. You are mentioning 3 ranges with multiple rows and columns, but I'd need to know what and how it is all to be summarized. The macro can be built under the Workbooks_Open event and a date can be kept on the summarized table; if the date listed is > 6 days when subtracted from today, the macro can add or replace the data in the summary table. It's probably best to start a new thread for that one, though.

Works perfectly! Another problem I need resolved is I am trying to copy frozen panes into an email. I have this code but it does not copy the correct column:

[B]<code style="margin: 0px; padding: 0px; font-style: inherit; font-weight: inherit; line-height: 12px;">Sheets("TSA Schedule").Activate
Set rng = Union(Range(Cells(1, 1), Cells(Rows.Count, 1).End(xlUp)), Range(Cells(1, ActiveWindow.Panes(2).VisibleRange.Columns(1).Column), Cells(Cells(Rows.Count, 1).End(xlUp).Row, ActiveWindow.Panes(2).VisibleRange.Columns(1).Column</code>[/B]

Does it matter that I use a code to hide the cells based off the value in cell A28? This the code that I am using

When it comes to putting a table into an email, I'm less experienced. I could take a stab at it, but I'm sure there would be lots of testing and debugging to get it working. The same would go with hidden ranges; I'm not sure how Union and Panes would fair in that task. Again, probably best to create a new thread for that.

 

[jamesraywebber]

That works great, but I also need it to sort out of those on that date the ones that has "Triage Created" & System Issue in column K on Sheet "TSA Support".