Sorting Ascending and Descending (Numbers 3.2)

TB,

The result you get will depend on the type of addressing you use. in general though, it's risky to sort on a column containing expressions.

If you post more information on what you are doing, maybe we can help.

Jerry

So, I assume that the table in the screen shot is Table 1 and that Tables 2 and 3 are out of sight.

This makes it difficult to be specific about what might be happening.

A different addressing scheme might straighten things out, but not knowing what you are referencing it's difficult to say what you should do. The problem is that whereas Numbers 2 used to adjust cell references to track sorted rows, Numbers 3 does not. For this reason, different cells are referenced after the sort, and some may be out of the range of your table, causing the error.

Jerry

Apple changed the way sorting works. In some ways it is better, in some ways it is worse.

I am not clear on where your formula is. The selected cell in the "after" screenshot appears to refer to C10 and D10 (because those two cells are colored in) but the formula you posted refers to cells in different tables. With a better understanding of the formulas used in your table and the structure of the other tables, maybe we can help you develop a sortable set of formulas.

You can make the reference to Table2::B3 an absolute reference by clicking on the reference and selecting preserve row, or column as appropriate.

FF,

There's no assurance that Preserving Row would help. In fact I doubt that it would. It will be good to see what the big picture is.

Jerry

Hi TB,

This may provide the end result you are looking for. Rather than sorting your Ranking Table, it extracts a list, sorted by rank, of the 16 teams, and lists their "Standings Points" and Rank.

The method uses an added column to provide a points list with no duplicate values (required by LOOKUP). The 'distinct values' column, shown greyed, may be hidden.

The renamed "Point totals" table contains the formula below in the added column (G):

G2, and filled down: =E+ROW()/1000

The second table, "Team Ranking" contains a lookup formula in columns B and C to retrieve the values shown in those columns:

B2, and filled down: =LOOKUP(LARGE(Point totals :: $F,ROW()-1),Point totals :: $F,Point totals :: $A)

C2, and filled down: =LOOKUP(LARGE(Point totals :: $F,ROW()-1),Point totals :: $F,Point totals :: $E)

Note that these differ only in the return-values column address.

Column A uses the RANK function and the actual Standings points values in column C to determine the RANK of each team, according to it's Standings points total.

A2, and filled down: =RANK(C,$C,0)

For a list sorted in descending order, replace LARGE with SMALL.

If the smallest point total means the team is in first place, replace =RANK(C,$C,) with =RANK(C,$C,1)

Regards,

Barry

You are absolutely correct JG1 - without having the complete picture about how the two tables inter-relate it is not possible to really come up with a solution. Lacking that, this was just one suggestion.

Hi barry,

u seem to be numbers expert, so i need your help, i am using number 3.5.1 on OS X yosemite iMac.

I just imported a CSV file for my credit card statement and played with the sort and filter a bit to see my consumption,

However now i am stuck at not being able to filter it to its default, which was in an ascending order, dates, month , year,

Now it is only showing in ascending order with prioritizing dates only, instead of dates and months.

See attached. i hope you know what i mean.

This is after playing around.

This is the original . ( How to make the top attached as the bottom attached )

HI Tumberry,

It's generally not a good idea to tag on a new question to a thread whose last post was almost a year ago, particularly when your question is different from the one that started the thread. The original question was regarding ranking a set of teams by their total scores, and listing the teams in rank order; yours does involve sorting, but the sort is by date—quite a different issue from sorting by total score.

Looking at your data, one particular characteristic of the display stands out: the data is aligned to the left side of the column. In Numbers, and in other spreadsheets, this is the default alignment for cells containing text values. Numeric values, including quasi-numeric values such as dates are aligned right by default.

Numbers has assigned text formatting to these imported values, and is sorting them correctly as text values.

To get them to sort as dates, you need to get Numbers to recognize the values as dates. That could be as simple as formatting the cells containing the 'dates' to contain Date and Time values (with only the Date part displayed). If that does not provide success, you may need to use an extra column and a formula to extract the day, month and year values from the text, then use those values to construct the Date part of a Date and Time value (Numbers will set the Time part to 00:00:00—midnight, at the beginning of that date.

Your task is made easier by the use of leading zeroes in the 'dates'. This provides a consistent dd/mm/ccyy pattern from which to extract the needed values.

Your main function will be DATE

The day, month and year values for DATE will be provided by LEFT, MID and RIGHT, or by three iterations of MID.

Check the instructions for those functions in Numbers.

If more specific instructions are needed, please repost your question as a New Topic.

Regards,

Barry

Hi,

sorry for posting in the wrong thread.

However, i have tried your method of changing the format from text to date, but still when i do sort ascending/descending, it still prioritizes the first 2 numbers.

I found out that only text starting with 0 can be changed to date & time format, and cell starting with 1 or 2, doesn't allow the change to date format from text. Pretty strange,

however the ones i managed to changed, when i tried the sorting , i still did not get what i wanted to get.

Anyways thanks for the help. ill make a new post about it and hope to get answers soon.