[1.181011.0] On tab "Search Units" sort order is wrong

PopGoesTheWza / swgoh-tb-sheets

SWGoH Territory Battles Spreadsheets

MIT License

7 stars 2 forks source link

[1.181011.0] On tab "Search Units" sort order is wrong #2

Closed DarkPhoenixSniper closed 6 years ago

DarkPhoenixSniper commented 6 years ago

Steps to reproduce

Expected Result

Sort logically by Rarity, then Level, then Gear, then Power

Actual Result

Sorts by text input like the following: 7L85G12PXXXXX 7L85G6PXXXXX because the first check of 1 is before 6, instead of 6 before 12

Version

1.181011.0

Testcase

Provide a link to a copy of the TB Sheets if necessary

DarkPhoenixSniper commented 6 years ago

=SORT((SPLIT(RangeOfCharacterStats,"[LGP]",TRUE)),1,TRUE,2,TRUE,3,TRUE,4,TRUE)

This outputs into 4 columns that are sorted as expected, Then further CONCATENATE(Col1,"L",Col2,"G",Col3,"P",Col4) Then first column would show the proper user for that characters power.

Maybe sort power, then find in array of user matching said value?

PopGoesTheWza commented 6 years ago

Sorting on raw Power should be the best option. Likely by using REGEXEXTRACT (https://support.google.com/docs/answer/3098244)

DarkPhoenixSniper commented 6 years ago

agreed... but we could also sort on all 3 if we wanted to do a little extra work...

PopGoesTheWza commented 6 years ago

Original purpose of this search is to identify the most disposable units (for platoons) So power is in fact the primary sorting key.

PopGoesTheWza commented 6 years ago

@DarkPhoenixSniper

Proposed formulas:

Heroes (cell B6)

=IFERROR(QUERY(SORT({TRANSPOSE(FILTER(Heroes!K1:BH,(Heroes!A1:A=$B$3)+(Heroes!A1:A="Hero"))),ARRAYFORMULA(VALUE(REGEXEXTRACT(TRANSPOSE(FILTER(Heroes!K2:BH,(Heroes!A2:A=$B$3)+(Heroes!A2:A="Hero"))),"P(\d+)")))},3,TRUE,1,TRUE),CONCATENATE("SELECT Col1,Col2 WHERE Col2>='",CONCATENATE(Platoon!A2+1,"*"),"'")))

Ships (cell F6)

=IFERROR(QUERY(SORT({TRANSPOSE(FILTER(Ships!K1:BH,(Ships!A1:A=$F$3)+(Ships!A1:A="Ship"))),ARRAYFORMULA(VALUE(REGEXEXTRACT(TRANSPOSE(FILTER(Ships!K2:BH,(Ships!A2:A=$F$3)+(Ships!A2:A="Ship"))),"P(\d+)")))},3,TRUE,1,TRUE),CONCATENATE("SELECT Col1,Col2 WHERE Col2>='",CONCATENATE(Platoon!A2+1,"*"),"'")))

DarkPhoenixSniper commented 6 years ago

CONFIRMED working!

Recommend implementation

PopGoesTheWza commented 6 years ago

Ready to ship with next release.