Conditional formatting: deleting entire rows

[CEM]

I need to delete rows that do not have the same number (a pair) in a column

O A B C
1 X 1 X
2 X 1 X
3 X 2 X
4 X 2 X
5 X 3 X
6 X 4 X
7 X 4 X
8 X 5 X


If the value in column b does not have the same value above/below than the whole row is deleted. Row 5 and 8 should be deleted in the example above.

Can anyone provide assistance?

Thank you.

[ChemistB]

Let's say in D2

=IF(OR(B1=B2,B2=B3),"Keep","Delete") dragged down.

Then filter on D2 for "Delete" and select and delete those rows.
Does that work for you?

[CEM]

Thanks, thats it! Is there any way I could have the formula actually delete the row automatically without sorting?

Thank you

[ChemistB]

Formulas Won't delete rows. You could write VBA (a macro) to delete the rows.
Also, I said Filtering, not sorting. Once you remove the filter, the order will be the same without the duplicates.

[ChemistB]

This macro should delete your duplicates

Please Login or Register  to view this content.

[CEM]

Understood. Thank you.