numbers formula problem

Here is a way:

add a new row for the formula that "filters" for the first occurrence:

B3=IF(COUNTIF($A$3:A3, B2)=0, B2, "")

this is shorthand for... select cell B3 then type (or copy and paste from here) the formula:

=IF(COUNTIF($A$3:A3, B2)=0, B2, "")

select cell B3, copy

select cells B3 thru U3, paste

As Wayne suggest, for each row of data you are going to need a helper row using IF COUNTIF. Then to retrieve sorted data, you can use the SMALL (sort ascending) or LARGE (sort descending) function. For example:

In row 3 I have this formula, B3, copied right:

=IF(COUNTIF($B2:B2,B2)=1,B2,"")

The above is the same as Wayne's approach.

Then in row 4 I have this formula in,B4, copied right:

=IFERROR(SMALL(3:3,B1),"")

The left column is defined as a Header Column.

Your localization may require you to substitute ; where I show , in formulas.

SG

Splitting a series of data into more than one column or row complicates life. But if it is important to do that (perhaps for ease of data entry) then here is one way to accomplish what I understand you are looking for:

This is the sample data table (I reduced the number of columns for demonstration purposes):

Then you can create a "Helper Table" that looks like this (in practice it will have many more rows):

The formula in A2 of the Helper Table, copied down as far as you can go until you get a red triangle range error is, is:

=INDEX(UNION.RANGES(FALSE,Data::$A$1,Data::$2,Data::$4),1,ROW())

(The $2 and $4 appear in this pasted formula means to anchor to those rows. You get that by clicking the row number to select the row)

The formula in B2, copied down, is:

=IFERROR(IF(COUNTIF(A$1:A2,A2)=1,A2,""),"")

The formula in C2, copied down, is:

=IFERROR(SMALL(B,ROW()−1),"")

(The B here is the column B address token which you get by clicking the column letter to select the column.)

Your summary table would look something like this:

The formula in B2, copied right until you get a blank, is:

=IFERROR(INDEX(Helper Table::$C,COLUMN()),"")

SG

Hi Rubberdonkey,

Just lurking around.

Courtesy of Google Translate

Mit freundlicher Genehmigung von Google Translate

Urliste = original list

Verteilung = distribution

Durchmesser = diameter

Häufigkeit = frequency

Rubberdonkey wrote:

Ok, I will try that one later, for now, I will use just one data row.

Durchmesser	9	10	11	12	13	13	14	15	16	17	18	19

How can I hide the orange (empty) cell? How can I filter rows?

Have you thought of using columns instead of rows? You can filter the table to hide rows with empty cells in the Durchmesser column.

(The lurker walks away).

Regards,

Ian.

columns are not an option for me. I just don't understand why you can't filter rows.

That's an interesting question. I don't think any spreadsheet software can filter rows. Neither Numbers nor Excel can. So in situations where filtering is important, you might, as Ian suggests, try to orient your data. If you're running the new Numbers 3.5 under Yosemite, you might experiment with the new Table > Transpose Rows and Columns functionality. Perhaps your data will work better in columns than you think. Sometimes it's hard to visualize how that would look. With the new built-in transpose functionality, it's much easier to give that a try.

SG

Hi SG,

You are so far ahead of me. Thank you for that tip.

Original table

Numbers 3.5 Menu > Table > Transpose Rows and Columns

Crikey! Too easy!

Regards,

Ian.

P.S. I hope the salties don't invade. Ian.

Hi Ian,

P.S. I hope the salties don't invade.

No salties spotted yet over here yet. Of course by the time one emerges from the depths it may too late.🙂 So I'm keeping Time Machine revved up, knowing one can never be too careful.

SG

Note that you can rearrange columns by dragging and dropping.

What filters do you have on your tables?

SG