Excel Broken?
 

My formulas:
in cell B1 = SMALL($A$1:$A:$10,1)
in cell B2 and down =TRUNC(SMALL(IF($A$1:$A$10B1,$A$1:$A
$10+0.001*ROW($A$1:$A$10)),1))

This is what i'm getting...

Data set:
A B
1) 10 10
2) 20 20
3) 30 30
4) 40 40
5) 50 50
6) 30 0
7) 20 20
8) 50 50
9) 60 60
10) 100 100

Looking at my formula you'll see that the results of B6 make no
sense. Can anyone explain this or help me fix this? I should be
getting: 10, 20, 30, 40, 50, 60, and finally 100 with 0's in the last
3 cells.

Other info, what i'm trying to do with this formula is extract unique
numbers (non-repeating) from a large data set. Some of the data in
column A will be repeating, but I only am interested in a condensed
list.

Thanks,
Daniel

Excel Broken?
 

Excel is not broken. It is giving you the correct answers based on your
formulas. I don't think your formulas are doing what you think they're
doing.
For example, ROW($A$1:$A$10) results in multiplication by 1 in every
instance of your formula. I suggest you look at your formulas using the
formula evaluator to see exactly what is happening.

Tyro

"Daniel" wrote in message
...
My formulas:
in cell B1 = SMALL($A$1:$A:$10,1)
in cell B2 and down =TRUNC(SMALL(IF($A$1:$A$10B1,$A$1:$A
$10+0.001*ROW($A$1:$A$10)),1))

This is what i'm getting...

Data set:
A B
1) 10 10
2) 20 20
3) 30 30
4) 40 40
5) 50 50
6) 30 0
7) 20 20
8) 50 50
9) 60 60
10) 100 100

Looking at my formula you'll see that the results of B6 make no
sense. Can anyone explain this or help me fix this? I should be
getting: 10, 20, 30, 40, 50, 60, and finally 100 with 0's in the last
3 cells.

Other info, what i'm trying to do with this formula is extract unique
numbers (non-repeating) from a large data set. Some of the data in
column A will be repeating, but I only am interested in a condensed
list.

Thanks,
Daniel

Excel Broken?
 

No, Excel is not broken
You need to commit the B2 and later formulas with CTRL+SHIFT+ENTER since
they are array formulas (in the Formula Bar you will then see {formula} --
Excel adds the braces)
The last three give a #NUM! error
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Daniel" wrote in message
...
My formulas:
in cell B1 = SMALL($A$1:$A:$10,1)
in cell B2 and down =TRUNC(SMALL(IF($A$1:$A$10B1,$A$1:$A
$10+0.001*ROW($A$1:$A$10)),1))

This is what i'm getting...

Data set:
A B
1) 10 10
2) 20 20
3) 30 30
4) 40 40
5) 50 50
6) 30 0
7) 20 20
8) 50 50
9) 60 60
10) 100 100

Looking at my formula you'll see that the results of B6 make no
sense. Can anyone explain this or help me fix this? I should be
getting: 10, 20, 30, 40, 50, 60, and finally 100 with 0's in the last
3 cells.

Other info, what i'm trying to do with this formula is extract unique
numbers (non-repeating) from a large data set. Some of the data in
column A will be repeating, but I only am interested in a condensed
list.

Thanks,
Daniel

Excel Broken?
 

When I enter the formula in B2 as an array formula in B2 through B10, I get
the answer 20, which is the first smallest value in the array, in B2 through
B10

Tyro
..
"Bernard Liengme" wrote in message
...
No, Excel is not broken
You need to commit the B2 and later formulas with CTRL+SHIFT+ENTER since
they are array formulas (in the Formula Bar you will then see {formula} --
Excel adds the braces)
The last three give a #NUM! error
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Daniel" wrote in message
...
My formulas:
in cell B1 = SMALL($A$1:$A:$10,1)
in cell B2 and down =TRUNC(SMALL(IF($A$1:$A$10B1,$A$1:$A
$10+0.001*ROW($A$1:$A$10)),1))

This is what i'm getting...

Data set:
A B
1) 10 10
2) 20 20
3) 30 30
4) 40 40
5) 50 50
6) 30 0
7) 20 20
8) 50 50
9) 60 60
10) 100 100

Looking at my formula you'll see that the results of B6 make no
sense. Can anyone explain this or help me fix this? I should be
getting: 10, 20, 30, 40, 50, 60, and finally 100 with 0's in the last
3 cells.

Other info, what i'm trying to do with this formula is extract unique
numbers (non-repeating) from a large data set. Some of the data in
column A will be repeating, but I only am interested in a condensed
list.

Thanks,
Daniel

