Page 1 of 2 1 2 LastLast
Results 1 to 10 of 17

Thread: A problem with duplication in Excel's macro

  1. #1

    A problem with duplication in Excel's macro



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hello and thank you for letting me into this forum!

    I have a code that I just recently had modified and it looks like this:


    Code:
     
    Sub FindDuplicates2() 'matches against 2 cols
      Dim rng1 As Range
      Dim rng2 As Range
      Dim bMatch As Boolean
      Dim origRng As Range
      Dim compRng As Range
       
      On Error Resume Next
      Set origRng = Application.InputBox("Choose the first range", "Range 1", Type:=8)
          If origRng Is Nothing Then Exit Sub
      Set compRng = Application.InputBox("Choose the second range", "Range 2", Type:=8)
      'matches first cell in first range against each cell in second range
      'ranges do not need to be equal size
      'if there is a match then cell in second range turns green
      'if there is not a  match then cell in first range turns red
          For Each rng1 In origRng
              bMatch = False
              For Each rng2 In compRng
                  If rng2.Text Like "*" & rng1.Text & "*" Then
                      bMatch = True
                      rng2.Interior.ColorIndex = 0
                          If rng2.Offset(0, -4).Value <> rng1.Offset(0, -4).Value Then
                              rng1.Offset(0, -4).Interior.ColorIndex = 3
                          
      End If
                  End If
              Next rng2
                  If bMatch = False Then
                      rng1.Interior.ColorIndex = 41
                  End If
          Next rng1
           For Each rng1 In origRng
              bMatch = False
              For Each rng2 In compRng
                  If rng2.Text Like "*" & rng1.Text & "*" Then
                      bMatch = True
                      rng2.Interior.ColorIndex = 0
                          If rng2.Offset(0, -5).Value <> rng1.Offset(0, -5).Value Then
                              rng1.Offset(0, -5).Interior.ColorIndex = 3
                          End If
                  End If
              Next rng2
                  If bMatch = False Then
                      rng1.Interior.ColorIndex = 41
                  End If
          Next rng1
           For Each rng1 In origRng
              bMatch = False
              For Each rng2 In compRng
                  If rng2.Text Like "*" & rng1.Text & "*" Then
                      bMatch = True
                      rng2.Interior.ColorIndex = 0
                          If rng2.Offset(0, -3).Value <> rng1.Offset(0, -3).Value Then
                              rng1.Offset(0, -3).Interior.ColorIndex = 3
                          End If
                  End If
              Next rng2
                  If bMatch = False Then
                      rng1.Interior.ColorIndex = 41
                  End If
          Next rng1
           For Each rng1 In origRng
              bMatch = False
              For Each rng2 In compRng
                  If rng2.Text Like "*" & rng1.Text & "*" Then
                      bMatch = True
                      rng2.Interior.ColorIndex = 0
                          If rng2.Offset(0, -1).Value <> rng1.Offset(0, -1).Value Then
                              rng1.Offset(0, -1).Interior.ColorIndex = 3
                          End If
                  End If
              Next rng2
                  If bMatch = False Then
                      rng1.Interior.ColorIndex = 41
                  End If
          Next rng1
          
      End Sub
    Now here's what I keep seeing when I check the numbers I want to check: Click image for larger version. 

Name:	image1.jpg 
Views:	20 
Size:	49.0 KB 
ID:	135

    Now as you'll notice from the graphic above, the name Vicki Sikkink is highlighted red. The code posted above does that to show me that something is not matching with the other sheet... in this case, the name. All names with this code are compared through the phone numbers as their starting point.

    Now here's the problem... this second sheet here is what it compared Vicki to:
    Click image for larger version. 

Name:	Image2.jpg 
Views:	15 
Size:	53.5 KB 
ID:	136

    As you can see from the first name on this sheet, the business "Intensive Hair Unit", it has the exact same phone number as Vicki (hence, this is her business). I didn't want the red highlight to highlight Vicki's name since as you can see from the bottom of the sheet, she is right down there also where her name matches what was on the first sheet (image1). What's happening is, this excel macro is assuming that since there is so much as one difference on that sheet with that number, the whole thing is wrong... where it doesn't even consider the fact that there is one that matches exactly.

    How do I modify this code so that if it sees so much as one perfect match, then there will be no red highlight? It can disregard all the other differences if one matches. I'd like to add whatever the answer is to this to Leith's code that he gave me here too!

    Thank you in advance!
    JB

  2. #2
    Super Moderator Zack Barresse's Avatar
    Join Date
    Mar 2011
    Location
    Oregon, United States
    Posts
    106
    Articles
    0
    Hi JB,

    I'm not sure I would want to modify the code, as I'm not sure you need so many loops in there. If this is a previous thread, can you point us in that direction so we're not double working this? From the sounds of it, and not knowing your data structure, I would recommend maybe a helper column, or at least an array of data to go from, then compare that one time, since you want to take the totality of the data, not cell-by-cell.
    Regards,
    Zack Barresse

  3. #3
    Hello,

    Sorry, this isn't from an earlier thread on this website. I guess I should have mentioned that.

    Yeah, I was wondering why this happens... it assumes that if two or more listings on the sheet do not match the first listing on the sheet number one where the phone numbers match, it figures the whole thing is wrong. All I need it to do is match one listing's data when the phone numbers match and that will suffice.

    Thanks again!
    JB

  4. #4
    Neophyte vasantjob's Avatar
    Join Date
    Jun 2011
    Location
    Bangalore
    Posts
    1
    Articles
    0

    A problem with duplication in Excel's macro

    Is it possible to upload the file ?

    Quote Originally Posted by jonathynblythe View Post
    Hello,

    Sorry, this isn't from an earlier thread on this website. I guess I should have mentioned that.

    Yeah, I was wondering why this happens... it assumes that if two or more listings on the sheet do not match the first listing on the sheet number one where the phone numbers match, it figures the whole thing is wrong. All I need it to do is match one listing's data when the phone numbers match and that will suffice.

    Thanks again!
    JB

  5. #5
    Super Moderator Zack Barresse's Avatar
    Join Date
    Mar 2011
    Location
    Oregon, United States
    Posts
    106
    Articles
    0
    It's hard to say what is what when we don't know what columns/rows of data you're looking at. You haven't given any kind of correlation to the pictures and the code supplied. I'm not really sure what is what range and what you're pointing us to. I understand your code, which is checking cell by cell, and is not doing any kind of overall check. If you can't upload a file, as vasantjob asked, at least describe (in detail) your worksheet ranges, as well as what ranges you're selecting for your original and comparative ranges.

    Also, why do you need to select the ranges? Are they going to move? Are you going to only want to do portions a a time? Why not just keep it dynamic in the code and make the code find the ranges for you?
    Regards,
    Zack Barresse

  6. #6
    Hello guys, sorry I've been away but there was sort of an emergency for past week...

    Unfortunately I'm not going to be allowed to upload an excel document with that data since it is sensitive information.

    Also, why do you need to select the ranges? Are they going to move? Are you going to only want to do portions a a time? Why not just keep it dynamic in the code and make the code find the ranges for you?
    Yes I do need to select ranges, they don't move but I don't need the whole document scanned - it has usally more than 45000 rows and that will take hours for data to be scanned that will not be needed on the sheet.

    My question is, is there a way for the code (that I posted above) to ignore the address, name, zip code and city if so much as one phone number matches on both sheets where one phone number listing already matches one set of data with the other? If this makes sense?

  7. #7
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,195
    Articles
    57
    Blog Entries
    14
    Quote Originally Posted by jonathynblythe View Post
    Unfortunately I'm not going to be allowed to upload an excel document with that data since it is sensitive information.
    Hi Jonathyn,

    We're not actually after the data, what we're after is seeing the structure and patterns that it follows. If you can take your data and mock up something that fits the same patterns, it's all good for us. Start the first phone number at (123) 456-7890 and run it up 1 for each line from there. Find a list of baby names for the names in your file. Do whatever you have to do to the rest to make it random. (I'm sure 50 rows of data would be more than enough.) The key is that you should be able to demonstrate the issue with the data that you give us, then I'm sure things can be worked out.
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  8. #8
    Sorry for the delay again...

    Here you go:

    example.xlsx

  9. #9
    Administrator Simon Lloyd's Avatar
    Join Date
    Apr 2011
    Location
    Manchester, England
    Posts
    324
    Articles
    0
    You can give this a try if you wish although im not sure i grasped what it is you really want
    Code:
    'courtesy of Chip Pearson
    Function FindAll(SearchRange As Range, _
                    FindWhat As Variant, _
                    Optional LookIn As XlFindLookIn = xlValues, _
                    Optional LookAt As XlLookAt = xlWhole, _
                    Optional SearchOrder As XlSearchOrder = xlByRows, _
                    Optional MatchCase As Boolean = False, _
                    Optional BeginsWith As String = vbNullString, _
                    Optional EndsWith As String = vbNullString, _
                    Optional BeginEndCompare As VbCompareMethod = vbTextCompare) As Range
                
    End Function
    Sub find_mismatch()
    Dim MyMatch As Long, rFound
    Dim Rng As Range, oRng As Range, MyCell As Range
    Dim SearchRange As Range
    Dim FindWhat As Variant
    Dim FoundCells As Range
    Dim FoundCell As Range
    Set Rng = Sheets("Sheet1").Range("F1:F" & Sheets("Sheet1").Range("F" & Rows.Count).End(xlUp).Row)
    Set oRng = Sheets("Sheet2").Range("F1:F" & Sheets("Sheet2").Range("F" & Rows.Count).End(xlUp).Row)
    For Each MyCell In Rng
        If Application.WorksheetFunction.CountIfs(oRng, MyCell, oRng, MyCell.Offset(0, -5)) <> 0 Then
    Set SearchRange = oRng
        FindWhat = MyCell
        Set FoundCells = FindAll(SearchRange:=SearchRange, _
                                FindWhat:=FindWhat, _
                                LookIn:=xlValues, _
                                LookAt:=xlWhole, _
                                SearchOrder:=xlByColumns, _
                                MatchCase:=False, _
                                BeginsWith:=vbNullString, _
                                EndsWith:=vbNullString, _
                                BeginEndCompare:=vbTextCompare)
        If FoundCells Is Nothing Then
            'Debug.Print "Value Not Found"
        Else
            For Each FoundCell In FoundCells
                FoundCell.Interior.ColorIndex = 3
            Next FoundCell
        End If
    End If
    Next MyCell
    End Sub
    Kind regards,
    Simon Lloyd
    Microsoft Office Discussion

  10. #10
    I thank you much Simon for giving this code, however I tried it and I don't see it highlighting any numbers or anything else when I apply it. Is it perhaps missing some code?

Page 1 of 2 1 2 LastLast

Posting Permissions

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