CHOOSECOLS

I have been putting together a data dashboard for my school for two years, and you'd think I would have had this figured out by now, but I didn't - the grades 1-12 all work out fine and sort nicely, but when I add in our early years classes (KB, KA, PS) those aren't recognized as number so they are put at the end of the list, after Grade 12; not in front of grade 1 where they should naturally reside.

Not knowing how to solve this effectively, I turned to ChatGPT to help me out. 

It suggested I make a helper column, I called it "hidden", and label each grade with its own grade, since the numbers were not a problem, and we needed to convert the "KB", KA, and "PS" text to a numerical value to rank them against other numbers.

I gave them -3 for PS, -2 for KA, and -1 for KB. 

I then used this formula to make the grade for each grade. It reads column B and rewrites the grade in column D for me.

Then I wanted another table with everything sorted. I would have used QUERY, but ChatGPT suggested I use CHOOSECOLS. The reason it said was that QUERY is used to search a database, whereas CHOOSECOLS creates a new array from the selected columns in the existing range.

That was kind of cool - I'd never used that formula before. 

The documentation shows it being used to manipulate data in an already-made table.