I have an extensive formula that uses VSTACK, FILTER, CHOOSECOLS, and SORT functions. This formula combines data from several worksheets and spits out the top 3 values of the array based on the SORT of calculation results in col3. However, the TAKE function limits me to a set number of results. This data is meant for top 3 awards and I would like the results to include any ties if they are present (i.e col3 = 0.999, 0.989, 0.989, 0.981). I done many searches and have not come up with any solutions that work for this problem.
Original working formula
=IFERROR(LET(a,VSTACK(Awards_Data),TAKE(CHOOSECOLS(SORT(FILTER(a,(INDEX(a,,col1)="Jr")*(INDEX(a,,col2)="Yes")),col3,-1),col4,col1,col10,col3),3)),"No Entries")
(Note: Awards_Data refers to ='Sheeet1:Sheet10'!$A$7:$GG$20, col1-col10 refer to column numbers)
Example results below. The header shown below is just for reference purposes to the above formula only.
Col4 Col1 Col10 Col3
Jake Jr 99 0.990
Paul Jr 98 0.980
Mary Ld/Jr 98 0.980
I have a separate calculation for my results to display rank. Bonus points if you can make the original formula spit out the rank values as well. See below for preferred results. Note that Mary's rank is not shown as she is tied for 2nd.
Rank Col4 Col1 Col10 Col3
1 Jake Jr 99 0.990
2 Paul Jr 98 0.980
Mary Ld/Jr 98 0.980
3 Steve Jr 97 0.970
Original working formula
=IFERROR(LET(a,VSTACK(Awards_Data),TAKE(CHOOSECOLS(SORT(FILTER(a,(INDEX(a,,col1)="Jr")*(INDEX(a,,col2)="Yes")),col3,-1),col4,col1,col10,col3),3)),"No Entries")
(Note: Awards_Data refers to ='Sheeet1:Sheet10'!$A$7:$GG$20, col1-col10 refer to column numbers)
Example results below. The header shown below is just for reference purposes to the above formula only.
Col4 Col1 Col10 Col3
Jake Jr 99 0.990
Paul Jr 98 0.980
Mary Ld/Jr 98 0.980
I have a separate calculation for my results to display rank. Bonus points if you can make the original formula spit out the rank values as well. See below for preferred results. Note that Mary's rank is not shown as she is tied for 2nd.
Rank Col4 Col1 Col10 Col3
1 Jake Jr 99 0.990
2 Paul Jr 98 0.980
Mary Ld/Jr 98 0.980
3 Steve Jr 97 0.970