I have two named ranges, Last12Function and Last12Count. What I want to do
pull the data out in order of the highest to lowest values.

I already have determined the "Count" for the top to the bottom, but some of
the counts are duplicates and I'm not sure how to get the match back to the
function.

This is what I have so far, but it only works if there is only one
observation with the given count.

=IF(F9=1,INDEX(Last12Function,MATCH(E9,Last12Count ,0)))

E9 is the value that I get from the "LARGE" function
F9 is the COUNT of the values for the LARGE function.

I don't have anything if the count is 2 or more.

Keep in mind that my data does not start at row 1. It currently starts at
row 9 and that is potentially variable.

I have tried this without any success.,
http://groups.google.com/group/micro...7b34ad797f6404

Any suggestions?

Thanks,
Barb Reinhardt

Perhaps an example to illustrate a simple non-array formulas play to
auto-sort source lines in descending order by a certain key col (eg: scores
col) where there's possibility of ties/multiple ties

Assuming source data is within A2:B10, where col A contains names, col B
contains the corresponding scores. Assume data is unsorted and there could be
ties/multiple ties in the scores in col B

In C2:
=IF(B2="","",B2-ROWS($1:1)/10^10)

In D2:
=IF(ROWS($1:1)COUNT($C$2:$C$10),"",INDEX(A$2:A$10 ,MATCH(LARGE($C$2:$C$10,ROWS($1:1)),$C$2:$C$10,0)) )

Copy D2 to E2. Select C2:E2, copy down to E10. Minimize/hide away col C.
Cols D & E will return the automatic sort in descending order of cols A and
B, by the scores in col B. Tied scores, if any, will be returned in the same
relative order that they appear within the source. Adapt to suit.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:16,000 Files:354 Subscribers:53
xdemechanik
---
"Barb Reinhardt" wrote:
I have two named ranges, Last12Function and Last12Count. What I want to do
pull the data out in order of the highest to lowest values.

I already have determined the "Count" for the top to the bottom, but some of
the counts are duplicates and I'm not sure how to get the match back to the
function.

This is what I have so far, but it only works if there is only one
observation with the given count.

=IF(F9=1,INDEX(Last12Function,MATCH(E9,Last12Count ,0)))

E9 is the value that I get from the "LARGE" function
F9 is the COUNT of the values for the LARGE function.

I don't have anything if the count is 2 or more.

Keep in mind that my data does not start at row 1. It currently starts at
row 9 and that is potentially variable.

I have tried this without any success.,
http://groups.google.com/group/micro...7b34ad797f6404

Any suggestions?

Thanks,
Barb Reinhardt

The problem is that my data is in row 9 or so and beyond. How would you
modify this for that?
--
HTH,
Barb Reinhardt



"Max" wrote:

Perhaps an example to illustrate a simple non-array formulas play to
auto-sort source lines in descending order by a certain key col (eg: scores
col) where there's possibility of ties/multiple ties

Assuming source data is within A2:B10, where col A contains names, col B
contains the corresponding scores. Assume data is unsorted and there could be
ties/multiple ties in the scores in col B

In C2:
=IF(B2="","",B2-ROWS($1:1)/10^10)

In D2:
=IF(ROWS($1:1)COUNT($C$2:$C$10),"",INDEX(A$2:A$10 ,MATCH(LARGE($C$2:$C$10,ROWS($1:1)),$C$2:$C$10,0)) )

Copy D2 to E2. Select C2:E2, copy down to E10. Minimize/hide away col C.
Cols D & E will return the automatic sort in descending order of cols A and
B, by the scores in col B. Tied scores, if any, will be returned in the same
relative order that they appear within the source. Adapt to suit.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:16,000 Files:354 Subscribers:53
xdemechanik
---
"Barb Reinhardt" wrote:
I have two named ranges, Last12Function and Last12Count. What I want to do
pull the data out in order of the highest to lowest values.

I already have determined the "Count" for the top to the bottom, but some of
the counts are duplicates and I'm not sure how to get the match back to the
function.

This is what I have so far, but it only works if there is only one
observation with the given count.

=IF(F9=1,INDEX(Last12Function,MATCH(E9,Last12Count ,0)))

E9 is the value that I get from the "LARGE" function
F9 is the COUNT of the values for the LARGE function.

I don't have anything if the count is 2 or more.

Keep in mind that my data does not start at row 1. It currently starts at
row 9 and that is potentially variable.

I have tried this without any success.,
http://groups.google.com/group/micro...7b34ad797f6404

Any suggestions?

Thanks,
Barb Reinhardt

I'm confused by your OP.

So, let's start in this manner.

Column A is text,
Column B is numbers.

You want to put the numbers in numerical order, descending.
You want to match the text in Column A to the sorted numbers.

Last12Function = A9 to A20
Last12Count = B9 to B20


In C9 enter this formula:
=LARGE(B$9:B$20,ROWS($1:1))

In D9 enter this *array* formula:
=INDEX(A$9:A$20,SMALL(IF(B$9:B$20=C9,ROW($1:$12)), COUNTIF(C9:C$20,C9)))

--
Array formulas must be entered with CSE, <Ctrl <Shift <Enter, instead of
the regular <Enter, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually. Also, you must use CSE when
revising the formula.

*After* the CSE entry of D9, select *both* C9 and D9,
and drag down to copy that 2 cell selection to row 20.

NOW ... what in the above dialog do you need changed to match your scenario?
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------


"Barb Reinhardt" wrote in message
...
The problem is that my data is in row 9 or so and beyond. How would you
modify this for that?
--
HTH,
Barb Reinhardt



"Max" wrote:

