Numbers : How to copy entire rows of data matching one criteria.

Don't forget that you don't even need to use an extra column and a formula to do this if you take advantage of the built-in Categories feature of Numbers:

Then, to add a new player just click in the last row of a sport and hit return.

Numbers automatically fills in the category (sport) for you and you fill in the rest of the row.

If you need to calculate a statistic (average, minimum, maximum for each sport just enter it here after clicking the 'gear' symbol:

SG

pheonix2610 wrote:

But, I don't want my swim students to see the score of Tennis students while I categorise and sort the data. I would rather switch the sheets to show them each of their scores.

That's when a copy Paste and Match Style works really well. Set up your swim table, your tennis table, etc. and format as desired. Then to update just select, command-c, click in the destination table and Edit > Paste and Match Style. The destination tables will expand automatically to accept your data. Twenty different sports? Still done in about a minute. Fewer sports? Done even quicker.

SG

Hi phoenix,

"find value" is what Numbers provides if you/ve written the formula by choosing the type of MATCH from the menu provided in that 'hint'. Clicking the disclosure triangle near the riht end of the lozenge opens a menu showing the available choices. Entering a zero in that position has the same effect—MATCH will accept only an exact match for the search value, which is what you want in this case.

Automatic addition of new rows as needed is not a supported feature in Numbers. Set these tables to the number of rows you expect to need, then add rows any time the table gets close to being full. As you've noted, the formula gets automatically filled into those new rows. If that fails to happen, you can use the Fill control (small yellow circle that appears on a cell edge when you bring the pointer close to the edge of the cell in the direction you want to fill) to manually fill the formula down as far as it is needed.

Regards,

Barry

Why have separate tables? Generally it’s a good idea to keep like data together.

Whenever you need data elsewhere (say for a report) just copy-paste. It takes a second or so and is much easier than setting up extra columns with formulas.

SG

Hi phoenix,

Add a column to the main table to contain an index, giving each row a distinct datum which can be searched.

The formula shown below the table is entered in D2, then filled down to the end of that column.

B2 gets the value in 'this row' of column B.

COUNTIF counts the occurrences of the value in 'this row' of column B in the (expanding) range of B1 to 'this row' of B.

The concatenation operators ( & ) join the contents of B2, a space, and the count returned by COUNTIF into a single string, and return that string to the cells containing the formula.

The formula shown in entered in B2 of the smaller table to the left, and is filled down to the end of that column.

The same formula, with "Table 1::C" replaced by "Table 1::A" is placed in cell A2 and filled down.

The second small table is a copy of the selected one, with a different sport entered in cell A1.

The core formula here is the part beginning with INDEX

A1 gets the sport name, Row gets the row number of 'this row', one is subtracted from it. The sport name, a space and the number are joined to form a search value (Swim 1) for MATCH.

MATCH looks for that value in column D of Table 1, and returns a number indocating it's position in the list in that column.

INDEX takes the number and returns the value in that row of column C of Table 1.

Search values not found will cause an error message from MATCH. This is trapped by the IFERROR function in which the core formula is wrapped, and IFERROR returns a null string, which appears 'blank' in the cell.

Regards,

Barry

Yes Categories are highly versatile. They can save a LOT of work!

You can turn them off and on with the switch in the right sidebar..

Just in case, save a copy of your main data table every once in a while.

SG