putting 2 long columns into multiple columns in excel page and sor

[bob_mhc]

Hi all,
What I want to do is to take a long 2-column sorted list of data points
like this:

[Name | age]

and then arrange the data into multiple columns on pages and then sort on
just the names keeping the ages correct with the names (see below).

[Allen | 32 ]
[Beth | 12 ]
[Jim | 8 ]
[Judith | 40 ]
[Keith | 32 ]
.....
[Zack | 21 ]
(this has been, say, 500 names)

I then want to put this into Excel pages with 8 columns
[name1 |age1 |name31 |age31 |name61 |age61 |name91 |age91 ]
[name2 |age2 |name32 |age32 |name62 |age62 |name92 |age92 ]
[name3 |age3 |name33 |age33 |name63 |age63 |name93 |age93 ]
.......
[name30|age30|name60 |age60 |name90 |age90 |name120|age120]

and then continue to the next page with [name121|age121] ... until all of
the data are included.

Then when I add another [name |age] (at the end) or delete a [name | age ]
(anywhere), I need to be able to sort the list so that it falls into the same
pattern I've set up (removing the deleted data points, remaking the columns,
with names in correct alphabetical order).

Is there a way to easily do this in Excel 2003?

Many thanks,
Bob

 

[RagDyeR]

The only way that I can think of accomplishing something like this would be
to use 2 sheets.

Say you make Sheet2 your data sheet, where all the names are in Column A and
all the corresponding ages are in Column B.
Then you just insert and/or delete and/or add and/or subtract and/or sort
however you wish.

Make Sheet1 your "presentation" sheet, where you create links to the cells
on Sheet2, in the configuration that you described.

You create these links using the Indirect() function, so that they (links)
will *always* look at the same, individual cells on Sheet2, and *not* follow
the data to new locations as you perform your various sorts and inserts and
deletions.

So, with your data on Sheet2, from A1 to Bn,
enter this formula in A1 on Sheet1:

=INDIRECT("Sheet2!A"&(ROWS($1:1)+30*COLUMNS($A:B)/2)-30)

AND, enter this formula in B1 on Sheet1:

=INDIRECT("Sheet2!B"&(ROWS($1:1)+30*COLUMNS($A:B)/2)-30)

NOW, select *both* A1 and B1, and drag that 2 cell selection across to
Column H,
Then drag down to Row 30.

This should give you the configuration that you're looking for.
Play around and make revisions and sorts to the data on Sheet2, and you'll
see that Sheet1 will display that data in numerical order, by row number, as
arranged on Sheet2.

For your 2nd page formulas, just add 120 to the Rows() function, so that
your 2 *starting* formulas for page 2 will look like this:

=INDIRECT("Sheet2!A"&(ROWS($1:121)+30*COLUMNS($A:B)/2)-30)

AND

=INDIRECT("Sheet2!B"&(ROWS($1:121)+30*COLUMNS($A:B)/2)-30)

I'm sure you can figure out the formulas for any subsequent pages.
--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================


Hi all,
What I want to do is to take a long 2-column sorted list of data points
like this:

[Name | age]

and then arrange the data into multiple columns on pages and then sort on
just the names keeping the ages correct with the names (see below).

[Allen | 32 ]
[Beth | 12 ]
[Jim | 8 ]
[Judith | 40 ]
[Keith | 32 ]
.....
[Zack | 21 ]
(this has been, say, 500 names)

I then want to put this into Excel pages with 8 columns
[name1 |age1 |name31 |age31 |name61 |age61 |name91 |age91 ]
[name2 |age2 |name32 |age32 |name62 |age62 |name92 |age92 ]
[name3 |age3 |name33 |age33 |name63 |age63 |name93 |age93 ]
.......
[name30|age30|name60 |age60 |name90 |age90 |name120|age120]

and then continue to the next page with [name121|age121] ... until all of
the data are included.

Then when I add another [name |age] (at the end) or delete a [name | age ]
(anywhere), I need to be able to sort the list so that it falls into the
same
pattern I've set up (removing the deleted data points, remaking the columns,
with names in correct alphabetical order).

Is there a way to easily do this in Excel 2003?

Many thanks,
Bob