How to delete rows containing one duplicate record

Z

Z Man

I have a spreadsheet with a few thousand rows. Each row lists an
individual's name, earnings, and identification number (the ID# is on column
B). I want each person's row to appear only once, regardless of whether it
currently only once or dozens of times. For example, the following table, I
would like the row with 123456 to appear only once (it can be the first
instance, since the other data is not significant). So, I would have just
three rows.

A B C

JohnA 123456 $12,543
Jim 456124 $11,250
Mary 545278 $12,111
John 123456 $12,597
JohnC 123456 $12,591

(Keep in mind that I want to delete subsequent rows with the same data in
Column B, regardless of what's in the other cells in that row.)

I have tried Filtering, but cannot seem to get it to work. Is there any easy
way to do this?
 
U

upstate_steve

Make sure your data starts on row 2.

Assuming your database occupies Columns A-C, select the three cells t
the right of your first row of data (D2-F2) and enter this as an arra
fomula:


=IF(COUNTIF(OFFSET($B$1,0,0,ROW()-1,1),$B2)=0,$A2:$C2,"")

Drag down to the last row of your data, select Column E, Autofilter an
choose (NonBlanks).

You can hide the original data, or enter the converted data on
different sheet and adjust the formula accordingly, whatever.

Steve Przyborski
Boston, Massachusett
 
Top