Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sort data in a file
I have created a worksheet that is generated by formulas which bring in the
data from other sheets. I am unable to sort the data and I beleive it is because of the formulas. I copied the data to another worksheet with out the formulas and I am able to sort but I can't be doing that. what can I do? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sort data in a file
On Sat, 27 Dec 2008 13:31:00 -0800, Mike
wrote: I have created a worksheet that is generated by formulas which bring in the data from other sheets. I am unable to sort the data and I beleive it is because of the formulas. I copied the data to another worksheet with out the formulas and I am able to sort but I can't be doing that. what can I do? What is your data looking like? Is it one column only? Is it one row only? Is it several rows and several columns? How do you want the data sorted? Lars-Åke |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sort data in a file
The column headings start in column A thru column BA. The data is in the
rows under the headings. Currently about 100. Formulas are in the entire sheet which bring the data in from another sheet.. I have set the headings, which are in row 5 column A through BA to sort the rows but I think the fomulas prevent the sort. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sort data in a file
On Sat, 27 Dec 2008 16:21:01 -0800, Mike
wrote: The column headings start in column A thru column BA. The data is in the rows under the headings. Currently about 100. Formulas are in the entire sheet which bring the data in from another sheet.. I have set the headings, which are in row 5 column A through BA to sort the rows but I think the fomulas prevent the sort. That answers most of my questions, but not the one about on which column you want to have the data sorted by. I thus assume that you want the data sorted by column A (only). Here is one way of doing it: On yet another sheet (which I call Sheet2), insert the same headings on row 5 that you have on the first sheet (which I assume is named Sheet1). Now in cell A6 of Sheet2 you may try the following formula: =INDEX(Sheet1!A$6:A$100,MATCH(SMALL(Sheet1!$A$6:$A $100,ROW()-ROW(A$5)),Sheet1!$A$6:$A$100,0)) Note that there are no $ before A's in the first range, but in the other two ranges. If you want to sort by another column than column A, change the later two ranges accordingly but not the first one. Copy across columns A thru BA and then copy down for as many rows as there are data. Change the '100' in three places to fit the number of data rows, i.e. rows with formulas on Sheet1, that you have. Hope this helps / Lars-Åke |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sort data in a file
Hi,
You really need to give us more info. For example 1. which columns control the sort order, 2. What type of data is in those columns - number or text or dates, 3. is the data in the sort controlling column(s) unique or can there be repeats? You could try something like this =INDEX(M,MATCH(ROW(C1),COUNTIF(M,"<="&M),0)) In this case I have named a single column that you want sorted M, suppose that was A1:A100 on another sheet. You can fancy up this formula to handle each of the columns in the data area by assigning M for the sort column and N for the entire range, then the formula would read =,INDEX(N,MATCH(ROW(C1),COUNTIF(M,"<="&M),0)) and you would change the last 0 to 1, 2, 3, ... to indicate the column of the range N that you are returning. This formula does not handle duplicates in the sort range. If this helps, please click the Yes button. Cheers, Shane Devenshire "Mike" wrote in message ... I have created a worksheet that is generated by formulas which bring in the data from other sheets. I am unable to sort the data and I beleive it is because of the formulas. I copied the data to another worksheet with out the formulas and I am able to sort but I can't be doing that. what can I do? |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sort data in a file
Hi,
I forgot to note that the formula below is Array Entered so press Shift+Ctrl+Enter to enter it. Cheers, Shane Devenshire "Shane Devenshire" wrote in message ... Hi, You really need to give us more info. For example 1. which columns control the sort order, 2. What type of data is in those columns - number or text or dates, 3. is the data in the sort controlling column(s) unique or can there be repeats? You could try something like this =INDEX(M,MATCH(ROW(C1),COUNTIF(M,"<="&M),0)) In this case I have named a single column that you want sorted M, suppose that was A1:A100 on another sheet. You can fancy up this formula to handle each of the columns in the data area by assigning M for the sort column and N for the entire range, then the formula would read =,INDEX(N,MATCH(ROW(C1),COUNTIF(M,"<="&M),0)) and you would change the last 0 to 1, 2, 3, ... to indicate the column of the range N that you are returning. This formula does not handle duplicates in the sort range. If this helps, please click the Yes button. Cheers, Shane Devenshire "Mike" wrote in message ... I have created a worksheet that is generated by formulas which bring in the data from other sheets. I am unable to sort the data and I beleive it is because of the formulas. I copied the data to another worksheet with out the formulas and I am able to sort but I can't be doing that. what can I do? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
data, sort option is grayed. how to sort on a column? | Excel Discussion (Misc queries) | |||
Excel should let me sort the file directory when saving a file | Excel Discussion (Misc queries) | |||
New data will not sort with existing data from copied file | Excel Worksheet Functions | |||
data sort is not including all columns in sort | Excel Discussion (Misc queries) | |||
How do I sort a column of data and have each data row sort accordi | Excel Discussion (Misc queries) |