Linking rows for sorting

[Monee Kidd]

Let's see if I can simplify my document and explain the problem...I
have a document where, in cell A1 is a team name. Then in cell B1 is a
man's name and in cell B2 is his wife's name. Then in cell A3 is a
team name, in B3 a man's name, in B4 the wife. Etc, for about 1000
lines, using 8 different team names (they repeat). Is there a way to
sort this list by Column A (team name), while keeping the husband and
wife ogether? In other words, is there a way to link the rows with the
wives (which don't have anything in the A column) with their husbands
(which do have something in the A column)? Hopefully that made
sense.........

 

[Ian Page]

In general, you need to make a column with the values to be sorted, do your
sort, then delete the sort values afterwards - details follow....


To begin, make sure you have a blank row before the first row in your list -
we will build a formula which refers to the cell ABOVE your first record, so
we need to make sure that cell exists. Having done this, the cells you refer
to in your query are now: A2 (1st team name), B2 (1st husband name), B3
(first wife name), A4 (2nd team name), etc.

Then create a formula in C2 (or other blank cell to the right of your data
range, in same row as your first record), as follows -
=IF(ISTEXT(A2),A2,A1)
This puts the team name in that cell OR, if team name is blank, gets team
name from row above (i.e. team name from husband row).
Fill this formula down for all rows in your data range.

Then select your data range (including Col C), and sort by Col C. Take care
NOT to specify a 2nd sort criterion. In the absence a 2nd sort criterion,
items in Col B (i.e. names) remain in the same order as in your original
list (i.e. husband, wife, husband, wife, etc).

Finally delete Col C.

This is crude, and doesn't actually lock the rows together, but it's easy
and gets the job done.

Ian

 

[Dr Rocket]

Your data are an array of 2 by 2 subarrays, and that makes the sorting
complex. Excel sorts on numbers or text and by columns or rows, not by
subarrays. It has a Rank function that might help, but it works on
numbers only and Excel has no text equivalent.

So here's a solution by which you'll compute two intermediate arrays
and then make an answer array in the same form as your original data:

Next to your data in Columns A and B, build a table three columns
wide, say at Cols. E-G. In cell EI insert the formula
=INDEX(B:B,2*ROW()-1). In cell F1 insert the formula
=INDEX(C:C,2*ROW()-1). In cell G1 write =INDEX(C:C,2*ROW()). Copy Down
E1-F1 until you've captured all the data, say to row n.

Copy E1-Gn and Paste Values at, say, I1.

Now create a two column array at M1 as follows: At cell M1 write the
formula =IF(MOD(ROW(),2)=0,"",INDEX(I:I,(ROW()+1)/2)) and at N1 write
=INDEX(J:K,(ROW()+1)/2,MOD(ROW()+1,2)+1). Copy Down M1-N1 as deep as
the original data.

This last array is the answer array, and should be the same as the
original data.

Finally, Sort the array at I1-Kn any way you want. The answer array
will look like the original data, but sorted as you wish.

Jeff