asked on

comparsion of two excel and get the result in another sheet using macro/VBA

workbook 1 and workbook 2

First requirement:

1)a) workbook 2 "A" column(Sheet 1) and workbook 1 "C" column(SHeet1) need to compare . data is available in A column(workbook2) but not in C column (workbook1) . i wanna know which data is missing in workbook1..i need this data in separete sheet and separate coding

1 b) workbook 2 "A" column(Sheet 1) and workbook 1 "C" column(SHeet1) need to compare . data is available in C column(workbook1) but not in A column (workbook2) . i wanna know which data is missing in workbook2..i need this data in separete sheet and separate coding

I want the entire rows in another sheet or excel not only the single columns.
Workbook1_R-.xlsb
Workbook2_N.xlsx

byundt

Since the worksheet names in your question didn't match the workbooks you posted, I wrote the code so you could adjust it easily. Note that the code takes a while to run.

Sub Comparo()
Dim wb1 As Workbook, wb2 As Workbook
Dim ws1 As Worksheet, ws2 As Worksheet, wsResults1 As Worksheet, wsResults2 As Worksheet
Dim sName1 As String, sName2 As String

Application.ScreenUpdating = False

Set wb1 = ThisWorkbook
Set wb2 = Workbooks("Workbook2_N.xlsx")
Set ws1 = wb1.Worksheets("Sheet1")
Set ws2 = wb2.Worksheets(1)     'There is no worksheet named Sheet1, so use first worksheet in the workbook
sName1 = "Missing from wb1"     'Name of results worksheet containing data missing from workbook wb1
sName2 = "Missing from wb2"     'Name of results worksheet containing data missing from workbook wb2

On Error Resume Next
Set wsResults1 = wb1.Worksheets(sName1)
Set wsResults2 = wb1.Worksheets(sName2)
If wsResults1 Is Nothing Then
    Set wsResults1 = wb1.Worksheets.Add(after:=wb1.Worksheets(wb1.Worksheets.Count))
    wsResults1.Name = sName1
End If
If wsResults2 Is Nothing Then
    Set wsResults2 = wb1.Worksheets.Add(after:=wb1.Worksheets(wb1.Worksheets.Count))
    wsResults2.Name = sName2
End If
On Error GoTo 0

CompareAndExport ws1.Columns("C"), ws2.Columns("A"), wsResults1
CompareAndExport ws2.Columns("A"), ws1.Columns("C"), wsResults2
End Sub

Sub CompareAndExport(SourceCompareCol As Range, TargCompareCol As Range, wsResults As Worksheet)
'Compares values in SourceCompareCol against TargCompareCol. If not found in TargCompareCol, then the entire row is copied to wsResults
Dim i As Long, ii As Long, n As Long, nCols As Long
Dim rg As Range, rgResults As Range, rgSource As Range, rgTarg As Range
Dim v As Variant
Set rgSource = Intersect(SourceCompareCol, SourceCompareCol.Worksheet.UsedRange).Columns(1)
Set rg = Intersect(rgSource.EntireRow, SourceCompareCol.Worksheet.UsedRange)
Set rgTarg = Intersect(TargCompareCol, TargCompareCol.Worksheet.UsedRange).Columns(1)
n = rg.Rows.Count
nCols = rg.Columns.Count
    
    'Clear wsResults worksheet, then copy over the header labels
wsResults.Cells.ClearContents
ii = 1
Set rgResults = wsResults.Cells(1, 1).Resize(1, nCols)
rgResults.Value = rg.Rows(1).Value

    'Loop through rgSource cells and match against rgTarg cells. If no match, then copy values to rgResults
For i = 2 To n
    If rgSource.Cells(i).Value <> "" Then
        v = Application.Match(rgSource.Cells(i).Value, rgTarg, 0)
        If IsError(v) Then
            ii = ii + 1
            rgResults.Rows(ii).Value = rg.Rows(i).Value
        End If
    End If
Next
End Sub

Open in new window

Workbook1_R-Q_29015954.xlsb

ASKER

byundt,

I tried the above code its said subscript out of range. i attached correct file here .could you please change the code

here we need to compare A column and A column .

above i ask two question byundt...first find the missing data in workbook2 when compare with workbook1 and do vice versa again that mean find the missing data in workbook1 when compare with workbook2 ..
Workbook1_R-.xlsb
Workbook2_N.xlsx

ASKER CERTIFIED SOLUTION

byundt

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer