Deleting duplicates of first instance

QuestionEdit

QUESTION: Here's what I need to happen:

1 Column A Column B 2 11111 X 3 12345 P 4 12345 P 5 12345 PC 6 12345 PC 7 12345 P

Where data in Column A is duplicated (rows 3-7), I need a way of clearing out the rows whose Column B value equals the first instance, while retaining all other rows: I need rows 4 and 7 deleted, cleared, or marked (whichever is easier). The trick is that I need the rows to remain in the original order (because of data in other columns).

ANSWER: Daniel,

in c3 put in the formula

=if(countif($A$3:A3,A3)=1,"Keep",na())

then drag fill it down the column.

Once you are certain that the correct rows are being kept, then go to column C and hit F5

this brings up the goto dialog. Select special, then in the resulting dialog, click on Formulas and uncheck everything under formulas but Errors.

then click OK.

Now all the rows to be deleted should be selected in column C.

in Excel 2003 and erlaier, do Edit=>Delete and it should prompt you whether you want to delete cells or entire rows. Select entire rows.

in Excel 2007, you go to the home tab, then the Cells command button and select the delete dropdown (don't just click the delete button), then select Delete Sheet Rows.

Now you can delete column C.

---------- FOLLOW-UP ----------

QUESTION: Thanks, Tom--

The formula only wants to keep the first instance of a value in Column A. In my example above, it kept only row 3. I need it to keep the first instance and any that have a differing value in column B, thus rows 3, 5, and 6.

Alternatively, when the value in A is repeated in multiple rows, if it only kept those rows where B's value differed from that of B on the first row, I would be happy.

AnswerEdit

Daniel,

I misread what you wrote. I thought you had written delete 4 to 7 rather than delete 4 and 7.

I think you will need to use two columns to what you want.

in C2 put in

=IF(COUNTIF($A$2:A2,A2)=1,A2&"_"&B2,"")

in D2 put in

=IF(AND(COUNTIF($C$2:C2,A2&"_"&B2)>0,A2&"_"&B2<>C2),NA(),"")

then select C2 and D2 and drag fill down the column.

then use Column D to delete the rows.

Lightly tested, but that worked for me.