+ Reply to Thread
Results 1 to 4 of 4

remove duplicate in one sheet by comparing to other

Hybrid View

  1. #1
    Registered User
    Join Date
    03-07-2013
    Location
    zurich
    MS-Off Ver
    Excel 2010
    Posts
    99

    remove duplicate in one sheet by comparing to other

    Hello,

    I got the solution from this forum to remove duplicate in child sheet if the records for columns A, H and S are exactly matching with the columns A, H and S of Master sheet.

    When I used it in some of my other process, it is not working. can anyone help me.

    Advance thanks

    Option Explicit
    
    Sub RemoveMatchesToMasterOnManySheets()
    Dim LR As Long, ws As Worksheet
        
    With Sheets("master")
        LR = .Range("A" & .Rows.Count).End(xlUp).Row
        .Range("AI2:AI" & LR).FormulaR1C1 = "=RC2&""-""&RC8&""-""&RC19"
    End With
        
    For Each ws In Worksheets
        If ws.Name <> "master" And ws.Range("A1").Value = "Responsibility" Then
            With ws
                .AutoFilterMode = False
                .Range("AI1") = "key"
                LR = .Range("A" & .Rows.Count).End(xlUp).Row
                .Range("AI2:AI" & LR).FormulaR1C1 = "=ISNUMBER(MATCH(RC2&""-""&RC8&""-""&RC19, master!C10, 0))"
                .Range("AI:AI").AutoFilter Field:=1, Criteria1:="TRUE"
                LR = .Range("A" & .Rows.Count).End(xlUp).Row
                If LR > 1 Then .Range("A2:A" & LR).EntireRow.Delete xlShiftUp
                .AutoFilterMode = False
                .Range("AI:AI").ClearContents
                .Range("A1").AutoFilter
            End With
        End If
    Next ws
    
    Sheets("master").Range("AI:AI").ClearContents
    End Sub
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    07-15-2012
    Location
    Leghorn, Italy
    MS-Off Ver
    Excel 2010
    Posts
    3,431

    Re: remove duplicate in one sheet by comparing to other

        If ws.Name <> "master" And ws.Range("A1").Value = "EMPID" Then
    in your attached file ther'is in A1 the word Responsibility
    If solved remember to mark Thread as solved

  3. #3
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: remove duplicate in one sheet by comparing to other

    Also, your formulas being inserted are actually checking columns B, H and S, not A, H and S. RC2 means column 2.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  4. #4
    Registered User
    Join Date
    03-07-2013
    Location
    zurich
    MS-Off Ver
    Excel 2010
    Posts
    99

    Re: remove duplicate in one sheet by comparing to other

    Thank you very much

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] remove duplicate in one sheet by comparing to other
    By abraham30 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-15-2014, 12:43 AM
  2. [SOLVED] Find Duplicates and remove the duplicate entry comparing two columns
    By retrospikz in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 04-04-2013, 03:17 AM
  3. Remove Duplicate Information After Comparing 2 Worksheets
    By CatExcel in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-03-2012, 06:03 PM
  4. remove a duplicate value and add in next sheet
    By raviexcel in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 11-23-2010, 02:46 PM
  5. comparing lists of data to remove duplicate data
    By Tom in forum Excel General
    Replies: 2
    Last Post: 10-13-2005, 02:05 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1