trying to sort some data

  • Thread starter Thread starter milamber2004
  • Start date Start date
M

milamber2004

Is there a way to do a group sort? I am working on a project to remove
data that shows up more than 3 times in one column. In this case, an
address...(multiple addresses would indicate an apartment building; my
company is looking for single family homes). I can sort by address,
however, it takes hours to view the entire list to remove duplicates.
Is there a way to sort in Excel 2000 in which it will group the entries
that have duplicates- like at the end of the list? I know this can be
done in a regular database.. not familiar with how Excel would handle
it.

what i want my data to do:
example:

ph # first name last name address city state zip

121 bill jones 1212 S Dr. San Diego CA 92929
233 steve roberts 1212 S Dr. San Diego CA 92929
123 rob smith 1001 T St. Sacramento CA 95828
234 jill rogers 1001 T St. Sacramento CA 95828
111 rob smith 1001 T St. Sacramento CA 95828

so all the duplicate addresses appear at the bottom; those that exceed
3 or more. all unique address would come first.

Any guidance will be much appreciated.
 
Not sure if this is what you want, but what about using Autofilter to filter
duplicates.(Data, Filter, Advanced Filter) to pull out only unique records?
 
Use a helper column: if your data is in A:D, then in E1, use the
formula
=COUNTIF(D:D,D1)
and copy down to match your data in column D. Then sort on Col E, and
delete all rows where column E >1.

HTH,
Bernie
MS Excel MVP
 
Excel data that is organized as a database (i.e. rows of columns) can
be queries using SQL just like a regular database (well, just like MS
Access). Take a look at MS Query (Data, Get External Data) or roll
your own using ADO and the MS OLEDB Jet provider.
 
Back
Top