Excel Broken?
 

Try this:

=IF(ISERR(SMALL(IF(MATCH($A$1:$A$10,$A$1:$A$10,0)= ROW($A$1:$A$10),MATCH($A$1:$A$10,$A$1:$A$10,0)),RO WS($1:1))),0,INDEX($A$1:$A$10,SMALL(IF(MATCH($A$1: $A$10,$A$1:$A$10,0)=ROW($A$1:$A$10),MATCH($A$1:$A$ 10,$A$1:$A$10,0)),ROWS($1:1))))

ctrl+shift+enter, not just enter
copy down as far as needed


"Daniel" wrote:

My formulas:
in cell B1 = SMALL($A$1:$A:$10,1)
in cell B2 and down =TRUNC(SMALL(IF($A$1:$A$10B1,$A$1:$A
$10+0.001*ROW($A$1:$A$10)),1))

This is what i'm getting...

Data set:
A B
1) 10 10
2) 20 20
3) 30 30
4) 40 40
5) 50 50
6) 30 0
7) 20 20
8) 50 50
9) 60 60
10) 100 100

Looking at my formula you'll see that the results of B6 make no
sense. Can anyone explain this or help me fix this? I should be
getting: 10, 20, 30, 40, 50, 60, and finally 100 with 0's in the last
3 cells.

Other info, what i'm trying to do with this formula is extract unique
numbers (non-repeating) from a large data set. Some of the data in
column A will be repeating, but I only am interested in a condensed
list.

Thanks,
Daniel

Excel Broken?
 

On Feb 7, 1:30 pm, Teethless mama
wrote:
Try this:

=IF(ISERR(SMALL(IF(MATCH($A$1:$A$10,$A$1:$A$10,0)= ROW($A$1:$A$10),MATCH($A$1:$A$10,$A$1:$A$10,0)),RO WS($1:1))),0,INDEX($A$1:$A$10,SMALL(IF(MATCH($A$1: $A$10,$A$1:$A$10,0)=ROW($A$1:$A$10),MATCH($A$1:$A$ 10,$A$1:$A$10,0)),ROWS($1:1))))

ctrl+shift+enter, not just enter
copy down as far as needed

"Daniel" wrote:
My formulas:
in cell B1 = SMALL($A$1:$A:$10,1)
in cell B2 and down =TRUNC(SMALL(IF($A$1:$A$10B1,$A$1:$A
$10+0.001*ROW($A$1:$A$10)),1))

This is what i'm getting...

Data set:
A B
1) 10 10
2) 20 20
3) 30 30
4) 40 40
5) 50 50
6) 30 0
7) 20 20
8) 50 50
9) 60 60
10) 100 100
The ctrl+shift+enter worked! I never knew that. Thanks!

Now to clean it up I'll just have to remove the #num error...


Looking at my formula you'll see that the results of B6 make no
sense. Can anyone explain this or help me fix this? I should be
getting: 10, 20, 30, 40, 50, 60, and finally 100 with 0's in the last
3 cells.

Other info, what i'm trying to do with this formula is extract unique
numbers (non-repeating) from a large data set. Some of the data in
column A will be repeating, but I only am interested in a condensed
list.

Thanks,
Daniel

Excel Broken?
 

Your formula in B2 could be entered as an array formula and then copied
down:

In versions of Excel prior to 2007:

=IF(ISERROR(SMALL(IF($A$1:$A$10B1,$A$1:$A$10),1)) ,"",SMALL(IF($A$1:$A$10B1,$A$1:$A$10),1))

In Excel 2007:

=IFERROR(SMALL(IF($A$1:$A$10B1,$A$1:$A$10),1),"")

Tyro


"Daniel" wrote in message
...
My formulas:
in cell B1 = SMALL($A$1:$A:$10,1)
in cell B2 and down =TRUNC(SMALL(IF($A$1:$A$10B1,$A$1:$A
$10+0.001*ROW($A$1:$A$10)),1))

This is what i'm getting...

Data set:
A B
1) 10 10
2) 20 20
3) 30 30
4) 40 40
5) 50 50
6) 30 0
7) 20 20
8) 50 50
9) 60 60
10) 100 100

Looking at my formula you'll see that the results of B6 make no
sense. Can anyone explain this or help me fix this? I should be
getting: 10, 20, 30, 40, 50, 60, and finally 100 with 0's in the last
3 cells.

Other info, what i'm trying to do with this formula is extract unique
numbers (non-repeating) from a large data set. Some of the data in
column A will be repeating, but I only am interested in a condensed
list.

Thanks,
Daniel

Excel Broken?
 

Wouldn't it be easier to just use Data - Filter - Advanced Filter and extract
all of the unique records that meet a certain criteria (if any) if you want a
subset of unique numbers?

"Daniel" wrote:

My formulas:
in cell B1 = SMALL($A$1:$A:$10,1)
in cell B2 and down =TRUNC(SMALL(IF($A$1:$A$10B1,$A$1:$A
$10+0.001*ROW($A$1:$A$10)),1))

This is what i'm getting...

Data set:
A B
1) 10 10
2) 20 20
3) 30 30
4) 40 40
5) 50 50
6) 30 0
7) 20 20
8) 50 50
9) 60 60
10) 100 100

Looking at my formula you'll see that the results of B6 make no
sense. Can anyone explain this or help me fix this? I should be
getting: 10, 20, 30, 40, 50, 60, and finally 100 with 0's in the last
3 cells.

Other info, what i'm trying to do with this formula is extract unique
numbers (non-repeating) from a large data set. Some of the data in
column A will be repeating, but I only am interested in a condensed
list.

Thanks,
Daniel

Excel Broken?
 

Thank you Tyro.

Well you could use the filter command, but the point is to automate
the whole procedure.

On Feb 7, 4:02 pm, MrAcquire
wrote:
Wouldn't it be easier to just use Data - Filter - Advanced Filter and extract
all of the unique records that meet a certain criteria (if any) if you want a
subset of unique numbers?

"Daniel" wrote:
My formulas:
in cell B1 = SMALL($A$1:$A:$10,1)
in cell B2 and down =TRUNC(SMALL(IF($A$1:$A$10B1,$A$1:$A
$10+0.001*ROW($A$1:$A$10)),1))

This is what i'm getting...

Data set:
A B
1) 10 10
2) 20 20
3) 30 30
4) 40 40
5) 50 50
6) 30 0
7) 20 20
8) 50 50
9) 60 60
10) 100 100

Looking at my formula you'll see that the results of B6 make no
sense. Can anyone explain this or help me fix this? I should be
getting: 10, 20, 30, 40, 50, 60, and finally 100 with 0's in the last
3 cells.

Other info, what i'm trying to do with this formula is extract unique
numbers (non-repeating) from a large data set. Some of the data in
column A will be repeating, but I only am interested in a condensed
list.

Thanks,
Daniel

Excel Broken?
 

.. what i'm trying to do .. is extract unique numbers
(non-repeating) from a large data set.

If that data set is running in A1 down,

In B1:
=IF(A1="","",IF(COUNTIF($A$1:A1,A1)1,"",ROW()))

In C1:
=IF(ROW()COUNT(C:C),"",INDEX(A:A,SMALL(C:C,ROW()) ))
Select B1:C1, copy down to cover the max expected extent of data in col A.
Minimize/hide col B. Col C will auto-return the required list of uniques
from col A, all neatly bunched at the top.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Excel Broken?
 

That is beautifully simple code. The adjustment I made is to extract
the actual numbers and not rows, ie:
In B1:
=IF(A1="","",IF(COUNTIF($A$1:A1,A1)1,"",A1))

In C1:
=IF(ROW()COUNT($B$1:$B$29),"",SMALL($B$1:$B$29,RO W()))

The issue come up when you have data starting on Row X other than 1...


On Feb 8, 6:27 am, "Max" wrote:
.. what i'm trying to do .. is extract unique numbers
(non-repeating) from a large data set.

If that data set is running in A1 down,

In B1:
=IF(A1="","",IF(COUNTIF($A$1:A1,A1)1,"",ROW()))

In C1:
=IF(ROW()COUNT(C:C),"",INDEX(A:A,SMALL(C:C,ROW()) ))
Select B1:C1, copy down to cover the max expected extent of data in col A.
Minimize/hide col B. Col C will auto-return the required list of uniques
from col A, all neatly bunched at the top.
--
Max
Singaporehttp://savefile.com/projects/236895
xdemechanik
---

Excel Broken?
 

Sorry, there was an error earlier in this line
In C1:
=IF(ROW()COUNT(C:C),"",INDEX(A:A,SMALL(C:C,ROW()) ))

In C1 should be:
=IF(ROW()COUNT(B:B),"",INDEX(A:A,SMALL(B:B,ROW()) ))

You're right that some adjustments are needed should the source
data/extractions start in other than row1 down
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Daniel" wrote in message
...
That is beautifully simple code. The adjustment I made is to extract
the actual numbers and not rows, ie:
In B1:
=IF(A1="","",IF(COUNTIF($A$1:A1,A1)1,"",A1))

In C1:
=IF(ROW()COUNT($B$1:$B$29),"",SMALL($B$1:$B$29,RO W()))

The issue come up when you have data starting on Row X other than 1...