How to find the difference of DATA

[hafizfarooq]

Hello,

I have 3 Excel sheet on which i have 3 Columns each,

1.UserName 2. Name 3. Contact#

I paste some data on Sheet1 in morning and some data on Sheet 2 in evening, How can i got the difference of data on Sheet 3.

Like on sheet 1, I paste only username A, B, C, D, E in morning and A, B, C, D, E, F, G, H, I on Sheet 2 in evening.

How can i get F, G, H, I on sheet 3.

Please help me.

FAROOQ

[Siddharth Rout]

Here is a basic example. you will have to modify this to suit your code.

to understand this, type a-e in Col A in Sheet1 starting from A2 and a-h in Col A in Sheet2 starting from A2. Run this code and it will populate the difference in Sheet3

Option Explicit

Sub Diff()

Dim Rng1 As Range, Rng2 As Range
Dim cl1 As Range, cl2 As Range
Dim Flg As Boolean
Dim i As Integer

Flg = False
'Change this number to the row
'number from where you want the
'data to be stored in sheet3
'for Example A2
i = 2

'Your range in Sheet1 in Column A in Sheet1
Set Rng1 = Sheets("Sheet1").Range("A2:A6")
'Your range in Sheet1 in Column A in Sheet2
Set Rng2 = Sheets("Sheet2").Range("A2:A9")

For Each cl2 In Rng2
    For Each cl1 In Rng1
        If cl1.Value = cl2.Value Then
            Flg = False
            Exit For
        Else
            Flg = True
        End If
    Next
        
    'if no match found store value
    If Flg = True Then
        Sheets("Sheet3").Range("A" & i).Value = cl2.Value
        i = i + 1
        Flg = False
    End If
Next

End Sub

If you have any queries, please feel free to ask.

Hope this puts you in the right direction....

[anhn]

This code works much faster and works with any number of records in Sheet1 and Sheet2.

Option Explicit

Sub GetNewRecords()
   Dim RangeToCopy As Range
   Dim UserNameRange As Range
   Dim aCell As Range
   
   Set aCell = Sheet2.Range("A2")
   Set UserNameRange = Sheet2.Range(aCell, aCell.End(xlDown))
   Set RangeToCopy = Sheet2.Range("A1:C1")
   For Each aCell In UserNameRange
      If Sheet1.Columns(1).Find(What:=aCell, LookAt:=xlWhole) Is Nothing Then
         Set RangeToCopy = Union(RangeToCopy, Sheet2.Range(aCell, aCell.Offset(0, 2)))
      End If
   Next
   Sheet3.Cells.ClearContents
   RangeToCopy.Copy Sheet3.Range("A1")
End Sub

[hafizfarooq]

How to use this code in EXCEL ??? I m ery new to Excel

[anhn]

In Excel VBA Editor, insert a Module then copy the code above to paste into the new Module.