Pulling duplicate data onto one worksheet (Excel 2003)

Topic

New Reply

Good Morning,

I have a workbook with about 12 worksheets so far. Each worksheet contains 4 columns. The columns are Name, Date, Time, Reason. I would like to have a macro that can pull all records with duplicate names and place the data onto another worksheet within the workbook. Every Friday a new tab is created and new data entered, is there a way I could also have to macro automatically include any duplicates from the new worksheets?

Thank you,
SME

Reply | Quote

Replies

The only criteria for duplication is Name?

When a new tab/worksheet is entered, does an old tab/worksheet get deleted? From your “so far” comment, I think not.

If a duplicate is found in Worksheet1 and Worksheet10, should it be reported? Or are you only concerned with the freshly added worksheet?

How do you plan to identify or differentiate the new worksheet from the older worksheets?

-Mike

Reply | Quote

Thanks for responding. The Name field is the only data that would be the same, all the other fields change. So I guess I would need a macro that could identify duplicite names. I would like the original and all duplicates (from all the worksheets) pulled onto one worksheet. You are correct; I keep all the old tabs/worksheets. The new tabs are named after the week which all the data is from (week 9.11.06, week 9.18.06…). I suspect that I would have to update the maco and add in the name of the new worksheet each time I create on. That is not a problem. I attached an example of what my workbook looks like.

Thanks,
SME

Reply | Quote

SME:

You seem concerned with duplicates if separate worksheets, but there very well may be folks who forget their badge multiple times in the same week. Note: This glasshouse dweller has nothing in his hands…really).

The attached workbook contains a routine named GetDuplicateFolks. Remember to enable macros when opening the workbook.

The routine traverses through the workbooks, carefully bypassing the Summary sheet. It notes each person and the date of their infraction, and stores this data in an array. After the data is collection, the people who have forgotten their badge more than once are reported on the Summary sheet.

Possible areas to improve: delete the summary page before retrieving the duplicate data.

Public Sub GetDuplicateFolks()
On Error GoTo Err_GetDuplicateFolks

   Dim i As Integer, j As Integer
   Dim MyData() As ForgetfulFolk
   Dim lIndex As Long
   Dim bSummarySheetExists As Boolean
   Dim sName As String, sDateOfInfraction As String
   
   With Application
      For i = 1 To .ActiveWorkbook.Worksheets.Count
         If .Sheets(i).Name  SUMMARY_SHEET_NAME Then
            .Sheets(i).Select
            .Range("A3").Select
            j = 0
            Do Until IsEmpty(.ActiveCell.Offset(j, 0).Value)
               sName = VBA.Trim(.ActiveCell.Offset(j, 0).Value)
               sDateOfInfraction = .ActiveCell.Offset(j, 1).Value
               Call AppendToArray(MyData(), lIndex, sName, sDateOfInfraction)
               j = j + 1
            Loop
         End If
      Next i
   End With
   
   If bSummarySheetExists = False Then Call CreateSummarySheet
   Call DumpSummaryData(MyData(), lIndex)
   
Exit_GetDuplicateFolks:
   Exit Sub
   
Err_GetDuplicateFolks:
   Call ErrHandler("GetDuplicateFolks routine", Err.Number, Err.Description)
   Resume Exit_GetDuplicateFolks
   
End Sub

Reply | Quote

Thank you for your help everyone and Mike thank you for the Macro. Have a great weekend!

Thank you,
SME

Reply | Quote

SME:

You’re welcome, but before we close the book on this issue, consider the following.

Adding new worksheets for every week is going to get cumbersome. There is a maximum number of worksheets, I am guessing 256 or so.

As an alternative, you could put all your data on one worksheet. Hopefully by the time the 65535th forgotten badge incident happens, it will no longer be your problem. With this method you can create a pivot table to count occurrences by people.

-Mike

Reply | Quote

The maximum number of worksheets in a default workbook is 255. The maximum number of worksheets in a workbook is limited only by memory. I agree though that too many would be cumbersome and impractical.

I would recommend combining them and adding another column (for week). This would allow creation of an individual “week” directly via filtering.

Steve

Reply | Quote

From your basic description, yes it seems possible for a macro to do what you request.

If you want help with the macro, you will need to provide more specifici questions so we can provide details. If you need more detailed information you will have to elaborate on how you are setup and what you want the end result to be.

It may be useful to provide and sample workbook of what you have and then a sample output of what you want the results to be after the macro is run.

A general approach would be to first combine all the sheets into a temp worksheet, then extract out the duplicates. Adv filter can be used to extract out the unique items directly (and can be called in code without doing too much “heavy lifting”).

Steve

Reply | Quote

Your description of your problem leaves many questions. For example, what constitutes a duplicate name. Are John Smith and J. Smith duplicates? Could you provide a sample workbook that shows what your data looks like and that could be used for testing. Also, a sheet that show what you want the result of the macro to be.

Reply | Quote