Sorting A Huge Sheet

Question

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.

Babanana · Accepted Answer · 2024-11-01 07:05:55Z

0

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:

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

edited Nov 1, 2024 at 7:05

answered Nov 1, 2024 at 6:53

Babanana

1,4081 gold badge5 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Sorting A Huge Sheet

1 Answer 1

Sorting Based on a Ranking

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Sorting Based on a Ranking

Comments

Your Answer

Sign up or log in

Post as a guest

Related