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?
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
