Sort data in a file

[Mike]

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?

 

[Lars-Åke Aspelin]

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

 

[Mike]

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.

 

[Lars-Åke Aspelin]

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

 

[Shane Devenshire]

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

 

[Shane Devenshire]

Hi,

I forgot to note that the formula below is Array Entered so press
Shift+Ctrl+Enter to enter it.

Cheers,
Shane Devenshire