0

I have let's say I have rows in the following order:

SN name1 grade1 name2 grade2 name3 grade3
1 Alice 97 Bob 90 Carl 80
2 Bob 80 Alice 100 Carl 95
3 Alice 100 Carl 80 Bob 90

Is there a way to keep the rows ordered 1 - 2 - 3, but alphabetize the columns by the student's names, while keeping the grade associated with each student?
Meaning, all rows will be in the format

SN | Alice | grade1 | Bob | grade2 | Carl | grade3 |
3
  • Welcome to Web Applications Stack Exchange. Consider sharing a publicly editable sample spreadsheet with realistic-looking data. Jan 5 at 21:27
  • I concur with doubleunary: please share a link to a spreadsheet with realistic sample data (e.g., if you have first and last name in cells, use first and last dummy names, not just first, etc.). What you want can likely be accomplished via formula; but it is complex and, in my opinion, may go beyond what a free forum such as this is designed to offer (though it will be up to each individual volunteer contributor how much time they are willing to invest via the forum).
    – Erik Tyler
    Jan 6 at 2:55
  • I'll assume you're no longer interested in help from the forum since you haven't responded with comment, a shared spreadsheet, or even acknowledging the work that volunteer contributor "doubleunary" did on your behalf over a week ago.
    – Erik Tyler
    Jan 13 at 3:11
0

You can sort the content of each row horizontally, and independently of other rows, using the recipe at Arrayformula with large function for multiple columns in Google Sheets.

But I think that it would probably make more sense to recast the data to a row-oriented layout. You can get a simple table with just three columns (SN, Name, Grade) like this:

=arrayformula( 
    query( 
      { 
        flatten( iferror( 
          vlookup( Grades!A1:A, Grades!A1:A, sign(sequence(1, columns(Grades!B1:G) / 2)), false ) 
        ) ), 
        flatten( filter( Grades!B1:G, not(isnumber(Grades!B2:G2)) ) ), 
        flatten( filter( Grades!B1:G, isnumber(Grades!B2:G2) ) ) 
      }, 
      "where Col1 is not null 
       and Col2 is not null 
       order by Col1, Col2", 
      1 
    ) 
)

This format is more conducive for further processing with additional query() or filter() formulas, pivot tables, and such.

In the event row 2 in data may contain blanks, use flatten( { B1:B, D1:D, F1:F } ) and flatten( { C1:C, E1:E, G1:G } ) instead of the two flatten(filter()) expressions.

Assuming that the three-column data is in Data!A1:C, you can create a simple report that pivots names horizontally like this:

=arrayformula( 
  query( 
    Data!A1:C, 
    "select A, max(C) 
     where A is not null 
     group by A 
     pivot B", 
    1 
  ) 
)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.