Numbers: Sorting a column with formulas

Can you structure your tables so that that you don't have rows that you need to hide? As you've discovered, sorting gives unexpected results when you filter or hide rows.

SG

Hi Blancaforthh,

I think the key will be how you bring the data into this table. With creative use of index columns in your tables of origin you may be able to bring only the data that is relevant and in the order you are looking for. This would be easier in the long run than sorting (it looks like your formulas are not "sort safe" anyway).

quinn

In Sort & Filter, under the Sort tab, you add the Datum column, and there should be a Sort Entire Table selection by default. Further, there is a choice to Sort by: Datum Ascending, and a Then by: Datum Ascending. These are the defaults.

Are you taking the above defaults, or are you attempting to Sort by Datum, and then by Uhrzeit (time of day)?

Hi Blancaforthh,

I know I am going to wish I had some German as we go along here but oh, well.

What do your source tables look like? What info are you trying to bring over? What does the formula you have in A2 look like? Is it filled down the column? Is it the same across the row? What are the choices in F1 and what are you actually doing there?

Bringing your data from 3 different tables into a summary in a usable fashion is an interesting problem but not impossible. It does require more info.

quinn

Hi Blancaforthh,

A thought on the overall structure of your document. It is much easier to have a single data table and then to draw out subsets from that. I don't know if this is practical for you but you might consider reversing the data flow. Enter all your data into one table. Easily sorted. Bring data into three specific tables as summary tables. This would be much less complicated and would probably scale better if you end up with more than 2 weeks of entries.

quinn

I agree with quinn. It is much easier to put your data in one well-formed table and then filter/sort as needed.

Having a big table with different sections and white space in between and lots of blank cells makes things far more complicated than they need to be.

You may find it helpful to study some of the included templates (File > New and pick from the chooser) to see some examples of effective use of Numbers tables.

SG

Hi Blancaforthh,

It seems to me that by using one large data table your current problems disappear. If you sort the one large table by teachers you will have what you asked for initially. Teachers will be grouped together and the dates will follow as they were entered.

There is a freeflowing quality that leaves the possibility that a teacher is assigned duplicate shifts on the same day. Whoops!

Here is an approach to flag duplicates:

Just looking at the table, you can see we have a date on each line and an added column for morning, noon or night. I used a popup menu for my example but using the shift times you could automate this with a lookup table. My teachers would also be in a popup menu.

In order to catch duplicate shifts I used the formula you see.

J2= =COUNTIFS(A,A2,B,B2,I,I2) filled down.

This counts the times that teacher, date and shift are the same. You can see I have been inattentive.

In order to make it simpler, I would set the text to white and create a conditional formatting rule:

I have a table where I wanted to see clearly when the next day started. I used a conditional highlighting rule for that too. If the date is the same as the preceding one it is grey.

With the large table it will be easy to pull any data you want to other tables.

Let me know what you think.

quinn

Hello

Two solutions I can suggest.

1) Use Numbers v2 (Numbers '09) if you have it, which will behave properly in sorting table with formulae referencing external addresses.

2) Use absolute row addressing in formulae when referencing external addresses. E.g.

Use

=External::A$2

in lieu of

=External::A2

which will prevent Numbers v3 from changing row addresses in formula when the containing cell is moved by sorting operation.

Hope this may help

H

Hello

Here's another method using index columns to link two tables. This should be sort-safe in both Numbers v2 and v3. Also it does not use absolute references so that the formulae are easily filled across range.

The following tables are to demonstrate the concept.

E.g.

View A1 data A2 =INDEX(Data::A,MATCH(B2,Data::B,0),1) A3 =INDEX(Data::A,MATCH(B3,Data::B,0),1) A4 =INDEX(Data::A,MATCH(B4,Data::B,0),1) A5 =INDEX(Data::A,MATCH(B5,Data::B,0),1) A6 =INDEX(Data::A,MATCH(B6,Data::B,0),1) A7 =INDEX(Data::A,MATCH(B7,Data::B,0),1) A8 =INDEX(Data::A,MATCH(B8,Data::B,0),1) A9 =INDEX(Data::A,MATCH(B9,Data::B,0),1) A10 =INDEX(Data::A,MATCH(B10,Data::B,0),1) A11 =INDEX(Data::A,MATCH(B11,Data::B,0),1) B1 index B2 1 B3 2 B4 3 B5 4 B6 5 B7 6 B8 7 B9 8 B10 9 B11 10

Data A1 data A2 87 A3 81 A4 40 A5 3 A6 27 A7 14 A8 36 A9 13 A10 96 A11 77 B1 index B2 1 B3 2 B4 3 B5 4 B6 5 B7 6 B8 7 B9 8 B10 9 B11 10

Notes.

The forumla using reference by index:

=INDEX(Data::A,MATCH(B2,Data::B,0),1)

is to replace the direct reference:

=Data::A2

This referencing scheme is sort-safe. You may sort both Data table and View table freely without breaking inter-references.

Formula in View:A2 can be filled down.

Tables are built with Numbers v2 but this method should be applicable to Numbers v3 as well.

Good luck,

H