Compare Excel Data

Indu - Sep 16, 2009 at 02:47 AM
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 - Sep 16, 2009 at 07:14 AM
I have two Excel files. Ex:A file having A to E column names and B file having A to J column names. A to E columns are same in A and B Table. And now the data of A to E columns has to be compared and if same data is there in A and B files that has to be separated including all the columns of B file into C file. How can I compare the data of A and B excel file and make separate if matches?

Indiramani K G
S K Region

1 response

venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 811
Sep 16, 2009 at 07:14 AM
let me rephrase it

1.columns A to E column names(column headings suppose in row n. 1) in both the sheets are smae.
2. column headings in columns F to J in sheet 2 are something else.
3. you want to find out cells for each row in colulmn A to E in sheet A are duplicated in columns A to E in sheet B and if so that particular row in sheet B should be ARCHIVED in sheet C
logic of the solution
what you can do in each of the sheets A and B have a helper column concatenating the cells in column A to E(column K in both the sheets may be the helper column for both the sheets more for uniformity sake ). then you make the test using "find" in a macro. as you have not given even an extract of your two sheets I had to compose sheet A and B

This is a hybrid solution

keep your original file safe somewhere so if there is a mess up original data is availability
do the following steps
name your sheets as sheet A,sheet B , sheet C
in sheet A in cell K2 copy this fomula
copy K2 down as long as there is data

similary in sheet B cell K2 copy the formula
here also copy K2 down.

both same formulas but for different sheets.

now try this macro

Sub test()
Dim rnga As Range, ca As Range
Dim rngb As Range, x, cfind As Range
With Worksheets("sheet A")
Set rnga = Range(.Range("K2"), .Range("K2").End(xlDown))
For Each ca In rnga
x = ca.Value
With Worksheets("sheet B")
'With .Columns("K:K")
    Set cfind = .Cells.Find(what:=x, lookat:=xlWhole, LookIn:=xlValues)
    If cfind Is Nothing Then GoTo line1
    Range(.Cells(cfind.Row, "a"), .Cells(cfind.Row, "j")).Copy
        With Worksheets("sheet C")
        .Cells(Rows.Count, "a").End(xlUp).Offset(1, 0).PasteSpecial
        End With
    End With
   ' End With
 Next ca
 End With
End Sub

see sheet c
If there is problem post back.