Perhaps an example to illustrate a simple non-array formulas play to
auto-sort source lines in descending order by a certain key col (eg:
scores
col) where there's possibility of ties/multiple ties

Assuming source data is within A2:B10, where col A contains names, col B
contains the corresponding scores. Assume data is unsorted and there
could be
ties/multiple ties in the scores in col B

In C2:
=IF(B2="","",B2-ROWS($1:1)/10^10)

In D2:
=IF(ROWS($1:1)COUNT($C$2:$C$10),"",INDEX(A$2:A$10 ,MATCH(LARGE($C$2:$C$10,ROWS($1:1)),$C$2:$C$10,0)) )

Copy D2 to E2. Select C2:E2, copy down to E10. Minimize/hide away col C.
Cols D & E will return the automatic sort in descending order of cols A
and
B, by the scores in col B. Tied scores, if any, will be returned in the
same
relative order that they appear within the source. Adapt to suit.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:16,000 Files:354 Subscribers:53
xdemechanik
---
"Barb Reinhardt" wrote:
I have two named ranges, Last12Function and Last12Count. What I want
to do
pull the data out in order of the highest to lowest values.

I already have determined the "Count" for the top to the bottom, but
some of
the counts are duplicates and I'm not sure how to get the match back to
the
function.

This is what I have so far, but it only works if there is only one
observation with the given count.

=IF(F9=1,INDEX(Last12Function,MATCH(E9,Last12Count ,0)))

E9 is the value that I get from the "LARGE" function
F9 is the COUNT of the values for the LARGE function.

I don't have anything if the count is 2 or more.

Keep in mind that my data does not start at row 1. It currently starts
at
row 9 and that is potentially variable.

I have tried this without any success.,
http://groups.google.com/group/micro...7b34ad797f6404

Any suggestions?

Thanks,
Barb Reinhardt

"Barb Reinhardt" wrote:
The problem is that my data is in row 9 or so and beyond.
How would you modify this for that?

Like this, assume source data in A9:B100

In C9:
=IF(B9="","",B9-ROWS($1:1)/10^10)

In D9:
=IF(ROWS($1:1)COUNT($C$9:$C$100),"",
INDEX(A$9:A$100,MATCH(LARGE($C$9:$C$100,ROWS($1:1) ),$C$9:$C$100,0)))
Copy D9 to E9. Select C9:E9, copy down to E100. The autosort will be in
D9:E100, all lines neatly bunched at the top.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:16,000 Files:354 Subscribers:53
xdemechanik
---

Perhaps you could post a closure, Barb? So that this thread doesn't appear
hung in midstream, unlike (lament) so many others
--
Max
Singapore
http://savefile.com/projects/236895
Downloads: 16,200, Files: 354, Subscribers: 53
xdemechanik
---

On Jul 23, 12:28 am, "Max" wrote:
Perhaps you could post a closure, Barb? So that this thread doesn't appear
hung in midstream, unlike (lament) so many others
--
Max
Singaporehttp://savefile.com/projects/236895
Downloads: 16,200, Files: 354, Subscribers: 53
xdemechanik
---

I've wondered....how do you access this? I see some requesting this,
but my access through Google doesn't show any Solved/Unsolved or
whatever you see.

"Spiky" wrote
I've wondered....how do you access this? I see some requesting this,
but my access through Google doesn't show any Solved/Unsolved or
whatever you see.

I'm not sure what you mean

"Closure" was refering to ...
Many thread discussions end prematurely w/o any closure from OPs
Whether response(s) posted helped with the issue(s) raised is left open
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:16,200 Files:354 Subscribers:53
xdemechanik
---

Hi Max,

I think Spiky meant that he has seen you and others ask OPs to click
"Yes" to indicate that the post has been answered, but this does not
show in Google Groups.

Pete

On Jul 24, 12:07*am, "Max" wrote:
"Spiky" wrote

I've wondered....how do you access this? I see some requesting this,
but my access through Google doesn't show any Solved/Unsolved or
whatever you see.

I'm not sure what you mean

"Closure" was refering to ...
Many thread discussions end prematurely w/o any closure from OPs
Whether response(s) posted helped with the issue(s) raised is left open
--
Max
Singaporehttp://savefile.com/projects/236895
Downloads:16,200 Files:354 Subscribers:53
xdemechanik
--- *

Ah, I see. That's the facility in MS' webpages
Google has it's own equivalent "stars" rating
But it's always good for OPs to post a closure note besides clicking the
rating
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:16,200 Files:354 Subscribers:53
xdemechanik
---
"Pete_UK" wrote in message
...
Hi Max,

I think Spiky meant that he has seen you and others ask OPs to click
"Yes" to indicate that the post has been answered, but this does not
show in Google Groups.

Pete

More significantly, it doesn't show when using a newsreader to access the
newsgroup.
--
David Biddulph

"Pete_UK" wrote in message
...
Hi Max,

I think Spiky meant that he has seen you and others ask OPs to click
"Yes" to indicate that the post has been answered, but this does not
show in Google Groups.

Pete

On Jul 24, 12:07 am, "Max" wrote:
"Spiky" wrote

I've wondered....how do you access this? I see some requesting this,
but my access through Google doesn't show any Solved/Unsolved or
whatever you see.

I'm not sure what you mean

"Closure" was refering to ...
Many thread discussions end prematurely w/o any closure from OPs
Whether response(s) posted helped with the issue(s) raised is left open

On Jul 23, 6:49 pm, "Max" wrote:
Ah, I see. That's the facility in MS' webpages
Google has it's own equivalent "stars" rating
But it's always good for OPs to post a closure note besides clicking the
rating
That was it. Thanks for the info.