Previous Topic Next Topic

Use in Scripting.Dictionary- item join string 3col

Solved/Closed
mokie1 Posts 2 Registration date Wednesday March 28, 2018 Status Member Last seen March 28, 2018 - Updated on Mar 28, 2018 at 04:59 PM
mokie1 Posts 2 Registration date Wednesday March 28, 2018 Status Member Last seen March 28, 2018 - Mar 28, 2018 at 12:37 PM
Hello, I'm looking for example to use scripting.dictionary.
on topic user Smallman1
https://ccm.net/forum/affich-947210-any-faster-version-of-the-same-code-to-handle-5000-records

the example is compare only for 1 column?
Please let me explain how to compare if string in item join form 3 column (if item is a string from column B+C+E)

I was try like this but I'm lost 
.Item(ar(i, 1) & ar (i,2) & ar(i,3) = Empty



Option Explicit

Sub DicSolve() 'Excel VBA find duplicates with the scripting dictionary.
Dim ar As Variant
Dim i As Long
Dim j As Long
Dim n As Long

ar = Sheet2.Cells(1, 3).CurrentRegion.Value
With CreateObject("Scripting.Dictionary")
.CompareMode = 1
For i = 2 To UBound(ar, 1)
.Item(ar(i, 1)) = Empty
Next
ar = Sheet1.Cells(1).CurrentRegion.Value
n = 1

For i = 2 To UBound(ar, 1)
If .exists(ar(i, 1)) Then
n = n + 1
For j = 1 To UBound(ar, 2)
ar(n, j) = ar(i, j)
Next
End If
Next
End With
Sheet3.Cells(10, 1).Resize(n, UBound(ar, 2)).Value = ar
End Sub

1 response

Answer 1 / 1
mokie1 Posts 2 Registration date Wednesday March 28, 2018 Status Member Last seen March 28, 2018
Mar 28, 2018 at 12:37 PM
I don't know is that solution is perfect but it works:) 

Option Explicit
Sub DicSolve() 'Excel VBA find duplicates with the scripting dictionary.
Dim ar As Variant
Dim i As Long
Dim j As Long
Dim n As Long
'Dim dict As Object

 ar = Sheet2.Cells(1, 3).CurrentRegion.Value
    With CreateObject("Scripting.Dictionary")
        .CompareMode = 1
        '.CompareMode = vbTextCompare
        For i = 2 To UBound(ar, 1)
            .Item(ar(i, 1) & "|" & ar(i, 2) & "|" & ar(i, 3)) = Empty
        Next
        ar = Sheet1.Cells(1).CurrentRegion.Value
        n = 1
        
        For i = 2 To UBound(ar, 1)
            If .exists(ar(i, 1) & "|" & ar(i, 2) & "|" & ar(i, 3)) Then
            'do nothing
            Else
            
                n = n + 1
                For j = 1 To UBound(ar, 2)
                    ar(n, j) = ar(i, j)
                Next
            End If
        Next
    End With
 Sheet3.Cells(10, 1).Resize(n, UBound(ar, 2)).Value = ar
 End Sub
0

Similar discussions

WhatsApp Business Catalog Blocked
Daniel -
HelpiOS -

1 response
Can't create a WhatsApp business catalog
Santeagu -
Zombie -

1 response
WhatsApp catalog error
rmogane16 -
HelpiOS -

1 response