1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Condensing multiple sheets in Excel into one for data duplicated across all s

Discussion in 'Business Applications' started by exerguy, Jan 7, 2009.

Thread Status:
Not open for further replies.
Advertisement
  1. exerguy

    exerguy Thread Starter

    Joined:
    Nov 10, 2008
    Messages:
    21
    I have three sheets of data that I want to combine into one based off of criteria in column "A", however I only want the data that is duplicated across each sheet (ie if the three sheet have the name "Bob" in column A, I want the name "Bob" to be in the new list). Some of the sheets may contain duplicated data within itself (ie. the name "John" may be on three different rows in one sheet.) I only want to keep the names that show up on all three sheets, not just multiple times within one sheet.

    Any suggestions would be great.
     
  2. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    I am assuming that the 3 sheets are identical in layout and you want the new sheet to match that layout?
    If so, it would be most helpful if you could post your current file - put in dummy data if necessary.
     
  3. exerguy

    exerguy Thread Starter

    Joined:
    Nov 10, 2008
    Messages:
    21
    OKay. I have been trying to use the macro "thisworkbook.singlelist" to pull the data from the sheets WControl, Aerobic and Strength and to create the new list of only the names that appear on all 3 sheets. This new list should appear on the consults page. I have it set to copy into column D. I've been playing with it a bit, and now besides not doing what i want it to, its giving me an error 400.
     

    Attached Files:

  4. turbodante

    turbodante

    Joined:
    Dec 19, 2008
    Messages:
    744
    Talking about having more than one way of flogging equine :eek:, that's one mean way of going round the houses dude;)...

    If you replace everything after your

    Code:
      Application.Run "'Book1.xls'!ThisWorkbook.RemoveDuplicates"
    with this

    Code:
        Sheets("Raw List").Select
        Range("A1").Select
        Range(Selection, Selection.End(xlDown)).Select
        Selection.Copy
        Sheets("Consults").Select
        Range("A1").Select
        ActiveSheet.Paste
    You should be good to go.
     
  5. exerguy

    exerguy Thread Starter

    Joined:
    Nov 10, 2008
    Messages:
    21
    Turbodante,

    That fixed the error 400, but I still get names that are dupliated within each list instead of just across all three sheets. I still need to have each of the original sheets intact. If I have each sheet copied to a separate sheet and then run the macro to remove duplicates on each new sheet prior to creating the final list, and then create the final list that should do it right? It sounds a little convoluted, but is there a simpler way to do this?

    I got the jokes with the smilies.
     
  6. turbodante

    turbodante

    Joined:
    Dec 19, 2008
    Messages:
    744
    Strange that.

    Just to clarify what’s happening in your macro.

    It copies the names from sheets [WControl],[ Strength] & [Aerobic]to [Raw List], from which it goes through the list and deletes unique entries, then it goes through again and deletes those that appear twice.

    The next significant thing it does, is to work upwards comparing with the cell above, deleting if there’s match. The only problem I see with this method is the list hadn’t been sorted; alas you jump to another routine which does the same thing but this time sorting the data before hand to cover my concern!

    Then the bit that I suggested in my earlier post just copies the remaining list to the sheets - [consults].

    Like the saying about horses, there are many ways... But if I'm not understanding the problem right- we can have another look.

    ;)I'm new to this forum too, and just getting the hang of all the different buttons and stuff.
     
  7. exerguy

    exerguy Thread Starter

    Joined:
    Nov 10, 2008
    Messages:
    21
    Your analysis is correct. It was working fine until I realized that it was possible for each sheet to have multiple entries with the same name (possible different data in the rest of the row) in Column B. It was giving me lists of names that didn't necessarily show up on all three sheets. So now I am trying to fix that, by having it copy each list of names to a separate sheet and remove the duplicates prior to combingin them all into one list, but everytime I make a change I get an error 400. I've tried an error trap, but that is not catching the error.
     
  8. turbodante

    turbodante

    Joined:
    Dec 19, 2008
    Messages:
    744
    Just to clarify. You need to check in each sheet for duplicates. Remove these for each sheet. Then consolidate all the names in the three sheets.

    At this point, do you still want to remove unique and duplicates and keeping only names if they appear three or more times?
     
  9. exerguy

    exerguy Thread Starter

    Joined:
    Nov 10, 2008
    Messages:
    21
    Exactly. All I want to know is the names that appear on all three sheets.
     
  10. turbodante

    turbodante

    Joined:
    Dec 19, 2008
    Messages:
    744

    Okay. Not really the most efficient way of doing things, but here's a rehash of your code...

    Code:
    Sub singleListRehashed()
    Dim iRow, jRow As Integer
    Application.ScreenUpdating = False
    
    Sheets("Consults").Cells.ClearContents
    Sheets("Raw List").Select
        Cells.Select
            Selection.ClearContents
            Range("A1").Select
    'creates raw list of names with duplicates
        Sheets("Aerobic").Range("B1:B900").Copy Sheets("Raw List").Range("A1")
        Range("A65536").End(xlUp).Offset(0, 1).Select
        Range(Selection, Selection.End(xlUp)).Select
        Selection = "Aerobic"
        
        Sheets("Strength").Range("B1:B900").Copy Sheets("Raw List").Range("A" & Rows.Count).End(xlUp).Offset(1)
        Range("A65536").End(xlUp).Offset(0, 1).Select
        Range(Selection, Selection.End(xlUp).Offset(1, 0)).Select
        Selection = "Strength"
        
        Sheets("Wcontrol").Range("B1:B900").Copy Sheets("Raw List").Range("A" & Rows.Count).End(xlUp).Offset(1)
        Range("A65536").End(xlUp).Offset(0, 1).Select
        Range(Selection, Selection.End(xlUp).Offset(1, 0)).Select
        Selection = "Wcontrol"
        
       'Removes all Duplicate names (Of each sheet)
        Sheets("Raw List").Select
        Range("A65536").End(xlUp).Offset(0, 2).Select
        Range(Selection, Selection.End(xlUp)).Select
        Selection.FormulaR1C1 = "=RC[-2]&RC[-1]"
        
        Range("A65536").End(xlUp).Offset(0, 3).Select
        Range(Selection, Selection.End(xlUp)).Select
    
        'Removes Duplicate Entries (of eachsheet)
        Cells.Sort Key1:=Range("C1")
        totalrows = Range("A1").CurrentRegion.Rows.Count
        For Row = totalrows To 2 Step -1
        If Cells(Row, 3).Value = Cells(Row - 1, 3).Value Then
        Rows(Row).Delete
        End If
        Next Row
        'Keep only Names that appear 3times over three sheets
        Dim RawRangeRows As Integer
        Columns(2).Delete
        Columns(2).Delete
    
    
        RawRangeRows = Range("a1").CurrentRegion.Rows.Count
        
        Range("B1:B" & RawRangeRows).FormulaR1C1 = "=COUNTIF(R1C1:R" & RawRangeRows & "C1,RC[-1])"
    
        Rows(1).EntireRow.Insert
        Range("A1").FormulaR1C1 = "A"
        Range("B1").FormulaR1C1 = "B"
        Range("D1").FormulaR1C1 = "B"
        Range("D2").FormulaR1C1 = "3"
        
       
        Range("A1:B" & RawRangeRows).AdvancedFilter _
        Action:=xlFilterCopy, CriteriaRange:=Range( _
            "D1:D2"), CopyToRange:=Range("F1"), Unique:=True
        
        Range("F2").Select
        Range(Selection, Selection.End(xlDown)).Select
        Selection.Copy
    
        Sheets("Consults").Select
        Range("A1").Select
        ActiveSheet.Paste
    End Sub
    
    Perhaps a better way of doing this would be to create a table (perhaps a pivot table) to analyse the three sheets.
     
  11. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    Probably a pivot table of each page individually, then combining the resulting names and a pivot table of those. Any with a count >1 would be the ones you want.
     
  12. exerguy

    exerguy Thread Starter

    Joined:
    Nov 10, 2008
    Messages:
    21
    The pivot table ended up being a simpler method of doing of accomplishing this. I had each of the three sets go to a pivot table and then I combined all three pivot tables into a fourth table. The macro I wrote then used the data in the final pivot table. Thanks for all the help and suggestions. I know I wouldn't have thought to use a pivot table.
     
  13. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    Great! Please use the button at the top of the page to mark this thread as Solved, then. :)
     
  14. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/787886

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice