Results 1 to 9 of 9
  1. #1
    Lounger
    Join Date
    Jul 2006
    Location
    N. Highlands, California, USA
    Posts
    43
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Pulling duplicate data onto one worksheet (Excel 2003)

    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 <img src=/S/confused.gif border=0 alt=confused width=15 height=20>

  2. #2
    2 Star Lounger
    Join Date
    Mar 2004
    Location
    Cincinnati, Ohio, USA
    Posts
    102
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pulling duplicate data onto one worksheet (Excel 2003)

    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

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Pulling duplicate data onto one worksheet (Excel 2003)

    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

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pulling duplicate data onto one worksheet (Excel 2003)

    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.
    Legare Coleman

  5. #5
    Lounger
    Join Date
    Jul 2006
    Location
    N. Highlands, California, USA
    Posts
    43
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pulling duplicate data onto one worksheet (Excel 2003)

    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

  6. #6
    2 Star Lounger
    Join Date
    Mar 2004
    Location
    Cincinnati, Ohio, USA
    Posts
    102
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pulling duplicate data onto one worksheet (Excel 2003)

    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.

    <pre>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
    </pre>


  7. #7
    Lounger
    Join Date
    Jul 2006
    Location
    N. Highlands, California, USA
    Posts
    43
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pulling duplicate data onto one worksheet (Excel 2003)

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

    Thank you,
    SME

  8. #8
    2 Star Lounger
    Join Date
    Mar 2004
    Location
    Cincinnati, Ohio, USA
    Posts
    102
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pulling duplicate data onto one worksheet (Exc

    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

  9. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Pulling duplicate data onto one worksheet (Exc

    <img src=/S/whisper.gif border=0 alt=whisper width=29 height=17>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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •