Ascending order

[Winehouse]

Hi

I’ve got a column of numbers starting in N4 for example

7
1
25
22
34
30

In the next column starting in O4, i’d like to place them in ascending order

1
7
22
25
30
34

I can’t work out how to do this.

Any help is very much appreciated.

Regards

 

[Marcelo Branco]

O4 copied down
=SMALL(N$4:N$9,ROWS(O$4:O4))

M.

 

[Winehouse]

Perfect. Thank you.

Could I ask another question?

In a header row, O3, i’d like to display the next lowest number yet to be displayed. In this case 2.

When 2 appears, then it should say 3.

Thank you for your help.

 

[Marcelo Branco]

Not sure i understand what you want in O3, maybe...

O3
=O4+1

M.

 

[Winehouse]

Sorry, I didn’t explain it well.

So the next 10 numbers entered for example where N is the next number entered & O3 is what should be displayed in O3 when the number is entered.

N—O3
2—2
4—2
8—2
11–2
20–2
3—4
5—5
6—8
9—9
10–11

 

[Marcelo Branco]

I'm confused...
Did you mean when the user?
enters 2 at N4 then O3 should be = 2
enters 4 at N5 then O3 should be = 2
enters 8 at N6 then O3 should be = 2
.....
enters 3 in N9 then O3 should be = 4
...
enters 10 in N13 then O3 should be = 11

What is the logic?

M.

 

[Winehouse]

So the first numbers in the first example were

1
7
22
25
30
34

So O3 would be 1

Then, in order

N—O3
2—2
4—2
8—2
11–2
20–2
3—4
5—5
6—8
9—9
10–11

So, i’d like O3 to display the lowest number currently entered.

Apologies for not being clearer. I should have entered the numbers from the previous post.

 

[Marcelo Branco]

hmm ... still confusing. Better start from scratch...

So at start we have this

[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

N​

[/TD]
[TD="bgcolor: #DCE6F1"]

O​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD][/TD]
[TD]

1​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4​

[/TD]
[TD]

7​

[/TD]
[TD]

1​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

5​

[/TD]
[TD]

1​

[/TD]
[TD]

7​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

6​

[/TD]
[TD]

25​

[/TD]
[TD]

22​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

7​

[/TD]
[TD]

22​

[/TD]
[TD]

25​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

8​

[/TD]
[TD]

34​

[/TD]
[TD]

30​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

9​

[/TD]
[TD]

30​

[/TD]
[TD]

34​

[/TD]
[/TR]
</tbody>[/TABLE]


Formula in N4 copied down (see post 2)
O3 = 1 (smallest value) Ok, I understand

Are you saying that when user inserts 2 in N10 then O3 should change to 2? Why if 1 is smaller than 2?

I'm not following you...

M.

 

[glfiedler]

I think the Op wants O3 to be the next number in sequence that is not already in the data list. So if the user entered 2 in N10 O3 would be 3, the next larger number not in the data.

 

[Winehouse]

Sorry, I thought I replied to this.

And apologies for my shocking description of what I’m looking for!

I’ll try again. In the quoted example below.

After the first 6 numbers starting with 1, O3 will be 1.

The next number is 2. O3 is 2.

Then numbers 4, 8, 11 & 20 are entered. O3 is still 2.

Then number 3 is entered. O3 changes to 4 as the numbers 1, 2, 3 & 4 have been entered.

Then 5 is entered. O3 becomes 5.

Then 6 is entered. O3 becomes 8 as 1-8 has already been entered.

And so on.

I hope this helps!

Thank you

So the first numbers in the first example were

1
7
22
25
30
34

So O3 would be 1

Then, in order

N—O3
2—2
4—2
8—2
11–2
20–2
3—4
5—5
6—8
9—9
10–11

So, i’d like O3 to display the lowest number currently entered.

Apologies for not being clearer. I should have entered the numbers from the previous post.