ExcelBanter - wich formula

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - wich formula (https://www.excelbanter.com/excel-worksheet-functions/108677-wich-formula.html)

Hi.
I need a formula that will read the items from locked cells A20 and B20 and
put them sorted into A1 and B1
A1 B1
PEN5 50%
PEN4 40%
PEN3 30%
PEN1 10%
PEN6 8%
PEN2 2%
------------
A20 B20
PEN1 10%
PEN2 2%
PEN3 30%
PEN4 40%
PEN5 50%
PEN6 8%
Thank you.
George...

Here's a way using non-array formulas to produce
the required auto-descending sorted range above the source range

A working sample is available at:
http://www.savefile.com/files/37534
Auto sorted range above source range.xls

Source data assumed within A20:B37 (18 lines, say)

In A1:
=IF(ROW()COUNT($C$1:$C$18),"",INDEX(A$20:A$37,MAT CH(LARGE($C$1:$C$18,ROW()),$C$1:$C$18,0)))
Copy A1 to B1. Format B1 as percentage.

In C1:
=IF(B20="","",B20-ROW()/10^10)

Select A1:C1, fill down to C18 (cover the same extent as the source data).
Hide away col C if desired, or mask the font to suit the fill.

A1:B18 will auto-return the source data in the required descending sort by
percentage. In the event of any ties in the percentages, tied items will
appear in the same relative order that they are within the source range.
Adapt to suit.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"George" wrote:
Hi.
I need a formula that will read the items from locked cells A20 and B20 and
put them sorted into A1 and B1
A1 B1
PEN5 50%
PEN4 40%
PEN3 30%
PEN1 10%
PEN6 8%
PEN2 2%
------------
A20 B20
PEN1 10%
PEN2 2%
PEN3 30%
PEN4 40%
PEN5 50%
PEN6 8%
Thank you.
George...

Anybody Please...

"George" wrote in message
.. .
Hi.
I need a formula that will read the items from locked cells A20 and B20
and put them sorted into A1 and B1
A1 B1
PEN5 50%
PEN4 40%
PEN3 30%
PEN1 10%
PEN6 8%
PEN2 2%
------------
A20 B20
PEN1 10%
PEN2 2%
PEN3 30%
PEN4 40%
PEN5 50%
PEN6 8%
Thank you.
George...

"George" wrote:
Anybody Please...

?? .. I ventured a response 4 days ago,
http://tinyurl.com/okdv3
There's even a nicely rendered sample file to complement (link given)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Possibility the MS servers may be acting up again Max.

I can't find a couple of my posts from some threads from just yesterday
without going into Google.

I do see your post in this thread though.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Max" wrote in message
...
"George" wrote:
Anybody Please...

?? .. I ventured a response 4 days ago,
http://tinyurl.com/okdv3
There's even a nicely rendered sample file to complement (link given)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

"Ragdyer" wrote:
Possibility the MS servers may be acting up again Max.
I can't find a couple of my posts from some threads from just yesterday
without going into Google.

Ay, that's exactly what I had to do too, a number of times of late <g

I do see your post in this thread though.
Thanks for the confirmation !

--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---