Identify numbers that only occur once in a column.

  • Thread starter Thread starter Karen
  • Start date Start date
K

Karen

I have numerous lists of about 2500 numbers each where most occure twice, but
some only occure once and these are the ones that I want to work with. Any
suggestion as to how I can do this in a simple manner?
 
Try this

suppose u have data in col A use this formula in B1
=COUNTIF($A$1:$A$2500,$A$1:$A$2500) and drag it ....

and then use filter to check which has 1 time.
 
some only occur once and these are the ones that I want to work with

One thought - try autofiltering on a helper col
Assume data in A2 down
Put in B2: =COUNTIF(A:A,A2)
Copy down. Put a label in B1. Then do a data>filter>autofilter on col B,
choose: 1, to filter out the required lines. You can copy the filtered lines
and paste it elsewhere.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:18,000 Files:359 Subscribers:56
xdemechanik
 
Hi,

Suppose your range is in A1:A30 (A1 has a heading, say Marks). In cell A33,
type Criteria. In Cell A34, type the following formula
=countif(range,A2)=1. Now click on any blank cell and Go to Filter >
Advanced Filter. The fields to be filled in Filter . Advanced Filter should
be self explanatory.

--
Regards,

Ashsih Mathur
Microsoft Excel MVP
www.ashishmathur.com
 
If your numbers are, say, in column F, then
=COUNTIF(F:F,F1)=1
returns true for the ones occuring once. you can use it either in e helper
column or as a conditional formatting formula depending on the way you want
to identify them.

Regards,
Stefi

„Karen†ezt írta:
 
Back
Top