You can make a difference in the Apple Support Community!

When you sign up with your Apple Account, you can provide valuable feedback to other community members by upvoting helpful replies and User Tips.

Looks like no one’s replied in a while. To start the conversation again, simply ask a new question.

Can Numbers automatically copy sorted data from sheet to sheet?

In the first sheet, I have 5 columns of categories and hundreds of rows of data.


Is there a formula to auto-fill additional sheets based on a variable in one of the columns from the full list?


For example, I have a document that lists TV shows from a variety of streamers/studios. I would like to create separate sheets for each studio, and have the related shows for that distributor consolidated in its own sheet.


Thank you in advance for the feedback.

Posted on Jun 5, 2023 3:16 AM

Reply

Similar questions

2 replies

Jun 5, 2023 5:30 AM in response to high fivers

If I were doing this, I would leave the data all together in one table and use filters to view subsets as needed, together with SUMIFS, COUNTIFS or Pivot Tables to extract summaries. That is usually more efficient. If I really needed the data in separate tables I would apply the filter, command-c to copy the visible cells, click once in the cell of an existing table, and command-v to paste. Quick and easier.


However, if you really want to split the data in an automated fashion into separate tables, you could do this, which is more complicated, something like this:




The formula in B2 of the destination table, filled down the column, is:


=REGEX.EXTRACT(TEXTJOIN("~",1,Table 1::A:B),"([\w| ]+)~"&A$1&"\b",ROW(cell)−1,1)


The formula in C2, filled down and right is:


=XLOOKUP($A2,Table 1::$A,Table 1::C,"nf")


If your region uses , as the decimal separator replace , in the formulas with ; That gives these:


=REGEX.EXTRACT(TEXTJOIN("~";1;Table 1::A:B);"([\w| ]+)~"&A$1&"\b";ROW(cell)−1;1)


=XLOOKUP($A2;Table 1::$A;Table 1::C;"nf")


This approach has the advantage of not needing extra columns, and doing the job with just two formulas.


But REGEX, even this relatively simple expression, can look intimidating. You can click the warning triangle in Column A to see what REGEX is assembled by the formula.




In general, though, I've founding sticking to the built-in filters and (if separate tables are really needed) good old tried-and-true copy paste can be the most reliable approach, and much quicker than it might seem.


SG

Jun 6, 2023 12:06 AM in response to high fivers

Hi high fivers,

SGIII wrote:

If I were doing this, I would leave the data all together in one table and use filters to view subsets as needed

I'm with SG on this. Filters are useful and simple. Here is a method where you can quickly and easily change the filter on the original table.


Cell B2 is a Pop-Up Menu


Add an extra column (D). You can hide Column D when all is working well.

Formula in D2 (and fill down): IF(B$1="Show All","Yes",IF(B$1=B2,"Yes",""))

Insert a filter and choose a Studio from B2:


Regards,

Ian.

Can Numbers automatically copy sorted data from sheet to sheet?

Welcome to Apple Support Community
A forum where Apple customers help each other with their products. Get started with your Apple Account.