Ranked list

[gmunro]

Hello,

Based on the following example:

Item Quantity
A 2
S 7
D 3
F 6

I am looking for a ranking formula that will pull the data from the
first column based on the ranking of the second column
so that the end result will look like this
1 S
2 F
3 D
As S has the largest quantity, F 2nd largest etc

The list I will pull this from is variable in length but in the
hundreds.

Any help would be appreciated.

Glen

[Ian]

Can't you use Data|Sort|Descending on the Quantity?

--
Ian
--
"gmunro" <[email protected]> wrote in message
news:[email protected]...
> Hello,
>
> Based on the following example:
>
> Item Quantity
> A 2
> S 7
> D 3
> F 6
>
> I am looking for a ranking formula that will pull the data from the
> first column based on the ranking of the second column
> so that the end result will look like this
> 1 S
> 2 F
> 3 D
> As S has the largest quantity, F 2nd largest etc
>
> The list I will pull this from is variable in length but in the
> hundreds.
>
> Any help would be appreciated.
>
> Glen
>

[gmunro]

I was hoping to pull a separate list, rather than running a data sort
(as easy as that is) and am relatively new to the Rank function. I
know I could do this with inserting a column and adding a vlookup, but
that will complicate other macros I have going on with this document.

With that, I am wondering if there is a formula that will do it.

[Bob Phillips]

Enter this in C1, and copy down

=INDEX($A$1:$A$10,MATCH(ROW(),RANK($B$1:$B$10,$B$1:$B$10),0))

it is an array formula, so commit with Carol-Shift-Enter. Change the 10 to a
realistic number for you

--
HTH

Bob Phillips

"gmunro" <[email protected]> wrote in message
news:[email protected]...
> I was hoping to pull a separate list, rather than running a data sort
> (as easy as that is) and am relatively new to the Rank function. I
> know I could do this with inserting a column and adding a vlookup, but
> that will complicate other macros I have going on with this document.
>
> With that, I am wondering if there is a formula that will do it.
>

[Ian]

"Bob Phillips" <[email protected]> wrote in message
news:OIJ%[email protected]...
> it is an array formula, so commit with Carol-Shift-Enter. Change the 10 to
> a

Hi Bob

Who's Carol? :-)

--
Ian
--

[Bob Phillips]

She's the damned spell-checker <vbg>

--
HTH

Bob Phillips

"Ian" <[email protected]> wrote in message
news:[email protected]...
> "Bob Phillips" <[email protected]> wrote in message
> news:OIJ%[email protected]...
> > it is an array formula, so commit with Carol-Shift-Enter. Change the 10
to
> > a
>
> Hi Bob
>
> Who's Carol? :-)
>
> --
> Ian
> --
>
>

[Alan]

Perhaps
Carol-Singer-Enter as the Yuletide will be upon us soon?

"Bob Phillips" <[email protected]> wrote in message
news:O%[email protected]...
> She's the damned spell-checker <vbg>
>
> --
> HTH
>
> Bob Phillips
>
> "Ian" <[email protected]> wrote in message
> news:[email protected]...
>> "Bob Phillips" <[email protected]> wrote in message
>> news:OIJ%[email protected]...
>> > it is an array formula, so commit with Carol-Shift-Enter. Change the 10
> to
>> > a
>>
>> Hi Bob
>>
>> Who's Carol? :-)
>>
>> --
>> Ian
>> --
>>
>>
>
>

[Ian]

What's a spill-chucker?

--
Ian
--
"Bob Phillips" <[email protected]> wrote in message
news:O%[email protected]...
> She's the damned spell-checker <vbg>
>
> --
> HTH
>
> Bob Phillips
>
> "Ian" <[email protected]> wrote in message
> news:[email protected]...
>> "Bob Phillips" <[email protected]> wrote in message
>> news:OIJ%[email protected]...
>> > it is an array formula, so commit with Carol-Shift-Enter. Change the 10
> to
>> > a
>>
>> Hi Bob
>>
>> Who's Carol? :-)
>>
>> --
>> Ian
>> --
>>
>>
>
>

[Bob Phillips]

It's wat so me of us cannot liev without because we have got fat fimgers.

"Ian" <[email protected]> wrote in message
news:[email protected]...
> What's a spill-chucker?
>
> --
> Ian
> --
> "Bob Phillips" <[email protected]> wrote in message
> news:O%[email protected]...
> > She's the damned spell-checker <vbg>
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > "Ian" <[email protected]> wrote in message
> > news:[email protected]...
> >> "Bob Phillips" <[email protected]> wrote in message
> >> news:OIJ%[email protected]...
> >> > it is an array formula, so commit with Carol-Shift-Enter. Change the
10
> > to
> >> > a
> >>
> >> Hi Bob
> >>
> >> Who's Carol? :-)
> >>
> >> --
> >> Ian
> >> --
> >>
> >>
> >
> >
>
>

[Ian]

Oh no! I've created a monster! :-)


--
Ian
--
"Bob Phillips" <[email protected]> wrote in message
news:[email protected]...
> It's wat so me of us cannot liev without because we have got fat fimgers.
>
> "Ian" <[email protected]> wrote in message
> news:[email protected]...
>> What's a spill-chucker?
>>
>> --
>> Ian
>> --
>> "Bob Phillips" <[email protected]> wrote in message
>> news:O%[email protected]...
>> > She's the damned spell-checker <vbg>
>> >
>> > --
>> > HTH
>> >
>> > Bob Phillips
>> >
>> > "Ian" <[email protected]> wrote in message
>> > news:[email protected]...
>> >> "Bob Phillips" <[email protected]> wrote in message
>> >> news:OIJ%[email protected]...
>> >> > it is an array formula, so commit with Carol-Shift-Enter. Change the
> 10
>> > to
>> >> > a
>> >>
>> >> Hi Bob
>> >>
>> >> Who's Carol? :-)
>> >>
>> >> --
>> >> Ian
>> >> --
>> >>
>> >>
>> >
>> >
>>
>>
>
>