Return and rank top 3 results (including ties) from VSTACK, FILTER, CHOOSECOLS, and SORT formula

HD_Excel

New Member
Joined
Dec 7, 2024
Messages
5
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
Welcome to the MrExcel board!

For the future, I suggest that you investigate XL2BB to provide sample data & expected results to make it easier for helpers to understand just what you hae, where it is, what you current formula is doing etc. It is very hard to "reverse engineer" a formula (actually not even a real formula that you have given us) to try to work out what is happening.
(If you have trouble with XL2BB, review the "XL2BB Icons greyed out" link in the 'Known XL2BB issues' section near the top of the XL2BB Instructions page linked above.)

Anyway, see if this helps (after substituting the relevant column numbers). I have assume there will be at least 3 different values in that final column (col3) before taking the "top 3". If this assumption may not be correct then a further formula modification is possible.

Excel Formula:
=LET(a,VSTACK(Awards_Data),b,CHOOSECOLS(SORT(FILTER(a,(INDEX(a,,col1)="Jr")*(INDEX(a,,col2)="Yes")),col3,-1),col4,col1,col10,col3),c,TAKE(b,,-1),FILTER(b,c>=LARGE(UNIQUE(c),3)))
 
Upvote 1
Solution
Welcome to the MrExcel board!

For the future, I suggest that you investigate XL2BB to provide sample data & expected results to make it easier for helpers to understand just what you hae, where it is, what you current formula is doing etc. It is very hard to "reverse engineer" a formula (actually not even a real formula that you have given us) to try to work out what is happening.
(If you have trouble with XL2BB, review the "XL2BB Icons greyed out" link in the 'Known XL2BB issues' section near the top of the XL2BB Instructions page linked above.)

Anyway, see if this helps (after substituting the relevant column numbers). I have assume there will be at least 3 different values in that final column (col3) before taking the "top 3". If this assumption may not be correct then a further formula modification is possible.

Excel Formula:
=LET(a,VSTACK(Awards_Data),b,CHOOSECOLS(SORT(FILTER(a,(INDEX(a,,col1)="Jr")*(INDEX(a,,col2)="Yes")),col3,-1),col4,col1,col10,col3),c,TAKE(b,,-1),FILTER(b,c>=LARGE(UNIQUE(c),3)))
Peter, Thank you for the response. Sorry for not using XL2BB, but the data in the arrays is huge...and contains private information that can not be shared. It would take me days to create a mockup file that would allow me to share everything.

Anyway, your formula was a big help. I had to do some tweaks to it, but made it work. I ended up using choosecols instead of the take function for c as my col3 from my example is not in fact the last column in my final array. I find I get the same results whether I include the unique function or leave it out. All values in the column be ranked for awards is a number up to 3 decimal places.

Excel Formula:
=IFERROR(LET(a,VSTACK(Awards_Data),b,CHOOSECOLS(SORT(FILTER(a,(INDEX(a,,col1)="Jr")*(INDEX(a,,col2)="Yes")),col3,-1),col4,col1,col10,col3),c,CHOOSECOLS(b,4),FILTER(b,c>=LARGE(UNIQUE(c),3))),"No Entries")

The results of my formula spills out into the 2nd column of my worksheet. In the first column, I am using the following formula to provide a rank number to the results. I would love to be able to build this directly into the formula, but haven't figured a way to do it. I currently have to copy this formula down. There are at least 15 different award lists, so I'm trying to get it all done in one formula. The below formula produces results like the RANK.EQ function.

Excel Formula:
=IF(OR(B116="No Entries",B116=""),"",COUNTIFS(B116:B119,"<>",D116:D119,">"&D116)+1)
 
Upvote 0
I realize that my ranking formula is overcomplicating things. I've gone back to the following RANK.EQ formula to calculate the rank.

Excel Formula:
=IFERROR(RANK.EQ(D116,$D$116:$D$120,0),"")

