Compare two columns in different worksheets and create a message listing the missing data

[Mann750]

Hi,

I'm sure this query has been answered somewhere else but I can't seem to find it. I basically have information in two worksheets in the same workbook which need to be compared and the missing values from one worksheet need to be listed in a message. There are duplicate values in both worksheets so only need a list of the unique missing values. For example:

Sheet1
Column A
1
2
1
5
5
2
3
5
4

Sheet2
Column A
2
3
3
4
3
4


The message box should state that we are missing 1 and 5 from the dataset as it is not in Sheet2. If the list could be sorted in ascending order that would be great too.

Many thanks!

 

[Mann750]

Many thanks to everyone who has replied to my query
I shall test them all today and let you know how it goes.

 

[Mann750]

I have tried everyone's macros and they all work great! Thank you once again

I have one additional query though...in my list in Sheet1 I use an IF statement to filter the list that needs to be compared and if the condition is true the cell is set as blank using double quotation marks (""). My issue is that when the macro is run the messagebox displays a blank to reference that that is missing from Sheet2 (which doesn't have blank cells). Without having to remove the blank cells how can I modify the code to ignore blank cells in the range? I hope this makes sense!

Would also like to mention I am using MickG's code (as I understand it a little better) so maybe one of the other macros may already solve this issue...

 

[Mann750]

It's ok I have figured it out! I inserted the code in red, simple but I still struggled at first:

Set Dic = CreateObject("scripting.dictionary")
For Each Dn In Rng2: Dic(Dn.Value) = Empty: Next
For Each Dn In Rng1
[COLOR=#ff0000]    If Dn <> "" Then
[/COLOR]    If Not Dic.exists(Dn.Value) Then
        ReDim Preserve Ray(c)
        Ray(c) = Dn
        c = c + 1
    End If
    End If

 

[hiker95]

Mann750,

My GetMissingV2 macro addressed your latest concern.

You are very welcome. Glad we could help.

Thanks for the feedback.

Come back anytime.

 

[Mann750]

Sorry all I have just thought of something else that I would like this macro to do.

Again I am using MickG's macro and what I would like is for the list created in the msgbox using the Join function to be put into cells after the end of the last row in sheet2.

Dic.removeall
[COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] R [COLOR=navy]In[/COLOR] Ray: Dic(R) = Empty: [COLOR=navy]Next[/COLOR]
    ReDim Ray(1 To Dic.Count)
        Ray = Dic.keys
[COLOR=navy]For[/COLOR] i = 0 To UBound(Ray)
    [COLOR=navy]For[/COLOR] j = i To UBound(Ray)
        [COLOR=navy]If[/COLOR] Ray(j) < Ray(i) [COLOR=navy]Then[/COLOR]
            temp = Ray(i)
            Ray(i) = Ray(j)
            Ray(j) = temp
        [COLOR=navy]End[/COLOR] If
    [COLOR=navy]Next[/COLOR] j
[COLOR=navy]Next[/COLOR] i
MsgBox "Missing Numbers" & vbCrLf & "from Sheet 2 = " & vbCrLf & Join(Ray, vbCrLf)
[COLOR=#ff0000]INSERT CODE HERE TO ADD THE LIST IN Join(Ray,vbCrLf) TO THE LAST ROW IN SHEET2 IN INDIVIDUAL CELLS
[/COLOR][COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]

So from the example after the number "4" in row 6 I would have "1" in row 7 and "5" in row 8

Sheet2
Column A
2
3
3
4
3
4
1
5

Hope this makes sense

 

[MickG]

Add this line to the bottom of the code:-

Sheets("Sheet2").Range("A" & Rng2.Count + 1).Resize(UBound(Ray) + 1) = Application.Transpose(Ray)

 

[jamilm]

Hiker,

may you please shed light on this Scripting od Dictionary. i have seen many VBA codes using this CreateObject("scripting.dictionary") but cannot understand why this is used. and what this really do and is there any other way to achieve the same thing without using the CreateObject("scripting.dictionary")?

i would love to know about this CreateObject("scripting.dictionary") .

thank you

Try this:-

[COLOR=navy]Sub[/COLOR] MG19Apr19
[COLOR=navy]Dim[/COLOR] Rng1        [COLOR=navy]As[/COLOR] Range
[COLOR=navy]Dim[/COLOR] Rng2        [COLOR=navy]As[/COLOR] Range
[COLOR=navy]Dim[/COLOR] Dn          [COLOR=navy]As[/COLOR] Range
[COLOR=navy]Dim[/COLOR] n           [COLOR=navy]As[/COLOR] [COLOR=navy]Long[/COLOR]
[COLOR=navy]Dim[/COLOR] i           [COLOR=navy]As[/COLOR] [COLOR=navy]Long[/COLOR]
[COLOR=navy]Dim[/COLOR] j           [COLOR=navy]As[/COLOR] [COLOR=navy]Long[/COLOR]
[COLOR=navy]Dim[/COLOR] temp        [COLOR=navy]As[/COLOR] [COLOR=navy]String[/COLOR]
[COLOR=navy]Dim[/COLOR] Dic         [COLOR=navy]As[/COLOR] Object
[COLOR=navy]Dim[/COLOR] c           [COLOR=navy]As[/COLOR] [COLOR=navy]Long[/COLOR]
[COLOR=navy]Dim[/COLOR] Ray()
[COLOR=navy]Dim[/COLOR] R           [COLOR=navy]As[/COLOR] Variant
With Sheets("Sheet1")
[COLOR=navy]Set[/COLOR] Rng1 = .Range(.Range("A1"), .Range("A" & Rows.Count).End(xlUp))
[COLOR=navy]End[/COLOR] With
With Sheets("Sheet2")
[COLOR=navy]Set[/COLOR] Rng2 = .Range(.Range("A1"), .Range("A" & Rows.Count).End(xlUp))
[COLOR=navy]End[/COLOR] With
[COLOR=navy]Set[/COLOR] Dic = CreateObject("scripting.dictionary")
[COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] Dn [COLOR=navy]In[/COLOR] Rng2: Dic(Dn.Value) = Empty: [COLOR=navy]Next[/COLOR]
[COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] Dn [COLOR=navy]In[/COLOR] Rng1
    [COLOR=navy]If[/COLOR] Not Dic.exists(Dn.Value) [COLOR=navy]Then[/COLOR]
        ReDim Preserve Ray(c)
        Ray(c) = Dn
        c = c + 1
    [COLOR=navy]End[/COLOR] If
[COLOR=navy]Next[/COLOR]
Dic.removeall
[COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] R [COLOR=navy]In[/COLOR] Ray: Dic(R) = Empty: [COLOR=navy]Next[/COLOR]
    ReDim Ray(1 To Dic.Count)
        Ray = Dic.keys
[COLOR=navy]For[/COLOR] i = 0 To UBound(Ray)
    [COLOR=navy]For[/COLOR] j = i To UBound(Ray)
        [COLOR=navy]If[/COLOR] Ray(j) < Ray(i) [COLOR=navy]Then[/COLOR]
            temp = Ray(i)
            Ray(i) = Ray(j)
            Ray(j) = temp
        [COLOR=navy]End[/COLOR] If
    [COLOR=navy]Next[/COLOR] j
[COLOR=navy]Next[/COLOR] i
MsgBox "Missing Numbers" & vbCrLf & "from Sheet 2 = " & vbCrLf & Join(Ray, vbCrLf)
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]

Regards Mick

 

[jamilm]

my message refers to Mick as well.

Hiker,

may you please shed light on this Scripting od Dictionary. i have seen many VBA codes using this CreateObject("scripting.dictionary") but cannot understand why this is used. and what this really do and is there any other way to achieve the same thing without using the CreateObject("scripting.dictionary")?

i would love to know about this CreateObject("scripting.dictionary") .

thank you

 

[MickG]

If you Google:- vba + scripting dictionary
You will get a lot off information, that you can dissect at your leisure