• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Data Duplicate

Hi SirJB7 ,


I tried the following code on the data in your file ; I do not know about the timing , but I think the code is more readable ?

[pre]
Code: 
Public Sub Compare_Lists()
With Application
.EnableEvents = False
.Calculation = xlManual
End With

Dim dictFor As New Dictionary, dictIn As New Dictionary
Dim nLookFor As Long, nLookIn As Long, nLookMax As Long
Dim lrow_colA As Long, lrow_colC As Long
Dim I As Long, j As Long, k As Long
Dim newvals As Variant

lrow_colA = Cells(Rows.Count, 1).End(xlUp).Row
lrow_colC = Cells(Rows.Count, 3).End(xlUp).Row

LookFor = Range("C2:C" & lrow_colC).Value2
LookIn = Range("A2:A" & lrow_colA).Value2

newvals = LookFor

nLookFor = UBound(LookFor)
nLookIn = UBound(LookIn)
nLookMax = Application.Max(nLookFor, nLookIn)

On Error Resume Next
For j = 1 To nLookIn
dictIn.Add LookIn(j, 1), LookIn(j, 1)    ' dictionary
Next j

For j = 1 To nLookFor
dictFor.Add LookFor(j, 1), LookFor(j, 1)    ' dictionary
Next j

On Error GoTo 0

I = 1
k = 1
For j = 1 To nLookFor
If Not (dictIn.Exists(LookFor(j, 1))) Then
newvals(k, 1) = LookFor(j, 1)
k = k + 1
End If
Next

Range("E2:E" & nLookMax + 1).ClearContents
Range("E2:E" & k).Value = newvals

I = 1
k = 1
For j = 1 To nLookIn
If Not (dictFor.Exists(LookIn(j, 1))) Then
newvals(k, 1) = LookIn(j, 1)
k = k + 1
End If
Next

Range("G2:G" & nLookMax).ClearContents
Range("G2:G" & k).Value = newvals

With Application
.EnableEvents = True
.Calculation = xlAutomatic
End With

Set newvals = Nothing
End Sub
[/pre]
This is from : http://fastexcel.wordpress.com/2012/07/10/comparing-two-lists-vba-udf-shootout-between-linear-search-binary-search-collection-and-dictionary/


As mentioned in the link :


Because the function uses the Dictionary Object from VBScript you need to add a reference to the Microsoft Scripting Runtime.


Narayan
 
@NARAYANK991

Hi!


I haven't checked that code yet and I think I won't be able to do it untl tomorrow late or the day after tomorrow. Perhaps someone else could do it (inbp, you, ...).


Regarding the time, it isn't hard at all to insert my "X" procedure, I guess.


If this solution does the same as the other, list of additions, list of deletions, and runs faster it'd be very advisable to do a benchmark with the two.


About code readability, I feel very very comfortable with my code techniques, so I'm afraid I wouldn't be a fair enough judge so as to rule in this case. But as you asked, personally -and it's only my humble opinion- I think that the code downloaded from that link isn't well structured nor grouped, so I really don't find it more readable. But the customer is always right, so let inbp compare and choose.


Regads!
 
Hi, inbp!


NARAYANK991 posted a solution downloaded from fastexcel website. As I can't do the appropriate and required tests, comparisons and benchmarks now, maybe you wanna give it a try, who knows! Perhaps it's equivalent in features and faster. If you or someone else (I diplomatically invited the poster to perform all checks and test so as to deliver a comparable solution, give him a little of time) think this might be a good idea, well, as Nike says, just do it.


Regards!
 
Dictionaries are also fast but they index items internally so I think end result will be closer to SirJB's code.


The code from FastExcel uses Early binding so setting the reference to Microsoft Scripting Runtime becomes necessary.

If the code is changed from early binding:

[pre]
Code: 
Dim dictFor As New Dictionary, dictIn As New Dictionary
Dim nLookFor As Long, nLookIn As Long, nLookMax As Long
Dim lrow_colA As Long, lrow_colC As Long
Dim I As Long, j As Long, k As Long
Dim newvals As Variant

lrow_colA = Cells(Rows.Count, 1).End(xlUp).Row
lrow_colC = Cells(Rows.Count, 3).End(xlUp).Row
to late binding like:

Dim dictFor As Object, dictIn As Object
Dim nLookFor As Long, nLookIn As Long, nLookMax As Long
Dim lrow_colA As Long, lrow_colC As Long
Dim I As Long, j As Long, k As Long
Dim newvals As Variant

'We create objects here instead declaring as new objects
Set dictFor = CreateObject("Scripting.Dictionary")
Set dictIn = CreateObject("Scripting.Dictionary")

lrow_colA = Cells(Rows.Count, 1).End(xlUp).Row
lrow_colC = Cells(Rows.Count, 3).End(xlUp).Row
[/pre]
 
Hi Sir & Narayan


I have used your code but it is showing the compile error


Regards

Muhammad SHakeel
 
If the compile error says User Defined Type is not defined then you have not set the reference which Narayan has asked you for.


1. Go to Visual Basic Editor [where you've pasted the code]

2. Choose Tools | References

3. Small window will open which will list available references you can attach. Scroll down and you should be able to find "Microsoft Scripting Runtime".

4. Select it by checking in the tickbox.


The error message should go away.


PS: Otherwise edit the code as I have suggested in my previous post.
 
You must log in or register to reply here.
Back
Top