HELP Roll Up Sheet showing only repeated data across 12 sheets

[nicolews]

Hello and Help Me Please

I have a current excel workbook that has a sheet for each month of the year. On each sheet I list branches (I'm a bank manager) that have had violation of some sort. What I need to create is a seperate Roll Up sheet that shows only branches that have had more than one violation in the year (meaning their branch name is present on more than one worksheet). Is this possible? I've watched a lot of videos about moving data to a summary page- and that I can do if the data is constant across all sheets. What I'm needing help with is only seeing the data that repeats (if it does).

*Each month may have totally different branches listed. I basically need to see the "repeat offenders" on one page. Can this be done? Help! I have a deadline to meet of 3 days.

 

[nicolews]

Hi- so sorry for the delay in response. Is this the info you needed:?
* Each Sheet has month name (January 2015, February 2015, etc.)
* Yes your assumption is correct, if the banking center does not have a violation, they are not listed on the spreadsheet
* My "Test" sheet (which I would like to be named Repeat Occurs) has all the same column descriptions as the monthly sheets
Column - Description
A - work date
B - reference number
C - banking center number
D - Market
E - Region
F - Teller #/ID #
G - First Occurence
H - Second Occurence
I - Late Filed: See Comments
J - Other: See Comments
K - Total Errors
L - Comments

 

[JLGWhiz]

This is the one that counts.
You will need to Add a sheet named "Repeat Occurs". You can rename sheets("Test") to Sheets("Key Index") and continue to list all of your branches in column A, the same as they are identified in column C of the monthly sheets, beginning in A1. Since you intend to use this procedure on a recurring basis, it is better to keep the control sheet separate from the output sheet. You can add to or delete items from the Key Index of branch ID numbers without any problem, so long as you do not leave blank spaces. When you delete an item use the delete, shift up, method to avoid leaving blank cells. The sheet name and columns for the branch Id are reflected in the code, so they must be made to your file before running the code to avoid an abort.

Sub repeats4()
Dim sh As Worksheet, x As Long, viol() As Variant, fn As Range, sp As Variant, i As Long, rw As Long, rng As Range
    For Each c In Sheets("Key Index").Range("A1", Sheets("Key Index").Cells(Rows.Count, 1).End(xlUp))
    x = 1
        For Each sh In ThisWorkbook.Sheets
            If sh.Name <> "Key Index" And sh.Name <> "Repeat Occurs" Then
                Set fn = sh.Range("C:C").Find(c.Value, , xlValues, xlWhole)
            If Not fn Is Nothing Then
                ReDim Preserve viol(1 To x)
                            viol(x) = sh.Name & "," & "A" & fn.Row & ":L" & fn.Row
                            x = x + 1
                    End If
            End If
        Next
        x = 0
    If Not IsEmpty(viol()) Then
        If UBound(viol) > 1 Then
            rw = Sheets("Repeat Occurs").Cells(Rows.Count, 1).End(xlUp).Row + 1
            For i = LBound(viol) To UBound(viol)
                sp = Split(viol(i), ",")
                Set rng = Sheets(sp(LBound(sp))).Range(sp(UBound(sp)))
                rng.Copy Sheets("Repeat Occurs").Cells(Rows.Count, 1).End(xlUp)(2)
            Next
        End If
    End If
    Next
End Sub

Once you have set up your sheets and run the code, if you have a problem or get an error message, please note the details in your response. ie. For an error message, click the 'Debug' button and note the line of code that is highlighted, then in your response, specify the error message content and the line of of code that was highlighted. This will speed up any troubleshooting action on this end.

 

[JLGWhiz]

It occured to me that repetitive use of the procedure would add the same data to the output sheet for many of the items, thus creating duplicate information unless you manually delete the old data before each use. So, this version will automatically clear the old data away and show only the current conditions for the output.

Sub repeats5()
Dim sh As Worksheet, x As Long, viol() As Variant, fn As Range, sp As Variant, i As Long, rw As Long, rng As Range
Sheets("Repeat Occurs").UsedRange.Offset(1, 0).ClearContents
    For Each c In Sheets("Key Index").Range("A1", Sheets("Key Index").Cells(Rows.Count, 1).End(xlUp))
    x = 1
        For Each sh In ThisWorkbook.Sheets
            If sh.Name <> "Key Index" And sh.Name <> "Repeat Occurs" Then
                Set fn = sh.Range("C:C").Find(c.Value, , xlValues, xlWhole)
            If Not fn Is Nothing Then
                ReDim Preserve viol(1 To x)
                            viol(x) = sh.Name & "," & "A" & fn.Row & ":L" & fn.Row
                            x = x + 1
                    End If
            End If
        Next
        x = 0
    If Not IsEmpty(viol()) Then
        If UBound(viol) > 1 Then
            rw = Sheets("Repeat Occurs").Cells(Rows.Count, 1).End(xlUp).Row + 1
            For i = LBound(viol) To UBound(viol)
                sp = Split(viol(i), ",")
                Set rng = Sheets(sp(LBound(sp))).Range(sp(UBound(sp)))
                rng.Copy Sheets("Repeat Occurs").Cells(Rows.Count, 1).End(xlUp)(2)
            Next
        End If
    End If
    Next
End Sub

 

[nicolews]

Wow! Your username is correct- you are a whiz! This is exactly what I wanted! I haven't gotten a chance to play around and test it with new input info- will do that tonight and get back with you if I have any issues.

Big Thank you, my friend! I appreciate your help!

 

[JLGWhiz]

Wow! Your username is correct- you are a whiz! This is exactly what I wanted! I haven't gotten a chance to play around and test it with new input info- will do that tonight and get back with you if I have any issues.

Big Thank you, my friend! I appreciate your help!

You're welcome, It would be better if you begin a new thread for any changes or enhancement. Just post the current code and explain what you need. Someone will pick up on it and assist.
Regards, JLG