3

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
Improve this question
3
  • 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
    Commented May 29, 2014 at 22:37
  • Also, what would be the reference point for sorting? Ie, if you sort A-Z, which cell value is it using to sort by?
    – Jonny Wright
    Commented May 29, 2014 at 22:40
  • 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.
    – Joe Lee Frank
    Commented May 30, 2014 at 2:53

5 Answers 5

Reset to default
3

Set up a helper column to preserve the 2-row structure and sort this new helper column instead.

enter image description here

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.

enter image description here

Improve this answer
1
  • 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.
    – Scott - Слава Україні
    Commented May 30, 2014 at 17:23
0

Store your data in one row per logical record:

                                data in single rows

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:

                        referencing the first matrix

That will display the way you want:

                                                        how the (paired row) data are displayed

Admittedly, this approach does not allow you to edit the data in the two-row format.

Improve this answer
2
  • Thanks Scott. Unfortunately there are two many potential clients and the database is too dynamic to make this approach efficient.
    – Joe Lee Frank
    Commented May 30, 2014 at 2:59
  • 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.
    – William T Froggard
    Commented Sep 16, 2016 at 2:04
0

I'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.

Improve this answer
1
  • 1
    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.
    – Ramhound
    Commented Sep 2, 2015 at 18:48
0

Are 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.
Improve this answer
4
  • 1
    If you have a question for the asker, leave a comment under the question. It's best to get clarification before answering.
    – Excellll
    Commented May 30, 2014 at 1:34
  • 2
    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.
    – tmfahall
    Commented May 30, 2014 at 13:35
  • 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 - Слава Україні
    Commented May 30, 2014 at 17:16
  • Also, your solution becomes unwieldy when the number of rows is 400 or 4000, rather than the four shown in the example.
    – Scott - Слава Україні
    Commented May 30, 2014 at 17:17
0

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.

Improve this answer
0

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .