Skip to main content
Code Review

Return to Question

edited tags
Link
Kaz
Kaz
  • 8.8k
  • 2
  • 31
  • 69
added 3 characters in body; edited title
Source Link
Jamal
Jamal
  • 35.2k
  • 13
  • 134
  • 238

compare Compare and find duplicates in 2 corresponding columns in 2 sheets VBA

I wantswant to compare (500) and find duplicate daily records within 2 sheets, and copy the unmatched row to another sheet, copy the match from another to 3rd sheet, and delete the matched records from original sheet.

I wantswant to compare Column B (unique) and column A of worksheet "results" to " Master List" flow would be- Match a first cell value in column B of "results" to Column B cell values of " Master List" If match found - compare column A of "results" to Column A cell values of " Master List" if match found Copy the row of Match from "Master List" for Column A to O to Next available row of "FOllow Ups" And mark the match row in "results" to be deleted in the end when search loop finished.

Else if match not found check next value in column B of " result" until last recordThe flow would be:

when whole search end delete marked records for match found in "results" and copy all the left out records to Next available table row in "Master List"

  • Match a first cell value in column B of "results" to Column B cell values of " Master List"

    • If match found - compare column A of "results" to Column A cell values of " Master List"

  • If match found

    • Copy the row of match from "Master List" for Column A to O to next available row of "Follow Ups"
    • Mark the match row in "results" to be deleted in the end when search loop finished

  • Else if match not found

    • check next value in column B of " result" until last record

  • When whole search ends, delete marked records for match found in "results" and copy all the left out records to Next available table row in "Master List".

I am kind of stuck and don't want to run in long loop, looking for expert help with shortest and fastest possible code. Here Here is some code already written and working, but not working well.

Thanks in advance for your help. IfIf possible -optionaloptional approach (Cancan both column value jointly compared with another sheet):

compare and find duplicates in 2 corresponding columns in 2 sheets VBA

I wants to compare (500) and find duplicate daily records within 2 sheets, and copy the unmatched row to another sheet, copy the match from another to 3rd sheet, and delete the matched records from original sheet.

I wants to compare Column B (unique) and column A of worksheet "results" to " Master List" flow would be- Match a first cell value in column B of "results" to Column B cell values of " Master List" If match found - compare column A of "results" to Column A cell values of " Master List" if match found Copy the row of Match from "Master List" for Column A to O to Next available row of "FOllow Ups" And mark the match row in "results" to be deleted in the end when search loop finished

Else if match not found check next value in column B of " result" until last record

when whole search end delete marked records for match found in "results" and copy all the left out records to Next available table row in "Master List"

I am kind of stuck and don't want to run in long loop, looking for expert help with shortest and fastest possible code. Here is some code already written and working, but not working well.

Thanks in advance for your help. If possible -optional approach (Can both column value jointly compared with another sheet)

Compare and find duplicates in 2 corresponding columns in 2 sheets

I want to compare (500) and find duplicate daily records within 2 sheets, and copy the unmatched row to another sheet, copy the match from another to 3rd sheet, and delete the matched records from original sheet.

I want to compare Column B (unique) and column A of worksheet "results" to " Master List".

The flow would be:

  • Match a first cell value in column B of "results" to Column B cell values of " Master List"

    • If match found - compare column A of "results" to Column A cell values of " Master List"

  • If match found

    • Copy the row of match from "Master List" for Column A to O to next available row of "Follow Ups"
    • Mark the match row in "results" to be deleted in the end when search loop finished

  • Else if match not found

    • check next value in column B of " result" until last record

  • When whole search ends, delete marked records for match found in "results" and copy all the left out records to Next available table row in "Master List".

I am kind of stuck and don't want to run in long loop, looking for expert help with shortest and fastest possible code. Here is some code already written and working, but not working well.

If possible optional approach (can both column value jointly compared with another sheet):

Source Link
sam
sam
  • 49
  • 2

compare and find duplicates in 2 corresponding columns in 2 sheets VBA

I wants to compare (500) and find duplicate daily records within 2 sheets, and copy the unmatched row to another sheet, copy the match from another to 3rd sheet, and delete the matched records from original sheet.

I have 3 worksheets(results, Master List, Follow Ups) " results" update daily with 500 records, and added to "master list", duplicate row added to "follow ups"

All have similar columns heading A to O.

I wants to compare Column B (unique) and column A of worksheet "results" to " Master List" flow would be- Match a first cell value in column B of "results" to Column B cell values of " Master List" If match found - compare column A of "results" to Column A cell values of " Master List" if match found Copy the row of Match from "Master List" for Column A to O to Next available row of "FOllow Ups" And mark the match row in "results" to be deleted in the end when search loop finished

Else if match not found check next value in column B of " result" until last record

when whole search end delete marked records for match found in "results" and copy all the left out records to Next available table row in "Master List"

I am kind of stuck and don't want to run in long loop, looking for expert help with shortest and fastest possible code. Here is some code already written and working, but not working well.

Thanks in advance for your help. If possible -optional approach (Can both column value jointly compared with another sheet)

Set sht1 = xlwb.Worksheets("results")
    Set sht4 = xlwb.Worksheets("Master List")
    Set sht5 = xlwb.Worksheets("Follow Ups")
    
    For i = 2 To sht1.Range("A1").SpecialCells(xlCellTypeLastCell).Row
            For j = 2 To sht4.Range("A1").SpecialCells(xlCellTypeLastCell).Row
                    If sht1.Cells(i, 2) = sht4.Cells(j, 2) And sht1.Cells(i, 1) = sht4.Cells(j, 1) Then
                    
                    'sht4.Rows(j).Copy
                   ' sht5.Activate
                    
                    'sht5.Cells(1, sht5.Range("A1").SpecialCells(xlCellTypeLastCell).Row).Select
                    
                  
                    
                    sht4.Rows(j).Copy _
                    Destination:=sht5.Cells(sht5.Range("A1").SpecialCells(xlCellTypeLastCell).Row + 1, 1)
                    
                    
                    
                        'sht1.Rows(i).Delete
                        
                        'i = i - 1
                     End If
        
            Next
    Next
        
    
     
sht1.Range("A2:O" & sht1.Range("A1").SpecialCells(xlCellTypeLastCell).Row).Copy Destination:=sht4.Cells(sht4.Range("A1").SpecialCells(xlCellTypeLastCell).Row, 1)