How to copy entire rows of data matching one criteria.

Question

Level 1

30 points

How to copy entire rows of data matching one criteria.

I have a main table set up like this:

Column 1: Student Name
Column 2: Sport (e.g., Swimming, Football, Tennis, etc.)
Column 3: Score they received in each sport

Now, I want to create separate tables on different sheets for each sport. For example, one table for Tennis that shows all student names and their scores in Tennis. So in total, I need around 6 tables—one for each of the 6 sports.

I would like these tables to update automatically whenever I add a new student and their score in the main table.

Could you please tell me what formula I should use to create these different tables?

I’m not very confident with formulas, , so please explain each step clearly, including what formula to use and where to place it (which row and column).

Thanks so much for your help!

MacBook Pro 14″

Posted on Jul 20, 2025 11:34 AM

Reply

Answer 1

Badunit

Level 7

30,336 points

Jul 25, 2025 6:32 PM in response to pheonix2610

If the word "football" is in cell A1 then the formula would be

=FILTER(Sheet 1::Table 1::A:C,Sheet 1::Table 1::B=A$1)

If it is somewhere other than cell A1 then use that other cell in the formula.

Or you could change the table name to Football and the formula would be

=FILTER(Sheet 1::Table 1::A:C,Sheet 1::Table 1::B=TEXTBEFORE(REFERENCE.NAME(A$1,1),"::"))

Reply

Answer 2

max2807

Level 1

0 points

Jul 26, 2025 2:50 PM in response to Badunit

I read your response in 255022338 . As this thread has been closed I am writing to you here.

My region is Austria (Österreich) and thus all the names of Number's functions are in German. The German equivalent of the OFFSET function is BEREICH.VERSCHIEBEN which uses ";" as parameter seperator. Thus your formula G2 =SUM(OFFSET(G2,−1,0,1,1))−D2+E2 had to be changed to BEREICH.VERSCHIEBEN(G2;−1;0;1;1) but that doesn't work because (G2;−1;0;1;1) refers to "Balance" in your example. If you would be so kind and verify if your original formula is working in the current version of Numbers - and if not - let me know what function would work now, I would be very grateful.

Reply

Answer 3

Badunit

Level 7

30,336 points

Jul 26, 2025 5:10 PM in response to max2807

In G2 it refers to G1 which has the word "balance" but I put SUM around it to turn it into a zero, which avoids an error triangle from doing math with a string. When filled down to the other rows it refers to the number in the row above. What did not work?

Reply

Answer 4

Badunit

Level 7

30,336 points

Jul 27, 2025 3:20 PM in response to max2807

Seeing that you know English well,

Use File->Advanced->Language&Region to change the spreadsheet to English,
Write your formulas using the English function names,
Change it back to System-German to convert them all to German.

Reply

Answer 5

pheonix2610 Author

Level 1

30 points

Jul 27, 2025 6:00 PM in response to Badunit

Thanks. It worked.

But, for all these formulas to work, looks like I need to have the rows in advance. Rows are not getting created when new items are added in the main table.

For example, my football sheet has 10 Empty rows and when I add upto 10 items in main sheet it works but when an 11th item about "Football" is added in the main sheet, the football sheet gets an error. If I increase the row to 11 manually in football sheet, it works. Is there any way to make the sheet automatically add those rows.

Reply

Answer 6

pheonix2610 Author

Level 1

30 points

Jul 25, 2025 3:25 PM in response to Badunit

Thank you so much explaining this. It worked.

You are a saviour.

Is it possible to filter by a manual name instead of using the sheet name?

Reply

Answer 7

pheonix2610 Author

Level 1

30 points

Jul 25, 2025 3:36 PM in response to Badunit

Is it possible to filter by a manual name instead of using the sheet name?

For example, my sheet name is Football 2025. But, the Sport will be Football.

Reply

Answer 8

max2807

Level 1

0 points

Jul 27, 2025 1:11 PM in response to Badunit

Thanks for the quick reply! I tried it again today: your solution works fine. I got irritated by replacing the English function names with German ones and then made errors with the complete formula.

Reply

Answer 9

Badunit

Level 7

30,336 points

Jul 27, 2025 7:13 PM in response to pheonix2610

No way to make a table add rows automatically. You can add a whole lot of extra rows and use a filter to hide the empty ones. Filter for column A to show if "cell is not blank".

Reply

Answer 10

Badunit

Level 7

30,336 points

Jul 20, 2025 2:32 PM in response to pheonix2610

Assuming you have Numbers 14.4 with all the new array functions (like FILTER):

First screenshot is the data table you described.

There are no formulas in the data sheet (none required for doing what you requested), just the data.

Next is the sheet for "football"

To make this sheet and table,

Add a new sheet

Rename it to Football

Row 1 is typed in headers. No formulas.

cell A2 formula =FILTER(Sheet 1::Table 1::A:C,Sheet 1::Table 1::B=TEXTBEFORE(REFERENCE.NAME(A$1,2),"::"))

This formula will automatically spill into all the other rows where it is needed

To make the other sheets,

Duplicate the sheet for Football

Rename the new sheet as Soccer

Repeat for the other sports

The formula uses the sheet name to filter the data.

If you don't want to see the sport name as column B in the filtered tables, you can hide that column.

Breakdown of the formula:

REFERENCE.NAME(A$1,2) gets a string representation of the cell address. Result is "Football::Table 1::A1"

TEXTBEFORE returns the part of the string before the first "::". Result is "Football"

FILTER returns the values from Table 1::A:C where Table 1::B = "Football"

Reply