Compare Columns in Excel Issue

P

Pretty1996

Board Regular
Joined
Apr 28, 2010
Messages
100
Hi,
I’m hoping somebody can help me here,
I’ve been trying to get a piece of code to work that I saw on this site at the following link.
http://www.mrexcel.com/forum/showthread.php?t=330477
As you can see I posted my question there as well but got no reply.
The code supposedly does exactly what I need, however it doesn’t seem to work properly for me.
When running the code it will show me all the entries that are not in ‘Column A’, however it will only show me the first entry for a value that is not in ‘Column B’ and nothing else.
I’m using Excel 2007, not sure if that makes any difference.
Any help is much appreciated.
Regards
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
Sort by date Sort by votes
Presumes values from row 1, change if header row.

Try:

Rich (BB code): 
Option Explicit
    
Sub exa3()
Dim DIC1 As Dictionary, DIC2 As Dictionary
Dim wks As Worksheet
Dim rngLRow As Range, rngData As Range
Dim aryData, aryRet
Dim i As Long, x As Long, y As Long
    
    Set DIC1 = CreateObject("Scripting.Dictionary")
    Set DIC2 = CreateObject("Scripting.Dictionary")
                                'use real sheet's name
    Set wks = ThisWorkbook.Worksheets(ActiveSheet.Name)
    
    Set rngLRow = wks.Range("A1:B" & wks.Rows.Count).Find("*", _
                                                          wks.Cells(1, 1), _
                                                          xlValues, _
                                                          xlPart, _
                                                          xlByRows, _
                                                          xlPrevious)
    If rngLRow Is Nothing Then Exit Sub
    
    Set rngData = wks.Range(wks.Cells(1, 1), wks.Cells(rngLRow.Row, 2))
    aryData = rngData.Value
    ReDim aryRet(1 To UBound(aryData), 1 To 2)
    
    For y = 1 To UBound(aryData)
        If Not aryData(y, 1) = Empty Then DIC1.Item(aryData(y, 1)) = aryData(y, 1)
        If Not aryData(y, 2) = Empty Then DIC2.Item(aryData(y, 2)) = aryData(y, 2)
    Next
    
    For y = 1 To DIC1.Count
        If Not DIC2.Exists(DIC1.Keys(y - 1)) Then
            i = i + 1
            aryRet(i, 2) = DIC1.Keys(y - 1)
        End If
    Next
    
    i = 0
    For y = 1 To DIC2.Count
        If Not DIC1.Exists(DIC2.Keys(y - 1)) Then
            i = i + 1
            aryRet(i, 1) = DIC2.Keys(y - 1)
        End If
    Next
    
    wks.Range("D2").Resize(UBound(aryData, 1), 2).Value = aryRet
    wks.Range("D1:E1").Value = Array("Not in A", "Not in B")
End Sub

Hoep that helps,

Mark
 
Upvote 0
OOPS.

Change:
Rich (BB code): 
Dim DIC1 As Dictionary, DIC2 As Dictionary
to:
Rich (BB code): 
Dim DIC1 As Object, DIC2 As Object
 
Last edited:
Upvote 0
Thank you for your help GTO, Much appreciated.

however I am getting an error at this line of code...

Code: 
 If Not DIC2.Exists(DIC1.Keys(y - 1)) Then

the error message I get is "Property let feature not defined and property get procedure did not return an object"

any suggestions?

regards
 
Upvote 0
Sorry - I had already posted before changing it to late-binding and forgot all about this anomoly. You can either go with early-binding or plunk the keys into an array first and use the array.

For late-bound, try:
Rich (BB code): 
Option Explicit
    
Sub exa3()
Dim _
DIC1        As Object, _
DIC2        As Object, _
wks         As Worksheet, _
rngLRow     As Range, _
rngData     As Range, _
aryData     As Variant, _
aryRet      As Variant, _
aryDic1     As Variant, _
aryDic2     As Variant, _
i           As Long, _
y           As Long
    
    Set DIC1 = CreateObject("Scripting.Dictionary")
    Set DIC2 = CreateObject("Scripting.Dictionary")
                                'use real sheet's name
    Set wks = ThisWorkbook.Worksheets(ActiveSheet.Name)
    
    Set rngLRow = wks.Range("A1:B" & wks.Rows.Count).Find("*", _
                                                          wks.Cells(1, 1), _
                                                          xlValues, _
                                                          xlPart, _
                                                          xlByRows, _
                                                          xlPrevious)
    If rngLRow Is Nothing Then Exit Sub
    
    Set rngData = wks.Range(wks.Cells(1, 1), wks.Cells(rngLRow.Row, 2))
    aryData = rngData.Value
    ReDim aryRet(1 To UBound(aryData), 1 To 2)
    
    For y = 1 To UBound(aryData)
        If Not aryData(y, 1) = Empty Then DIC1.Item(aryData(y, 1)) = aryData(y, 1)
        If Not aryData(y, 2) = Empty Then DIC2.Item(aryData(y, 2)) = aryData(y, 2)
    Next
    
    '// added workaround for late-binding//
    ReDim aryDic1(1 To DIC1.Count)
    ReDim aryDic2(1 To DIC2.Count)
    aryDic1 = DIC1.Keys
    aryDic2 = DIC2.Keys
    
    For y = 1 To DIC1.Count
        If Not DIC2.Exists(aryDic1(y - 1)) Then
            i = i + 1
            aryRet(i, 2) = aryDic1(y - 1)
        End If
    Next
    
    i = 0
    For y = 1 To DIC2.Count
        If Not DIC1.Exists(aryDic2(y - 1)) Then
            i = i + 1
            aryRet(i, 1) = aryDic2(y - 1)
        End If
    Next
    
    wks.Range("D2").Resize(UBound(aryData, 1), 2).Value = aryRet
    wks.Range("D1:E1").Value = Array("Not in A", "Not in B")
End Sub


Hope that helps,

Mark
 
Upvote 0
It's been a while but I'm hoping someone will be able to assist. I'm trying to use this code and it outputs exactly what I'm after. My issue is that columns I'm adding to the array are from 2 separate worksheets from within the same workbook.

So I want to compare Column C from Sheet1 and Column B from Sheet2.

Is this even possible?

Any help would be appreciated as I'm quite new with working with arrays in VB!
 
Upvote 0
Greetings Kobo and welcome to the forum :-)

Not sure I'll have time right now, but just for anyone to answer:

We want to build two arrays of unique items that exist in one column, but not the other, correct?

Mark
 
Upvote 0
Looks like I've solved the issue by fumbling around making 2 arrays and comparing them. Thanks for the push in the direction I needed :)
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Solved
Insert Image In Cell - which versions of excel does this work with?
Replies
8
Views
247
RoryA
RoryA
A
  • Question Question
Complex (for me) coding issue.
Replies
1
Views
99
offthelip
O
J
  • Question Question
Pulling Data From ~300 Individual Sheets Onto a Master Sheet
Replies
1
Views
342
ranman256
ranman256
D
  • Question Question
Picking out the correct Meursing code from table
Replies
0
Views
91
DazCD
D
J
  • Question Question
Filtering Results in List Box
Replies
2
Views
160
JohnS_323
J

Forum statistics

Threads
1,225,247
Messages
6,183,834
Members
453,190
Latest member
Makri93

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top