| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SORT MULTIPLE COLUMNS AT SAME TIME automatically
I have 8 columns of data and each row contains the corresponding data. i
want to automaticaly sort this data using excel funtions preferably not code so that alll the corresponding data from the row moves, not just one column. thanks -- |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SORT MULTIPLE COLUMNS AT SAME TIME automatically
Care to answer my question in your first post?
We can use this much newer thread if you prefer. -- Regards, RD ----------------------------------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit ! ----------------------------------------------------------------------------------------------- "bkunes" wrote in message ... I have 8 columns of data and each row contains the corresponding data. i want to automaticaly sort this data using excel funtions preferably not code so that alll the corresponding data from the row moves, not just one column. thanks -- |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SORT MULTIPLE COLUMNS AT SAME TIME automatically
ok sorry for the caps i left it on from auto cad, i have duplicate dates
-- "RagDyeR" wrote: Care to answer my question in your first post? We can use this much newer thread if you prefer. -- Regards, RD ----------------------------------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit ! ----------------------------------------------------------------------------------------------- "bkunes" wrote in message ... I have 8 columns of data and each row contains the corresponding data. i want to automaticaly sort this data using excel funtions preferably not code so that alll the corresponding data from the row moves, not just one column. thanks -- |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SORT MULTIPLE COLUMNS AT SAME TIME automatically
BallPark - about how many rows are you talking about?
-- Regards, RD ----------------------------------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit ! ----------------------------------------------------------------------------------------------- "bkunes" wrote in message ... ok sorry for the caps i left it on from auto cad, i have duplicate dates -- "RagDyeR" wrote: Care to answer my question in your first post? We can use this much newer thread if you prefer. -- Regards, RD ----------------------------------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit ! ----------------------------------------------------------------------------------------------- "bkunes" wrote in message ... I have 8 columns of data and each row contains the corresponding data. i want to automaticaly sort this data using excel funtions preferably not code so that alll the corresponding data from the row moves, not just one column. thanks -- |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SORT MULTIPLE COLUMNS AT SAME TIME automatically
25 rows
-- "RagDyeR" wrote: BallPark - about how many rows are you talking about? -- Regards, RD ----------------------------------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit ! ----------------------------------------------------------------------------------------------- "bkunes" wrote in message ... ok sorry for the caps i left it on from auto cad, i have duplicate dates -- "RagDyeR" wrote: Care to answer my question in your first post? We can use this much newer thread if you prefer. -- Regards, RD ----------------------------------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit ! ----------------------------------------------------------------------------------------------- "bkunes" wrote in message ... I have 8 columns of data and each row contains the corresponding data. i want to automaticaly sort this data using excel funtions preferably not code so that alll the corresponding data from the row moves, not just one column. thanks -- |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SORT MULTIPLE COLUMNS AT SAME TIME automatically
The procedure here, since this is all accomplished using functions which
cannot change other cells, is to have a working datalist, where you will input data, and a "presentation" datalist which will be populated by formulas drawing values from the working datalist in a particular, pre-defined order. You could, if you wish, use "Custom Views", where a "views" window is added to the Menu Bar for a real time indication of the present view in force, plus the ability to toggle between views with a single click. You said 8 columns, so let's say that the working datalist is in Columns S to Z, and the presentation datalist is in Columns A to H. Enter identical headers in Row1 for both datalists, with the sort key (the dates), being in Columns A and S. I've sized the formulas to Row 50 so you have room to expand. Fill in your working datalist in S2 to Z(n) with whatever data you presently have. To start: In A2, enter this formula: =IF(ISERR(LARGE($S$2:$S$50,ROWS($1:1))),"",LARGE($ S$2:$S$50,ROWS($1:1))) And copy down to A50. This will *automatically* sort the dates in your working datalist - descending, newest dates first. If you would like to sort with the oldest dates on top, simply change the Large() function to the Small() function in both places in the formula. Now, to match the other fields to the sorted dates, enter this *array* formula in B2: =IF($A2="","",INDEX(T$2:T$50,SMALL(IF($S$2:$S$50=$ A2,ROW($1:$49)),COUNTIF($A 2:$A$50,$A2)))) -- Array formulas must be entered with CSE, <Ctrl <Shift <Enter, instead of the regular <Enter, which will *automatically* enclose the formula in curly brackets, which *cannot* be done manually. You *must also* use CSE when revising the formula. *After* the CSE entry, copy the formula across to H2. Then, select B2 to H2, and copy that selection down to Row50. You should now have your "presentation" datalist displaying the data from your working datalist in sorted order, according to the dates. Assuming that your adding new data to the bottom of the working datalist, the newer (lower) duplicate date entries will display higher in the presentation datalist. This can be changed if you wish by simply changing the Small() function in the array formula to the Large(0 function. Post back if you're interested in creating "Custom Views". -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "bkunes" wrote in message ... 25 rows -- "RagDyeR" wrote: BallPark - about how many rows are you talking about? -- Regards, RD -------------------------------------------------------------------------- --------------------- Please keep all correspondence within the Group, so all may benefit ! -------------------------------------------------------------------------- --------------------- "bkunes" wrote in message ... ok sorry for the caps i left it on from auto cad, i have duplicate dates -- "RagDyeR" wrote: Care to answer my question in your first post? We can use this much newer thread if you prefer. -- Regards, RD -------------------------------------------------------------------------- --------------------- Please keep all correspondence within the Group, so all may benefit ! -------------------------------------------------------------------------- --------------------- "bkunes" wrote in message ... I have 8 columns of data and each row contains the corresponding data. i want to automaticaly sort this data using excel funtions preferably not code so that alll the corresponding data from the row moves, not just one column. thanks -- |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SORT MULTIPLE COLUMNS AT SAME TIME automatically
thank you i apreciate your help, it worked
-- "Ragdyer" wrote: The procedure here, since this is all accomplished using functions which cannot change other cells, is to have a working datalist, where you will input data, and a "presentation" datalist which will be populated by formulas drawing values from the working datalist in a particular, pre-defined order. You could, if you wish, use "Custom Views", where a "views" window is added to the Menu Bar for a real time indication of the present view in force, plus the ability to toggle between views with a single click. You said 8 columns, so let's say that the working datalist is in Columns S to Z, and the presentation datalist is in Columns A to H. Enter identical headers in Row1 for both datalists, with the sort key (the dates), being in Columns A and S. I've sized the formulas to Row 50 so you have room to expand. Fill in your working datalist in S2 to Z(n) with whatever data you presently have. To start: In A2, enter this formula: =IF(ISERR(LARGE($S$2:$S$50,ROWS($1:1))),"",LARGE($ S$2:$S$50,ROWS($1:1))) And copy down to A50. This will *automatically* sort the dates in your working datalist - descending, newest dates first. If you would like to sort with the oldest dates on top, simply change the Large() function to the Small() function in both places in the formula. Now, to match the other fields to the sorted dates, enter this *array* formula in B2: =IF($A2="","",INDEX(T$2:T$50,SMALL(IF($S$2:$S$50=$ A2,ROW($1:$49)),COUNTIF($A 2:$A$50,$A2)))) -- Array formulas must be entered with CSE, <Ctrl <Shift <Enter, instead of the regular <Enter, which will *automatically* enclose the formula in curly brackets, which *cannot* be done manually. You *must also* use CSE when revising the formula. *After* the CSE entry, copy the formula across to H2. Then, select B2 to H2, and copy that selection down to Row50. You should now have your "presentation" datalist displaying the data from your working datalist in sorted order, according to the dates. Assuming that your adding new data to the bottom of the working datalist, the newer (lower) duplicate date entries will display higher in the presentation datalist. This can be changed if you wish by simply changing the Small() function in the array formula to the Large(0 function. Post back if you're interested in creating "Custom Views". -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "bkunes" wrote in message ... 25 rows -- "RagDyeR" wrote: BallPark - about how many rows are you talking about? -- Regards, RD -------------------------------------------------------------------------- --------------------- Please keep all correspondence within the Group, so all may benefit ! -------------------------------------------------------------------------- --------------------- "bkunes" wrote in message ... ok sorry for the caps i left it on from auto cad, i have duplicate dates -- "RagDyeR" wrote: Care to answer my question in your first post? We can use this much newer thread if you prefer. -- Regards, RD -------------------------------------------------------------------------- --------------------- Please keep all correspondence within the Group, so all may benefit ! -------------------------------------------------------------------------- --------------------- "bkunes" wrote in message ... I have 8 columns of data and each row contains the corresponding data. i want to automaticaly sort this data using excel funtions preferably not code so that alll the corresponding data from the row moves, not just one column. thanks -- |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SORT MULTIPLE COLUMNS AT SAME TIME automatically
You're welcome, and thank you for the feed-back.
-- Regards, RD ----------------------------------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit ! ----------------------------------------------------------------------------------------------- "bkunes" wrote in message ... thank you i apreciate your help, it worked -- "Ragdyer" wrote: The procedure here, since this is all accomplished using functions which cannot change other cells, is to have a working datalist, where you will input data, and a "presentation" datalist which will be populated by formulas drawing values from the working datalist in a particular, pre-defined order. You could, if you wish, use "Custom Views", where a "views" window is added to the Menu Bar for a real time indication of the present view in force, plus the ability to toggle between views with a single click. You said 8 columns, so let's say that the working datalist is in Columns S to Z, and the presentation datalist is in Columns A to H. Enter identical headers in Row1 for both datalists, with the sort key (the dates), being in Columns A and S. I've sized the formulas to Row 50 so you have room to expand. Fill in your working datalist in S2 to Z(n) with whatever data you presently have. To start: In A2, enter this formula: =IF(ISERR(LARGE($S$2:$S$50,ROWS($1:1))),"",LARGE($ S$2:$S$50,ROWS($1:1))) And copy down to A50. This will *automatically* sort the dates in your working datalist - descending, newest dates first. If you would like to sort with the oldest dates on top, simply change the Large() function to the Small() function in both places in the formula. Now, to match the other fields to the sorted dates, enter this *array* formula in B2: =IF($A2="","",INDEX(T$2:T$50,SMALL(IF($S$2:$S$50=$ A2,ROW($1:$49)),COUNTIF($A 2:$A$50,$A2)))) -- Array formulas must be entered with CSE, <Ctrl <Shift <Enter, instead of the regular <Enter, which will *automatically* enclose the formula in curly brackets, which *cannot* be done manually. You *must also* use CSE when revising the formula. *After* the CSE entry, copy the formula across to H2. Then, select B2 to H2, and copy that selection down to Row50. You should now have your "presentation" datalist displaying the data from your working datalist in sorted order, according to the dates. Assuming that your adding new data to the bottom of the working datalist, the newer (lower) duplicate date entries will display higher in the presentation datalist. This can be changed if you wish by simply changing the Small() function in the array formula to the Large(0 function. Post back if you're interested in creating "Custom Views". -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "bkunes" wrote in message ... 25 rows -- "RagDyeR" wrote: BallPark - about how many rows are you talking about? -- Regards, RD -------------------------------------------------------------------------- --------------------- Please keep all correspondence within the Group, so all may benefit ! -------------------------------------------------------------------------- --------------------- "bkunes" wrote in message ... ok sorry for the caps i left it on from auto cad, i have duplicate dates -- "RagDyeR" wrote: Care to answer my question in your first post? We can use this much newer thread if you prefer. -- Regards, RD -------------------------------------------------------------------------- --------------------- Please keep all correspondence within the Group, so all may benefit ! -------------------------------------------------------------------------- --------------------- "bkunes" wrote in message ... I have 8 columns of data and each row contains the corresponding data. i want to automaticaly sort this data using excel funtions preferably not code so that alll the corresponding data from the row moves, not just one column. thanks -- |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Sort multiple columns to look for gaps | Excel Discussion (Misc queries) | |||
| How do I sort multiple columns of of numerical and alphanumerical. | New Users to Excel | |||
| Sort multiple columns with multiple formulas without returning #R | Excel Worksheet Functions | |||
| UDF to sort array with multiple columns | Excel Worksheet Functions | |||
| Sort multiple rows by data in certain columns | Excel Discussion (Misc queries) | |||
Linear Mode
