View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.misc
RichardM
 
Posts: n/a
Default How do I delete both duplicate rows from a spreadsheet?

Try the following:
Make sure your data has a heading row
In the Test column below
put the formula

=IF(OR(AND(A2=A3,B2=B3,C2=C3),AND(A2=A1,B2=B1,C2=C 1)),"***","")
and copy down the column.

Result is as follows:

A B C D
1 Row1 Row2 Row3
2 dd ee ff ***
3 dd ee ff ***
4 ee dd dd ***
5 ee dd dd ***
6 gg ee ww ***
7 gg ee ww ***
8 ccc
9 cc
10 ff
11 hh
12 jj
13 aaa ***
14 aaa ***

You can then sort the column with the *** and delete all the marked
rows to remove the duplicates.

Normally before I remove duplicates I copy the Test column and then do
a paste special values to ensure that the duplicates stay marked but in
this case they stay together even after a sort.