Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
putting 2 long columns into multiple columns in excel page and sor
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
putting 2 long columns into multiple columns in excel page and sor
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! ================================================== === "bob_mhc" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Convert multiple columns into one long row | Excel Discussion (Misc queries) | |||
Combine multiple columns into two long columns, Repeating rows in first column | Excel Discussion (Misc queries) | |||
Combine multiple columns into two long columns, Repeating rows in first column | Excel Discussion (Misc queries) | |||
How do I print a long vertical list in two columns in 1 page ? | Excel Discussion (Misc queries) | |||
print 1 long row(20 columns) in excel to one single page | Excel Worksheet Functions |