How do I delete both duplicate rows from a spreadsheet?
 

I have a spreadsheet of over 10,000 rows, with 3 columns of data. Most lines
in the spreadsheet are duplicates, but there are a few singles here and
there. I need to do a filter to completely remove BOTH duplicate lines so I
am only left with the single records. Is there a way to do this?

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.

How do I delete both duplicate rows from a spreadsheet?
 

Thank you so much Richard, this is working for the most part, but here is my
problem below. It is missing some lines I am guessing because of how the
formula is worded. I need it to recognize all duplicate lines. Is there
more wording that should be added to the formula?

name id filename
ALFARAG99CCCFF alfarag samerissa-07[1].mp3 ***
ALFARAG99CCCFF alfarag samerissa-07[1].mp3 ***
ALFARAG99CCCFF alfarag samerissa-08[1].mp3 ***
ALFARAG99CCCFF alfarag samerissa-08[1].mp3
ALLENJC99BC124 allenjc Barenaked Ladies - If I Had $1000000.mp3
ALLISWSMZ10JM ALLISWS RadioPhoneCallGoneWrong.mp3
ALLISWSMZ10JM ALLISWS TowYardComplaint.mp3 ***
ALLISWSMZ10JM ALLISWS WeAretheCh[1].mp3 ***
ALLISWSMZ10JM ALLISWS WeAretheCh[1].mp3

"RichardM" wrote:

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.

How do I delete both duplicate rows from a spreadsheet?
 

HI,

You would need to check the second part of the formula in your
worksheet, -AND(A2=A1,B2=B1,C2=C1)-
The formula has already tested that line 4 equals line 5, but fails the
test line 5 = line 4.

Hope this helps.

--

natalia Wrote:
Thank you so much Richard, this is working for the most part, but here
is my
problem below. It is missing some lines I am guessing because of how
the
formula is worded. I need it to recognize all duplicate lines. Is
there
more wording that should be added to the formula?

name id filename
ALFARAG99CCCFF alfarag samerissa-07[1].mp3 ***
ALFARAG99CCCFF alfarag samerissa-07[1].mp3 ***
ALFARAG99CCCFF alfarag samerissa-08[1].mp3 ***
ALFARAG99CCCFF alfarag samerissa-08[1].mp3
ALLENJC99BC124 allenjc Barenaked Ladies - If I Had $1000000.mp3
ALLISWSMZ10JM ALLISWS RadioPhoneCallGoneWrong.mp3
ALLISWSMZ10JM ALLISWS TowYardComplaint.mp3 ***
ALLISWSMZ10JM ALLISWS WeAretheCh[1].mp3 ***
ALLISWSMZ10JM ALLISWS WeAretheCh[1].mp3

"RichardM" wrote:

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.




--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059
View this thread: http://www.excelforum.com/showthread...hreadid=537221