Compare value

[smartguy]

Hello all,

I have excel file in the below format.

Sheet1:

Excel Workbook
	A	B
1	Data	value
2	45	1245617
3	46	2547563
4	48	1234
5	45	1545454545
Sheet1

Sheet 2:

Excel Workbook
	A	B
1	Data	value
2	45	1245617
3	48	1234
4	45	1545454545
Sheet2

I want to compare data based on "B" column value Sheet1 to Sheet2.

i want missing data in to sheet3.

Answer :

Excel Workbook
	A	B
1	Data	value
2	46	2547563
Sheet3

Please help to provide VBA MAcro...

 

[MickG]

Hi, Smartguy , Try this:-

[COLOR="Navy"]Sub[/COLOR] MG26May48
[COLOR="Navy"]Dim[/COLOR] Rng [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] shts [COLOR="Navy"]As[/COLOR] Variant
[COLOR="Navy"]Dim[/COLOR] sNum [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
 shts = Array("Sheet1", "Sheet2")
[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
            .CompareMode = vbTextCompare
[COLOR="Navy"]For[/COLOR] sNum = 0 To UBound(shts)
 [COLOR="Navy"]With[/COLOR] Sheets(shts(sNum))
       [COLOR="Navy"]Set[/COLOR] Rng = .Range(.Range("B2"), .Range("B" & Rows.Count).End(xlUp))
   [COLOR="Navy"]End[/COLOR] With
        
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    [COLOR="Navy"]If[/COLOR] Not .Exists(Dn.Value) [COLOR="Navy"]Then[/COLOR]
        .Add Dn.Value, Dn.Offset(, -1)
    [COLOR="Navy"]Else[/COLOR]
         .Remove Dn.Value
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]Next[/COLOR] sNum
Sheets("Sheet3").Range("A2").Resize(.Count, 2) = Application.Transpose(Array(.items, .Keys))
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[smartguy]

Hi Mickg !!!

Thanks for your Reply.

Some times additional data also come in Sheet2.

But that data no need to compare.

Excel Workbook
	A	B
1	Data	value
2	45	1245617
3	48	1234
4	45	1545454545
5	10	12345
Sheet2

Wee need to check whether sheet 1 data is there in sheet2 . if not there what the data. that it.

i think you will clear now...

 

[MickG]

Try this:-

[COLOR="Navy"]Sub[/COLOR] MG26May31
[COLOR="Navy"]Dim[/COLOR] Rng1 [COLOR="Navy"]As[/COLOR] Range, Dn1 [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Rng2 [COLOR="Navy"]As[/COLOR] Range, Dn2 [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Fd [COLOR="Navy"]As[/COLOR] Boolean
[COLOR="Navy"]Dim[/COLOR] c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]With[/COLOR] Sheets("Sheet1")
    [COLOR="Navy"]Set[/COLOR] Rng1 = .Range(.Range("B2"), .Range("B" & Rows.Count).End(xlUp))
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]With[/COLOR] Sheets("Sheet2")
    [COLOR="Navy"]Set[/COLOR] Rng2 = .Range(.Range("B2"), .Range("B" & Rows.Count).End(xlUp))
[COLOR="Navy"]End[/COLOR] With
    ReDim Ray(1 To Rng1.Count + Rng2.Count, 1 To 2)
    [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn1 [COLOR="Navy"]In[/COLOR] Rng1
        Fd = False
        [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn2 [COLOR="Navy"]In[/COLOR] Rng2
            [COLOR="Navy"]If[/COLOR] Dn1 = Dn2 [COLOR="Navy"]Then[/COLOR] Fd = True
        [COLOR="Navy"]Next[/COLOR] Dn2
            [COLOR="Navy"]If[/COLOR] Fd = False [COLOR="Navy"]Then[/COLOR]
                c = c + 1
                Ray(c, 1) = Dn1.Offset(, -1)
                Ray(c, 2) = Dn1
            [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]Next[/COLOR] Dn1
Sheets("Sheet3").Range("A2").Resize(c, 2) = Ray
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[smartguy]

Thanks Mr.MickG

Your Rocking ......

it's Working Fine....

 

[smartguy]

Hi Mickg,

Sheet 1 to Sheet 2 Working fine
Sheet 2 to Sheet 1 i got error

If Sheet 2 New data found it's working fine. New data not found i got error.

Application Defined Object error.

Now i compare Sheet 2 to Sheet1.
Please help

 

[MickG]

Try this:-
Sheet 2 to sheet 1.
Results sheet3, column "D"

[COLOR="Navy"]Sub[/COLOR] MG26May23
[COLOR="Navy"]Dim[/COLOR] Rng1 [COLOR="Navy"]As[/COLOR] Range, Dn1 [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Rng2 [COLOR="Navy"]As[/COLOR] Range, Dn2 [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Fd [COLOR="Navy"]As[/COLOR] Boolean
[COLOR="Navy"]Dim[/COLOR] c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]With[/COLOR] Sheets("Sheet1")
    [COLOR="Navy"]Set[/COLOR] Rng1 = .Range(.Range("B2"), .Range("B" & Rows.Count).End(xlUp))
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]With[/COLOR] Sheets("Sheet2")
    [COLOR="Navy"]Set[/COLOR] Rng2 = .Range(.Range("B2"), .Range("B" & Rows.Count).End(xlUp))
[COLOR="Navy"]End[/COLOR] With
    ReDim Ray(1 To Rng1.Count + Rng2.Count, 1 To 2)
    [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn2 [COLOR="Navy"]In[/COLOR] Rng2
        Fd = False
        [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn1 [COLOR="Navy"]In[/COLOR] Rng1
            [COLOR="Navy"]If[/COLOR] Dn1 = Dn2 [COLOR="Navy"]Then[/COLOR] Fd = True
        [COLOR="Navy"]Next[/COLOR] Dn1
            [COLOR="Navy"]If[/COLOR] Fd = False [COLOR="Navy"]Then[/COLOR]
                c = c + 1
                Ray(c, 1) = Dn2.Offset(, -1)
                Ray(c, 2) = Dn2
            [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]Next[/COLOR] Dn2
Sheets("Sheet3").Range("D2").Resize(c, 2) = Ray
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[smartguy]

Hi,

I got error,

Application Defined object error

 

[smartguy]

Hi,

Sheet2 New Data found it's Working fine. if New data not found i got error...

 

[smartguy]

Hi MickG,

I got an error.

If Sheet1 & Sheet2 Same Value Means i got an error.

Application Defined object error