Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Index, Large and Match??
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Index, Large and Match??
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Index, Large and Match??
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Index, Large and Match??
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Index, Large and Match??
"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 --- |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Index, Large and Match??
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 --- |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Index, Large and Match??
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. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Index, Large and Match??
"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 --- |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Index, Large and Match??
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 --- * |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Index, Large and Match??
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 |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Index, Large and Match??
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 |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Index, Large and Match??
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
INDEX/MATCH/LARGE returning #VALUE! error | Excel Worksheet Functions | |||
index match array function-returning only first match, need last. | Excel Worksheet Functions | |||
Large Index Match Lookup | Excel Worksheet Functions | |||
Match, Index, Vlookup, Large....Help Please | Excel Worksheet Functions | |||
Match, Index, Vlookup, Large....Help Please | Excel Worksheet Functions |