Compare Excel Data
Closed
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
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 - Sep 16, 2009 at 07:14 AM
Related:
- Compare Excel Data
- Transfer data from one excel worksheet to another automatically - Guide
- Tmobile data check - Guide
- Excel date format dd.mm.yyyy - Guide
- Beyond compare - Download - File management
- Excel free download - Download - Spreadsheets
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
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
=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
see sheet c
If there is problem post back.
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.
