google-sheets-apigoogle-workspace

Sorting A Huge Sheet

I have a sheet in the following format:

Sheet

What I need to do is, sorting the skills by skill number, not alphabetically. I'm having trouble because the original sheet has thousands of names.

Here's what I'm trying to achieve:

Sorted Version

As seen, skill 1 should be the skill with the lowest skill number, skill 2 will be the second lowest, and so on. For example, Jill's Skill number 2 is now pool, not darts because pool has a lower skill number.

I think I first need to transpose the leftmost part as sorting by columns is much easier than sorting by rows. This gives me a thousand columns because I have a thousand names which is a bit inconvenient, but I couldn't think of anything else.

Then I thought of a VLOOKUP conditional formatting formula to give a color to every skill, so then I can sort them by color. I have 18 total skills so it makes 18 different formulas, but at least it's not a 1000. I could successfully give every skill a color by doing so, but only for the first column. I don't know how to apply the formula to all 1000 columns.

Either way, I'm sure there's a much easier way than what I thought, so any help is appreciated.

Solution

  • Sorting Based on a Ranking

    I have crafted a formula that you can achieve. By utilizing Sort, Index and Filter to match. Though there are more shorter ways I keep my formula this way to be more manipulable feel free to raise any question if you have any issues so I can address them.

    Try this:

    =BYROW(B2:D4, LAMBDA(b, LET(x, b, y,F2:F7, z,G2:G7, TOROW(BYROW(SORT(BYROW(TOCOL(x),LAMBDA(r,FILTER(z,y =r)))), LAMBDA(rr, INDEX(y,rr,1)))))))

    Sample Output:

    Sample Output

    Note: This doesn't overwrite data and most of the formula doesn't as well depending on the restriction level for your expected project and where the answer should be posted you might need to use an apps script custom formula

    Reference:

    Index

    FIlter

    Sort