Forum Discussion
Sorting Problem
I build computers for myself and for friends, and I built a spreadsheet for comparing builds. I have a well developed system of determining a standardized point value to each component based on price and performance, but that's not part of the issue.
I am trying to get a list of the builds in order of their rank based on totals of the points. I am 95% of the way there, when there are multiple builds with the same point value, the list shows the correct number of entries for that point total, but it lists the first build name of that total for all the entries. It's not listing the different names for each of the builds with the same total.
I'm including a screenshot of a simplified version of this setup (excluding all the calculations of the point values, just their totals) as well as a link to the simplified spreadsheet if it's useful.
Cell K2 uses the formula =LARGE($F$2:$F$16,$J2)
Cell L2 uses the formula =INDEX($A$2:$A$16,MATCH(LARGE($F$2:$F$16,$J2),$F$2:$F$16,0))
If anyone has a suggestion how to adjust or rewrite the formula to solve this issue it would be greatly appreciated!
Lee
https://www.dropbox.com/scl/fi/28e5jr65lbov3yp1u64v7/SortingProblem.xlsx?rlkey=rq50ubzz4x2w0yr3870i3gasn&dl=0
3 Replies
As variant
=LET( data, $A$2:$F$16, VSTACK( {"Rank","Score","Build Name"}, HSTACK( SEQUENCE(ROWS(data)), SORT(CHOOSECOLS(data, {1,6}),2,-1) ) ) )SergeiBaklan​ thats fantastic, thank you so much! I'll be digging into this to learn more about how the vstack and hstack commands work. I really appreciate this!
alternatively instead of another table just add the filters to that table (on Data tab) or Format as Table (on Home tab) and use the drop down filters to sort by that Totals column.
