finding unique numbers

  • Thread starter Thread starter Glenn
  • Start date Start date
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.
 
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!
 
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.
 
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
 
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.]
 
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.
 
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.
 
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)
 
Back
Top