how do i sort a spread sheet and remove duplication

[Shiraz]

I need to sort a large spreadsheet - by the column of account numbers - (over
50K) with duplications. I need to remove the duplications and keep only 1
entry of each account

 

[Max]

One way is to use autofilter on a helper col which flags the duplicates

Assume account numbers running in A2 down
Insert a new col B (if required)
Put in B2:
=IF(COUNTIF(A$2:A2,A2)>1,"x","")
Copy B2 down to the last row of data in col A. Col B will flag all duplicate
account numbers found in col A with an "x". Then apply autofilter on col B,
select: "x" from the droplist in B1. Select all the filtered rows,
right-click > Delete rows. Remove autofilter.

 

[Don Guillett]

data>filter>advanced filter>unique

 

[Gord Dibben]

Data>Filter>Advanced Filter>Unique records only will return the uniques which
you can copy to another location.

For more on this see Debra Dalgleish's site

http://www.contextures.on.ca/xladvfilter01.html#FilterUR


Gord Dibben MS Excel MVP