finding unique numbers

G

Glenn

I have a column of numbers. Some numbers may appear in the column more than
one time. I need to find out the numbers that appear only one time.

For instance the column looks like this:

32
47
15
92
32
15
92

I need to find out that 47 is only used once. How can I do this?

Thanks.
 
D

Domenic

Assuming that A1:A7 contains your data, try...

B1, copied down:

=IF(ROWS($B$1:B1)<=SUM(--(COUNTIF($A$1:$A$7,$A$1:$A$7)=1)),INDEX($A$1:$A$
7,SMALL(IF(COUNTIF($A$1:$A$7,$A$1:$A$7)=1,ROW($A$1:$A$7)-ROW($A$1)+1),ROW
S($B$1:B1))),"")

....confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!
 
G

Glenn

I can't quite get this to work. What do you mean by confirmed with
CONTROL+SHIFT+ENTER ?
 
D

Domenic

Glenn said:
I can't quite get this to work. What do you mean by confirmed with
CONTROL+SHIFT+ENTER ?
Click to expand...

After typing the formula, instead of hitting just ENTER, hold the
CONTROL and SHIFT keys down, then while both keys are held down, hit the
ENTER key. Excel will place braces around the formula indicating that
you've entered the formula correctly.
 
A

Alan Beban

Glenn said:
I have a column of numbers. Some numbers may appear in the column more than
one time. I need to find out the numbers that appear only one time.

For instance the column looks like this:

32
47
15
92
32
15
92

I need to find out that 47 is only used once. How can I do this?

Thanks.
Click to expand...
=IF(COUNTIF(A:A,A1)=1,A1,"") filled down

Alan Beban
 
H

Harlan Grove

Domenic wrote...
Assuming that A1:A7 contains your data, try...

B1, copied down:

=IF(ROWS($B$1:B1)<=SUM(--(COUNTIF($A$1:$A$7,$A$1:$A$7)=1)),INDEX($A$1:$A$7,
SMALL(IF(COUNTIF($A$1:$A$7,$A$1:$A$7)=1,ROW($A$1:$A$7)-ROW($A$1)+1),
ROWS($B$1:B1))),"")

...confirmed with CONTROL+SHIFT+ENTER, not just ENTER.
Click to expand...
....

Bit redundant.

B1 could be simplified to =A1. As for B2, how about

=IF(OR(COUNTIF(B$1:B1,A2:A$7)=0),
INDEX(A2:A$7,MATCH(0,COUNTIF(B$1:B1,A2:A$7),0)),"")

[I just figured out that both ranges could be anchored at one end,
floating at the other.]
 
D

Domenic

Harlan Grove said:
B1 could be simplified to =A1.
Click to expand...

I'm not quite sure what you mean by this.
As for B2, how about

=IF(OR(COUNTIF(B$1:B1,A2:A$7)=0),
INDEX(A2:A$7,MATCH(0,COUNTIF(B$1:B1,A2:A$7),0)),"")
Click to expand...

Actually, the OP is looking for a list of values that occur once within
the range, not a list of unique values.

But I like the IF statement. I'll have to make a note of it.
[I just figured out that both ranges could be anchored at one end,
floating at the other.]
Click to expand...

Yep, makes an already expensive formula less so.
 
H

Harlan Grove

Domenic wrote...
....
Actually, the OP is looking for a list of values that occur once within
the range, not a list of unique values.
Click to expand...
....

Good point. All array formulas.

B1:
=IF(OR(COUNTIF(A1:A7,A1:A7)=1),
INDEX(A1:A7,MATCH(1,COUNTIF(A1:A7,A1:A7),0)),"")

B2:
=IF(SUMPRODUCT(--(COUNTIF(A$1:A$7,A$1:A$7)=1))>=ROWS(A$1:A2),
INDEX(A$1:A$7,MATCH(1,COUNTIF(A$1:A$7,IF(COUNTIF(C$1:C1,A$1:A$7)=0,
A$1:A$7)),0)),"")

B2 filled down as needed.
 
D

Domenic

Harlan Grove said:
B2:
=IF(SUMPRODUCT(--(COUNTIF(A$1:A$7,A$1:A$7)=1))>=ROWS(A$1:A2),
INDEX(A$1:A$7,MATCH(1,COUNTIF(A$1:A$7,IF(COUNTIF(C$1:C1,A$1:A$7)=0,
A$1:A$7)),0)),"")

B2 filled down as needed.
Click to expand...

Nice one, Harlan!

I take it that you meant...

COUNTIF(B$1:B1,A$1:A$7)=0

....and maybe...

ROWS(B$2:B2)
 
Top