How can I pair two rows on a spreadsheet, so that for each data entry I can sort the matrix but the pair of rows moves as a single list of data, retaining the structure of the two rows?
For example: Original entry
A1,1 B1,1 C1,1 D1,1
A1,2 B1,2 C1,2 D1,2
A2,1 B2,1 C2,1 D2,1
A2,2 B2,2 C2,2 D2,2Sorted reverse order
A2,1 B2,1 C2,1 D2,1
A2,2 B2,2 C2,2 D2,2
A1,1 B1,1 C1,1 D1,1
A1,2 B1,2 C1,2 D1,2 5 Answers
Set up a helper column to preserve the 2-row structure and sort this new helper column instead.
The helper column (E in my example) references to the same column you want to filter for (e.g column C) but with one small improvement: Every second row your reference jumps one row above
You can speed up things by entering only two formulas, select both cells and drag them with auto-fill down.
Store your data in one row per logical record:
You will, of course, be able to sort this normally. Then set up a matrix with two rows per record on another sheet (or, possibly, on the same sheet) that references the data in the first matrix:
That will display the way you want:
Admittedly, this approach does not allow you to edit the data in the two-row format.
2I've been having the same issue .... this is how I fixed it. Highlight/select all of the columns you have data entered in (or that you want included in the sort). Select data and select auto filter. This will place double arrows in the top row that will allow you to sort each column while keeping corresponding data in the same row. Hope this helps.
1Are you asking for a step by step break down to do this or do you want a way to automate this?
The step by step for your example:
- Select the first 4 rows then click Data and click Z -> A.
- Then select just the top 2 and click A -> Z
- Then select the bottom 2 and click A -> Z.
Turn your data into a table (highlight all the data, then go to "Insert" and click on Table). Once you have a table, you can sort on any column you want and the rows will always be "linked" so that they stay together when they move in the sort.
0
