8 Replies Latest reply: May 8, 2014 12:15 PM by SGIII
Level 1 (0 points)

I need to transfer data from one sheet to another, but I am only interested in every 4th row in a column-- ex: I want the data in C11, C15, C19 on sheet 1 to be transfered to A2, A3, and A4 in sheet 2. I would like to do this without copy and pasting and then going back and deleting unwanted data. Thanks for any advice!

MacBook Pro, Mac OS X (10.7.5)
• ###### 1. Re: How do I transfer every nth row in a column to another sheet?
Level 6 (13,615 points)

nicole,

This may work for you:

B2=OFFSET(\$A\$2, 4*(ROW()-2), 0)

select B2 and fill down as needed

• ###### 2. Re: How do I transfer every nth row in a column to another sheet?
Level 6 (10,815 points)

I'm not sure I totally get your question.  Wayne has shown an example of a formula that will "transfer" every 4th row of data but if you need the original data to automatically "delete" itself, that isn't going to happen nor can it happen using formulas. You could do it with an Applescript but not with formulas.

• ###### 3. Re: How do I transfer every nth row in a column to another sheet?
Level 7 (29,180 points)

Essentially the same solution as Wayne's. The only changes are:

1. Added a fixed offset to start the transfer from C10.

2. Moved the base cell to column C, the column from which the data is being read.

3. Use a separate table to receive the transfered data. This table may be moved to a separate sheet. numbers will automatically adjust the formulas as necessary.

Blue markers in column B are there to let me check that the correct data was being transfered. They are not necessary to the table's working.

Formula:

Table 2 :: B2, and filled down: =OFFSET(Table 1 :: \$C\$1,10+(4*(ROW()-2)),0)

Regards,

Barry

• ###### 4. Re: How do I transfer every nth row in a column to another sheet?
Level 5 (5,015 points)

Hello

Here's a AppleScript script to transfer every k'th row in current selection to new table.

Copy and paste the code listed below in a new document of AppleScript Editor.app, select the source range in Numbers, run the script and it will duplicate the current table and remove unwanted rows and columns in the duplicate and unwanted rows in the source. If you want to keep the source unchanged, set the property _move to false. Currently k = 4.

Hope this may help,

H

```main()
on main()
script o
property k : 4 -- extraction interval; every k'th row (starting from 1st row in selection) is extracted
property _move : true -- whether to move rows or copy rows; true = move, false = copy

tell application "Numbers"
activate
-- get selection specifiers
set {range:_range, table:_table, sheet:_sheet} to my _selection(document 1)
if _range is missing value then return

-- duplicate table
tell application "System Events"
tell process "Numbers"
keystroke return using {control down} -- move focus to table
keystroke "d" using {command down} -- edit > duplicate
end tell
end tell
tell _sheet
set _table1 to table -1 -- last table which is duplicated table
end tell

-- delete unnecessary columns
tell _range
end tell
tell _table1
repeat with j from 1 to count columns
if j < j1 or j > j2 then delete column j
end repeat
end tell

-- delete unnecessary rows
tell _range
end tell
tell _table1
repeat with i from (count rows) to 1 by -1
if i < i1 or i > i2 then
delete row i
else if (i - i1 + 1) mod k ≠ 1 then
delete row i
end if
end repeat
end tell

-- delete original rows if _move = true
if _move then
tell _table
repeat with i from (count rows) to 1 by -1
if i < i1 or i > i2 then
else if (i - i1 + 1) mod k = 1 then
delete row i
end if
end repeat
end tell
end if

-- select cells in new table
tell _table1
set selection range to cell range
end tell
end tell
end script
tell o to run
end main

on _selection(doc)
(*
reference doc : target document
return record : {range:_range, table:_table, sheet:_sheet}
_range = reference to named range in selection
_table = table object to which selection range belongs
_sheet = sheet object to which selection range belongs
*)
(*
Limitation
Numbers allows to select uncontinuous regions
but its scripting interface does not provide decent method to retrieve them.

If uncontinuous regions are selected, 'selection range' returns the minimum continuous region
which includes all the regions in selection.
*)
script o
property parent : {}
property pp : {}
local q, r, s, _range, _table, _sheet
tell application "Numbers"
set pp to doc's every sheet's every table's selection range as list
repeat with p in my pp -- per sheet
set q to p's every reference -- retrieve object (filtering out missing value)
if q ≠ {} then
set q to q's item 1 -- selection range object [1]
set r to q as record -- selection range object specifier record [2]
set _table to r's every reference's item 1 -- container table reference [3]
set s to (a reference to _table's selection range) -- selection range reference [4]
set _range to (a reference to _table's range (s's name)) -- named range reference [5]
set _sheet to (_table as record)'s every reference's item 1 -- container sheet reference [3]
return {range:_range, table:_table, sheet:_sheet}
end if
end repeat
return {range:missing value, table:missing value, sheet:missing value}
end tell
(*
[1] class specifier for 'range' is broken in Numbers 09
[2] «class want» value is broken in Numbers 09
[3] simple method to get «class from» value without asking for «class from» key which causes trouble in recompilation of the token 'from'.
[4] proper reference of selection range object
[5] proper reference of named range object
*)
end script
tell o to run
end _selection
```
• ###### 5. Re: How do I transfer every nth row in a column to another sheet?
Level 5 (5,015 points)

Hello

I noticed wrong code in deleting unnecessary columns. Deletion order must be reversed.

Here's the corrected script just in case.

All the best,

H

