Array question?

[mjk1chi]

Thanks!
I have a list of data associated with a group of people. The sheet is an aggregation of weekly data, there are 52 supporting spreadsheet tabs - week 1, week 2, week 3, etc.
So for example, in the "Total" tab (and in each other tab), Column A lists Adams (cell A1), Baker (cell A2), Charles (etc.), Davis, Evans down to Zavala (26 people)
Column B lists sales (number, not dollars for B-F)
Column C lists assists
Column D lists generations
Column E lists closes
Column F lists losses
Column G -K lists dollar values associasted with each of Columns B-F
I would like to generate a list of "Leaders" from among the 26 people for each of these 10 data points (i.e., the 10 data points measured for each of 26 people in columns B-K).
How do I do that? I have a vague idea that it is using arrays, but I havent used arrays in the past.
Thanks!!!

Did I mention - Thanks!!

Mike
xxxxxxxxx@yahoo.com

 

[Somendra Misra]

Hi Mike,

See the attached file. Yellow cells has formula. Not sure this meets your requirement. Press F9 to see how the formula will give different results for different value.

If there is a change in a sheet layout. Just upload a sample file.

Regards,

 

[mjk1chi]

Hi Mike,

See the attached file. Yellow cells has formula. Not sure this meets your requirement. Press F9 to see how the formula will give different results for different value.

If there is a change in a sheet layout. Just upload a sample file.

Regards,

Thanks!!!

What is I want to create a leader list? See the attached and look at the desired outcome in pink.

Thanks!!!

Mike

 

[Jon von der Heyden]

Using your sample file:

1. In M12:M16 enter the series of numbers {1;2;3;4;5}
2. Select O12:O16 and enter this formula: =LARGE($B$2:$B$27,M12:M16)
3. Commit the formula in #2 with Control+Shift+Enter (not just enter)
4. Enter this formula in N12: =INDEX($A$1:$A$27,SMALL(IF($B$1:$B$27=O12,ROW($A$1:$A$27)),COUNTIF($O$12:$O12,"="&O12)),1)
5. Commit the formula in #4 with Control+Shift+Enter (not just enter)
6. Copy the formula in N12 and paste to N13:N16

Sample file attached.

 

[mjk1chi]

Thanks!

Works in part on my data but not in full so I am missing something.

My data in green

It is definitely finding the correct 1-5 leaders, but not associating the correct name with the data.

Help?

Thanks!

Mike

 

[Jon von der Heyden]

Your data commences from row 12 in sheet 'Tot', which means that when you index rows 12:25 you cannot use the row reference from the SMALL expression unless you adjust it.

So for instance, formula in 'Test'!N21 should be: =INDEX(Tot!$A$12:$A$25,SMALL(IF(Tot!$BA$12:$BA$25=O21,ROW(Tot!$A$12:$A$25)-11),COUNTIF($O$21:$O21,"="&O21)),1)

 

[Debraj]

Hi Mike..

Hope Jon will not mind.. if I change a lil in his formula..

=INDEX(Tot!$A$12:$A$25,SMALL(IF(Tot!$BA$12:$BA$25=O21,ROW(Tot!$A$12:$A$25)-Min(ROW(Tot!$A$12:$A$25))+1),COUNTIF($O$21:$O21,"="&O21)),1)

 

[mjk1chi]

Thanks Jon and Debra and Somendra!!
But what am I messing up? It keeps giving me the incorrect result even when I copy in these formulas! Could you possibly do it right in the spreadsheet and send back so that I can see what i am doing wrong?

Thanks!

Mike

 

[NARAYANK991]

Hi Mike ,

See if this is OK.

Narayan

 

[Somendra Misra]

Hi,

See the attached file. Yellow color cells is solution based on my understanding of your data.

Just advise if any issue.

Regards,