Excel formula that will delete a row

  • Thread starter Thread starter Bill
  • Start date Start date
B

Bill

I'm using an IF, THEN formula to compare two rows of
data, if they're the same, I don't want it to do
anything, however if they're different, I want it to
delete the row that's different. Example of the formula
as I've typed it on row 2: =if(b1=b2, "", "delete"). In
this example, if the two cells are the same, nothing will
be written for the formula, if they are not the same, the
word "delete" will be put in the cell where the formula
is. What I want the formula to do is Delete the row
rather than put the word delete in there. Is this
possible, and if so, how do I tell the formula to delete
a row rather then returning a text word?

Thanks again for your help if anyone knows!

Bill
 
Bill;

Formulas can't delete things.

What you can do is create a NEW list based on the list you want to delete
things from. E.g. if your big list is on sheet1, go to sheet2 and use some
IF formulas either either return a value, or "". Then you'll have a new list
of only the ones you want.

Alternatively, a macro could be put together to do some actual deleting.
 
I thought it sounded too simple. Thanks for the quick
response and help. I guess I'll have to look into this
whole Macro thing, there might be something to it?
Thanks again.

Bill
 
OK. One other method would use a helper column. Add a column to the right
side of your data. In that, put your formula that will return either "" or
"delete".

So you'll have
1 2 delete
1 1
1 4 delete
1 2 delete
0 0
0 4 delete

Then, select the whole mess of data, and sort it descending by the column
where "deletes" are. Now select the rows that have "delete" and delete the
whole rows.

Just an idea.
 
Or Autofilter on "Delete", select that column, do Edit / Go To / Special /
Visible cells only, then Edit / Delete / Entire row
 
Back
Top