Compare two columns and delete cells [Closed]

Report
-
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
-
I have 3 columns in Excel 2007. The following is the sample data. Col3 is a formula that compares Col1 and Col2. If the value in the two columns match, then return True, else return False. I need a macro to remove the cells in Col2 and Col3 if Col3 is False. Once the cells removed, the cells on the Col2 and Col3 below the removed cells will move up. The expected result is shown below. Any help would be great!

Col1 Col2 Col3
AAA AAA True
CCC BBB False
DDD CCC False
DDD False

The expected result:

Col1 Col2 Col3
AAA AAA True
CCC CCC True
DDD DDD True

1 reply

Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
797
quote
Col1 Col2 Col3
AAA AAA True
CCC BBB False
DDD CCC False
DDD False
unquote

I suppose in the last row col.1 is blank col2 is DDD and column 3 is the formula

keep the formulas in col.. 3in all the rows.


copy data in sheet 1 in sheet 3 (from cell A1 also (this is for undo purposes)

AS YOU ARE MESSING UP WITH THE DATA SAVE THE DATA BASE SAFELY SOMEWHERE BEFORE RUNNING THE MACRO


try the macro on the sample sheets first and then try in original file later

the undo macro undoes what the macro has done provided the data is availabel in sheet 3 also as instructed before.

the macros are

Sub test() 
Dim r As Range, c As Range 
Set r = Range(Range("C1"), Range("C1").End(xlDown)) 
For Each c In r 
If c = False Then 
c.Offset(1, -1).Copy c.Offset(0, -1) 
If c.Offset(0, -1) = "" Then 
c.EntireRow.Delete 
End If 
End If 
Next c 
End Sub



Sub undo() 
Worksheets("sheet1").Cells.Clear 
Worksheets("sheet3").Cells.Copy 
Worksheets("sheet1").Range("A1").PasteSpecial 
End Sub

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!