Originally Posted by
natalia
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=C1)),"***","")
> 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.
>
>
Bookmarks