Previous Topic Next Topic

Compare Excel Data

Closed
-
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
 -
Hello,
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
SA RPLI
S K Region
Bangalore

1 reply

Answer 1 / 1
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
 804
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
=A2&B2&C2&D2&E2
copy K2 down as long as there is data

similary in sheet B cell K2 copy the formula
=A2&B2&C2&D2&E2
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
line1:
 Next ca
 End With
End Sub


see sheet c
If there is problem post back.
Ask a question