Prev: Average a column containing numbers and text
Next: Automate a Solver solution
From: Max on 22 May 2010 22:16
Discard the previous formula (the one using RANK)
Now here, you have the criteria formula in R7:R20
In D7:
=IF(ROWS($1:1)>COUNT($R$7:$R$20),"",INDEX($O$7:$O$20,MATCH(SMALL($R$7:$R$20,ROWS($1:1)),$R$7:$R$20,0)))
Copy down to D20 to return the auto full ascending sort
--
Max
Singapore
---
From: Matlock on 22 May 2010 22:51
Added it down from D7:D20. It is listing Silsbee as 1st - which is correct.
But Silsbee is listed all the way down to D20.

Where should be $1:1s be corrisponding to? in the formula?

=IF(ROWS($1:1)>COUNT($R$7:$R$20),"",INDEX($O$7:$O$20,MATCH(SMALL($R$7:$R$20,ROWS($1:1)),$R$7:$R$20,0)))

From: Max on 23 May 2010 02:46
I'm not sure what is still tripping you up over there. It should have worked
just as well for you. Check again that you have done this ..
In R2: =IF(D7="","",D7+ROW()/10^10)
R2 is copied down to R20

In D2:
=IF(ROWS($1:1)>COUNT($R$7:$R$20),"",INDEX($O$7:$O$20,MATCH(SMALL($R$7:$R$20,ROWS($1:1)),$R$7:$R$20,0)))
D2 is copied down to D20

Ensure that calc mode is set to auto. ROWS($1:1) is just an incrementer
which returns the series: 1, 2, 3, .. when you copy it down from the start
cell.
--
Max
Singapore
---
"Matlock" wrote:
> Added it down from D7:D20. It is listing Silsbee as 1st - which is correct.
> But Silsbee is listed all the way down to D20.
>
> Where should be $1:1s be corrisponding to? in the formula?
>
> =IF(ROWS($1:1)>COUNT($R$7:$R$20),"",INDEX($O$7:$O$20,MATCH(SMALL($R$7:$R$20,ROWS($1:1)),$R$7:$R$20,0)))
>
From: Max on 23 May 2010 03:02
Sorry, this part in the earlier should read as (following your set-up):
In R2: =IF(Q7="","",Q7+ROW()/10^10)
R2 is copied down to R20
--
Max
Singapore
---
From: Matlock on 23 May 2010 22:33
I am not sure where I messed the formula at or what I did wrong, but your
Formulas worked flawlessly. Thank you very much, Max. I am definitely putting
you on the source information for the excel book for thanks.

I'll be back when I need help with other formulas!

Thanks!
First  |  Prev  |  Next  |  Last
Pages: 1 2 3
Prev: Average a column containing numbers and text
Next: Automate a Solver solution