```main()
on main()
script o
property k : 4 -- extraction interval; every k'th row (starting from 1st row in selection) is extracted
property _move : true -- whether to move rows or copy rows; true = move, false = copy

tell application "Numbers"
activate
-- get the selection specifiers
set {range:_range, table:_table, sheet:_sheet} to my _selection(document 1)
if _range is missing value then return

-- duplicate table
tell application "System Events"
tell process "Numbers"
keystroke return using {control down} -- move focus to table
keystroke "d" using {command down} -- edit > duplicate
end tell
end tell
tell _sheet
set _table1 to table -1 -- last table which is duplicated table
end tell

-- delete unnecessary columns
tell _range
end tell
tell _table1
repeat with j from (count columns) to 1 by -1
if j < j1 or j > j2 then remove column j
end repeat
end tell

-- delete unnecessary rows
tell _range
end tell
tell _table1
repeat with i from (count rows) to 1 by -1
if i < i1 or i > i2 then
remove row i
else if (i - i1 + 1) mod k ≠ 1 then
remove row i
end if
end repeat
end tell

-- delete original rows if _move = true
if _move then
tell _table
repeat with i from (count rows) to 1 by -1
if i < i1 or i > i2 then
else if (i - i1 + 1) mod k = 1 then
remove row i
end if
end repeat
end tell
end if

-- select cells in new table
tell _table1
set selection range to cell range
end tell
end tell
end script
tell o to run
end main

on _selection(doc)
(*
reference doc : target document
return record : {range:_range, table:_table, sheet:_sheet}
_range = reference to named range in selection
_table = table object to which selection range belongs
_sheet = sheet object to which selection range belongs
*)
(*
Limitation
Numbers allows to select uncontinuous regions
but its scripting interface does not provide decent method to retrieve them.

If uncontinuous regions are selected, 'selection range' returns the minimum continuous region
which includes all the regions in selection.
*)
script o
property parent : {}
property pp : {}
local q, r, s, _range, _table, _sheet
tell application "Numbers"
set pp to doc's every sheet's every table's selection range as list
repeat with p in my pp -- per sheet
set q to p's every reference -- retrieve object (filtering out missing value)
if q ≠ {} then
set q to q's item 1 -- selection range object [1]
set r to q as record -- selection range object specifier record [2]
set _table to r's every reference's item 1 -- container table reference [3]
set s to (a reference to _table's selection range) -- selection range reference [4]
set _range to (a reference to _table's range (s's name)) -- named range reference [5]
set _sheet to (_table as record)'s every reference's item 1 -- container sheet reference [3]
return {range:_range, table:_table, sheet:_sheet}
end if
end repeat
return {range:missing value, table:missing value, sheet:missing value}
end tell
(*
[1] class specifier for 'range' is broken in Numbers 09
[2] «class want» value is broken in Numbers 09
[3] simple method to get «class from» value without asking for «class from» key which causes trouble in recompilation of the token 'from'.
[4] proper reference of selection range object
[5] proper reference of named range object
*)
end script
tell o to run
end _selection
```
• ###### 6. Re: How do I transfer every nth row in a column to another sheet?
Level 1 (0 points)

I 'm not getting this formula

I need to transfer data from one sheet to another, ex: I want the data in F4, F8, F12 ,F16 So on F208 to on sheet 1 to be transfered to F4, F5, and F55 in sheet 2. I would like to do this

My Email ID is ********

Give ur mail ID ,So I will send that file

<E-mail Edited by Host>

• ###### 7. Re: How do I transfer every nth row in a column to another sheet?
Level 7 (29,180 points)

Hi rrvc,

"I 'm not getting this formula"

What is it you are not getting with respect to the formula?

Table 1 is the Table containing the original data.

Tables 2, which may be on another Sheet, but must be in the same Document, is the table to which the formula copies the selected data.

In the original, the first copied data is to be placed in cell B2 of Table 2, so the first instance of the formula is entered in that cell. In your case, you want the first copied data to be in F4, so that is the cell into which you enter the formula.

Original: =OFFSET(Table 1 :: \$C\$1,10+(4*(ROW()-2)),0)

Table 1::C1 is the base cell for OFFSET. It is located in a header row to prtect it from being sorted to a different location, and made absolute (Table 1::\$C\$1) to keep the reference fixed to that cell as the formula is filled down to column receiving the data.

You are collecting data from column F, so the base should be Table 1::\$F\$1 if your formula is going to be a functional match for the one in my initial post.

10+(4*(ROW()-2)) determines the row-offset from the base to the cell containing the wanted data.

The first datum is in C11, 10 rows below the base.

Your first datum is in F4, 3 rows below the base.

The first instance of the original formula is in A2, so ROW() will return 2, ROW()-2 will return zero, and 4*(ROW()-2) will return zero.

Your first formula is in F4, so to get the same zero result, you need to subtract 4 where the original subtracted 2.

The row-offset part of your formula should look like this: 3+(4*(ROW()-4))

In both the original and your case, data is being copied from the column contining the base, so the column-offset in both cases is 0.

Putting these together, your formula, entered in Table 2::F4, should look like this:

=OFFSET(TABLE 1::\$F\$1,3+(4*(ROW()-4)),0)

Fill down to F55.

(Replace "Table 1" with the name of your data table.)

Regrds,

Barry

• ###### 8. Re: How do I transfer every nth row in a column to another sheet?
Level 5 (4,115 points)

Or, using the slightly more succinct INDEX function, you could do this:

=INDEX(Table 1::F,4+4×(ROW()−4))

The "trick" I used in puzzling out Barry's and Wayne's formula was to note that the 4 x ROW() - x part of the expression must evaluate to 0 when the formula is in row 4 (so that, after adding the 3 for OFFSET or 4 for INDEX, the entire expression looks on row 4 in Table 1).  So x has to be 4.

SG