List Duplicates

[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

 

[Jacob Skaria]

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

 

[T. Valko]

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.

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

 

[Storm]

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

 

[Bernd P]

Hello,

Another option ist my UDF Pfreq which lists how often an item appears:
for example, array-enter into B1:C20
=Pfreq(A1:A100)

My UDF you can find here:
http://www.sulprobil.com/html/pfreq.html

Regards,
Bernd