List Duplicates

  • Thread starter Thread starter Francis
  • Start date Start date
F

Francis

Column A1 through A100 contains a list of names, some of which are
duplicated. what is the formula in Column B that will list only the names in
column A, but only list them once if they are duplicated and also names that
are not duplicate

TIA
 
If you are using 2007 you can use the menu function 'Remove duplicates'.
Sorry that I am not sure whether this is available in previous versions.

Another way is Sort Column A in ascending order. Apply thiis formula in B2
and then copy that upto B100. This will return the distinct values in Column
B. Copy the entire column and Paste special there itself and sort to get the
unique list.

=IF(A3<>A2,A2,"")


If this post helps click Yes
 
If you are using 2007 you can use the menu function
'Remove duplicates'. Sorry that I am not sure
whether this is available in previous versions.
Click to expand...

It is not.

Another way, and probably the easiest, is to use Advanced filter to extract
the unique values.

Let's assume your data is in the range A1:A10.

Select the range A1:A10
Goto the menu Data>Filter>Advanced filter
Answer the prompt about using a column header.
Select Copy to another location
Copy to: B1
Select: Unique records only
OK
 
Hi Francis,

I don't believe that Remove duplicate is available in 2003. There is an
add-in but I know you have to buy it.

There is also an alternative. You can use Advanced Filter. Go to Data,
Advanced Filter. On the action, you can either filter the list, in place OR
Copy to another location. If you plan to paste it onto another place, just
use Copy to another location as it will save you from having to copy-paste it
yourself. If you do select this, you will enable the "Copy to:" option.

On the list range, select your cells, which is A1:A100.

On the Copy to range, point it to wherever you want it to copy to (i.e. B1)

Then make sure you tick the box "Unique records only".

Then hit OK.

Storm
 
Back
Top