Numbers: Copy/Paste from multiple tables into 1 single Table?

Question

Level 1

8 points

Numbers: Copy/Paste from multiple tables into 1 single Table?

I would appreciate some guidance in creating a copy/paste script from multiple numbers tables into a different single table within the same numbers document.

The columns in the source table are in different positions to the destination table.

So for example, i would want to copy…

Sheet 1, Table 1 Cell A3 copy to Sheet 2 Table Z Cell B2

Sheet 1, Table 1 Cell B3 copy to Sheet 2 Table Z Cell C2

Sheet 1, Table 1 Cell C3 copy to Sheet 2 Table Z Cell E2

Sheet 1, Table 1 Cell D3 copy to Sheet 2 Table Z Cell F2

Sheet 1, Table 1 Cell E3 copy to Sheet 2 Table Z Cell G2

Sheet 1, Table 1 Cell G3 copy to Sheet 2 Table Z Cell I2

Sheet 1, Table 1 Cell J3 copy to Sheet 2 Table Z Cell H2

and repeat the copy/paste on subsequent rows of each table until there is an empty row in Sheet 1 Table 1 (or if this is difficult, say set the repeat to a max of 30 rows).

The script would then need to move on to Sheet 1 Table 2, and do the same copying to Sheet 2 Table Z (from where the previous copy/paste finished in Table Z).

Repeat process would finalise with Sheet 1 Table 6.

The Paste part would need to be pasting values, as the cells in the source tables do contain some formulas.

At the moment this is all done manually and does take some to to undertake.

If anyone can help, i would be very grateful.

Thanks,

Colin

Numbers-OTHER, OS X Mountain Lion (10.8)

Posted on Dec 18, 2013 8:39 AM

Reply

Answer 1

Dec 18, 2013 10:40 AM in response to interceptor3

Interceptor,

are you trying to aggregate data from severl tables into a single table? If so you whould be able to do this without a script. The function indirect() will allow you to construct the proper formula, which you may fill over (in the same row) to the appropriate cells, then fill down.

Here is a small example:

There are three data tables ("Table 1", "Table 2", and "Table 3"). And a summary "Table Z"

In table Z make the first row a header (as shown):

Use column A to enter the Sheet name and colomn B to enter the table name.

In row 1 (the header), enter the cells you want to get

in cell C2 type (or copy and paste from here) the formula:

=INDIRECT($A2&"::"&$B2&"::"&C$1)

now select cell C2, copy now select cell C2 thru H2, paste

now select the cells C2 thu H2, hover the corsor over the bottom edge of the selection, and drag the yellow cirlc down as needed to fill the formula down.

Update the sheet and table names as needed for each row

Reply

Answer 2

interceptor3 Author

Level 1

8 points

Dec 19, 2013 3:25 AM in response to interceptor3

Resolved. Wayne, you put me along the right lines. You may indeed have a much better solution.

My solution isn't pretty, but does work.

Instead of having the reference cells in the row under the header, i have added every individual cell in columns to the right of the main data (they can be hidden). It would have been a big problem if the source tables had unlimited rows, but in this case we have a max of around 30 rows.

You can see your ref cells in row2 (which will be deleted), and my new cells in cols N-U. As i said, not pretty, and i would love a simpler formula or script (especially if it just copied the rows containing data from each table), but for now it does the job.

Thanks for introducing that formula function, i think i may have more uses for it.

My friend, a lecturer who i am helping with all these tables to create classroom simulations, will be impressed.

Reply

Answer 3

interceptor3 Author

Level 1

8 points

Dec 19, 2013 2:21 AM in response to Wayne Contello

Thanks Wayne, i appreciate your quick response, this works to a certain extent, but will only copy the first row of tables 1-3 i.e. C$1 in your example copies cell A3 from table 1.

If you repeat sheet 2 table 1 a few more times in table Z, add an equal amount of new rows in table 1, then drag down the formula, it will only continue to duplicate the data from cell A3, rather than content from the remaining rows in table 1.

In my case i have 30 rows in each source table! Perhaps i didn't mention that.

Any thoughts?

Reply