Sorting by Row with sub-rows in place

je_mapel 

Excel for web doesn't have the same level of sorting options as the desktop version, but there are a few workarounds you can try.

One option is to add a helper column to your table that contains a concatenated string of the values in the header and subheader cells for each row. For example, if your table has headers in columns A and B, and subheaders in columns C and D, you could add a formula to column E that combines the values in A, B, C, and D for each row:

=A2&"|"&B2&"|"&C2&"|"&D2

his formula concatenates the values in A2, B2, C2, and D2 with "|" as the separator.

You can adjust the formula to fit the structure of your table.

Once you have this helper column, you can sort the table by that column to maintain the hierarchy of the rows. Select the entire table, including the helper column, and go to Data > Sort > Smallest to Largest (or Largest to Smallest, depending on your data).

Make sure to choose the helper column as the sort key, and make sure to check the "My data has headers" box if your table has headers.

After the table is sorted, you can hide the helper column if you don't need to see it.

Another option is to use a custom sort order based on the hierarchy of your rows.

This method is more tedious, but it can work if your table has a relatively fixed structure.

To use a custom sort order, you'll need to manually define the sort order for each level of your table. For example, if your table has headers in columns A and B, and subheaders in columns C and D, you could define a custom sort order like this:

1. Sort by column A in alphabetical order.
2. Within each group of column A values, sort by column B in alphabetical order.
3. Within each group of column B values, sort by column C in alphabetical order.
4. Within each group of column C values, sort by column D in alphabetical order.

To apply this custom sort order, select the entire table, including the headers and subheaders, and go to Data > Sort > Custom Sort.

In the Sort dialog box, click "Add Level" to add each level of your table, and define the sort order for each level using the "Sort On", "Order", and "Custom List" options.

Keep in mind that this method can be time-consuming, especially if your table has many levels or if the structure of your table changes frequently. If possible, using a helper column is usually a more efficient solution.

I hope this helps!