How can I sort my data while keeping paired rows together?

Question

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,2

Sorted 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  

Solution

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.

Comments

[Jonny Wright]

Can you divulge any information regarding what this will actually be used for? Might help with answers. Eg; If its always going to be random data entered then it may be difficult. If it is, say two dates, that may make things easier.

[Jonny Wright]

Also, what would be the reference point for sorting? Ie, if you sort A-Z, which cell value is it using to sort by?

[Joe Lee Frank]

Certainly I am happy to explain the purpose. I am trying to build a business development status sheet to track the sales funnel in my corporation. If I list all the information we want to track in a single row, it would not fit on a single sheet of 14 inch paper and those who are using lap tops must continuously scroll. Using two rows per client makes it possible to read or print on a single screen. Thanks for the answers I got so far. Don't quite understand but will try them. There is a need to sort on different cells though-not always the same one.

[Excellll]

If you have a question for the asker, leave a comment under the question. It's best to get clarification before answering.

[Joe Lee Frank]

Thanks Scott. Unfortunately there are two many potential clients and the database is too dynamic to make this approach efficient.

[tmfahall]

Excellll you have to have at least a 50 reputation to comment on anything other then your own answer. Otherwise I would have put my whole answer in a comment.

[Scott - Слава Україні]

When an example is clearly artificial, it is wise to assume that it is just a representative example, and not a real example. (Admittedly, the OP could have done a better job of creating a representative example.) In the example in the question (before the sort), A1 = A1,1, A2 = A1,2, A3 = A2,1, and A4 = A2,2. I believe that the appropriate interpretation is that this represents a data set where A1 ≤ A3 that is being sorted into reverse order (A1 ≥ A3). But your solution assumes that A1 ≤ A2 ≤ A3 ≤ A4, which is probably not true for the real data.

[Scott - Слава Україні]

Also, your solution becomes unwieldy when the number of rows is 400 or 4000, rather than the four shown in the example.

[Scott - Слава Україні]

Note that the OP says (in a comment) that there is a requirement to be able to sort on various fields. Therefore, this solution would need to have a helper column for each field that could ever be used as a sort key. And BTW, it probably doesn’t make sense to filter the columns in this context. Aside from that, +1; this is a good, easy-to-implement answer.

[Ramhound]

This does not provide an answer to the question. To critique or request clarification from an author, leave a comment below their post - you can always comment on your own posts, and once you have sufficient reputation you will be able to comment on any post.

[Giacomo1968]

“…you have to have at least a 50 reputation to comment on anything…” Yes, there is a reason for that. At least this answer has some answer context, but it is veering into a comment. Earning 50 rep is quite easy. Just do some edits and engage positively in the community. In a few days you’ll get at least 50 rep and can comment.

[William T Froggard]

Just so people aren't misinformed: Excel has nothing at all to do with databases. Databases were designed to overcome many of the weaknesses that spreadsheets have, including sorting data, grouping things logically, etc. Really, spreadsheets are best kept away from. Eventually you'll find yourself in a situation you can't easily get out of with spreadsheets, one way or another.