I should probably start a new thread for the second part of this problem. I would like to create a dynamic array for the RANQ.EQ formula to coincide with my original results and then I can use HSTACK to combine it with those results. One problem being that the original array could be either 3 or 4 rows depending if there is a tie in 3rd place.

Ranking results will be 1, 2, 3 or 1, 1, 2 or 1, 2, 2 or 1, 2, 3, 3. In a perfect world, I would love to leave the duplicate rank blank for a cleaner look.
 
Upvote 0
It would take me days to create a mockup file
It shouldn't. I had to create one to test my formula & it didn't take much time. ;)


I ended up using choosecols instead of the take function for c as my col3 from my example is not in fact the last column in my final array.
OK, fair enough. Glad you could adjust. (y)


I find I get the same results whether I include the unique function or leave it out
To me that would indicate that you have not had any ties in the first or second places.

Here is my formula using my sample data. B8 has UNIQUE and B16 does not. The results are not the same.
They are not the same because without UNIQUE on the values in column E the third largest value is 60. With UNIQUE the third largest value is 55.

HD_Excel.xlsm
BCDEF
839Jr4699
96Jr4060
1048Jr7460
1160Jr7360
1275Jr6655
13
14
15
1639Jr4699
176Jr4060
1848Jr7460
1960Jr7360
20
Formula
Cell Formulas
RangeFormula
B8:E12B8=LET(a,VSTACK(Sheet1!A8:J15,Sheet2!A8:J15,Sheet3!A8:J15),b,CHOOSECOLS(SORT(FILTER(a,(INDEX(a,,1)="Jr")*(INDEX(a,,2)="Yes")),3,-1),4,1,10,3),c,TAKE(b,,-1),FILTER(b,c>=LARGE(UNIQUE(c),3)))
B16:E19B16=LET(a,VSTACK(Sheet1!A8:J15,Sheet2!A8:J15,Sheet3!A8:J15),b,CHOOSECOLS(SORT(FILTER(a,(INDEX(a,,1)="Jr")*(INDEX(a,,2)="Yes")),3,-1),4,1,10,3),c,TAKE(b,,-1),FILTER(b,c>=LARGE(c,3)))
Dynamic array formulas.
 
Upvote 0
For the purposes of my spread sheet, Unique isn't required. Awards are handed out to top 3 places. If two tie for second, no 3rd place is awarded. in your B16 data, 1st would be awarded and 2nd would be tied between 3 people. Pretty rare scenario where we use a formula to calculate a percentage down to 3 decimal places.

Any luck with adding a ranked number to the array? Or am I stuck with filling out column A (Rank) separate from my Sort, Filter, Choosecols, Vstack formula? I was hoping to create an array formula to show 1st, 2nd, 3rd, based on the results of the original formula, then I could easily HStack it to combine it with the original formula.
 
Upvote 0
Awards are handed out to top 3 places. If two tie for second, no 3rd place is awarded.
:confused: Isn't that a direct contradiction of what you said in post #1 where, in your preferred results, you have awarded 3rd place to Steve even though two were tied for second?
See below for preferred results. ...
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
 
Upvote 0
:confused: Isn't that a direct contradiction of what you said in post #1 where, in your preferred results, you have awarded 3rd place to Steve even though two were tied for second?
Yup. In the haste of the original post I made a mistake. That should have shown 1, 2, 2, 4.
 
Upvote 0
Yup. In the haste of the original post I made a mistake. That should have shown 1, 2, 2, 4.
.. and what you wrote specifically about that situation was also a mistake?
Note that Mary's rank is not shown as she is tied for 2nd.


In any case, I am still confused. If we show those 4 rows of results then what has happened to what you said in post 6?
Awards are handed out to top 3 places. If two tie for second, no 3rd place is awarded.
 
Upvote 0

Forum statistics

Threads
1,226,489
Messages
6,191,335
Members
453,656
Latest member
lilyrosy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top