Check duplicates in two workbooks

abcd1234 0
Viewed 24

Script

Can somebody help me out...

I am trying to compare two worksheets in separate workbooks using an unique identifier i.e. NewID/ OldID. If an ID is found in both worksheets, compare cell by cell in the containing the ID in the worksheets, then highlight the duplicated cell in the latest worksheet.

vba excel
1 Answers

You method will be very slow when both table is large.

I have deal with similar problem. The logic is like that:

Dim Range1 as Variant, Range2 as Variant
Range1 = ....
Range2 = .... (include 1 more column at the end)
Use for loop to put row number into the last column of Range2.

Call quicksort(Range2,1,norow2)
For i = 1 to norow1
  result = Search(Range1(i,1),Range2)
  If result >0 (you can do anything you want here)
next i

You can find quicksort and search function with nlogn speed instead of n^2 speed in stackoverflow easily. I just show the logic, the above is not VBA code.

Abel Wong0
Related