Index, Large and Match??

[Barb Reinhardt]

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/microsoft.public.excel.worksheet.functions/msg/827b34ad797f6404

Any suggestions?

Thanks,
Barb Reinhardt

 

[Max]

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]

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

 

[RagDyer]

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?

 

[Max]

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
---

 

[Max]

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
---

 

[Spiky]

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.

 

[Max]

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
---

 

[Pete_UK]

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

 

[Max]

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
---
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

 

[David Biddulph]

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

 

[Spiky]

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.