## How do I transfer every nth row in a column to another sheet?

603 Views 5 Replies Latest reply: Mar 21, 2013 3:17 AM by Hiroto
Calculating status...
Currently Being Moderated
Mar 19, 2013 9:06 AM

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)
• Level 6 (11,945 points)

nicole,

This may work for you:

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

select B2 and fill down as needed

• Level 6 (10,515 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.

• Level 7 (28,815 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

• Level 5 (4,660 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
``````
• Level 5 (4,660 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
``````

#### More Like This

• Retrieving data ...

#### Legend

• This solved my question - 10 points
• This helped me - 5 points
This site contains user submitted content, comments and opinions and is for informational purposes only. Apple disclaims any and all liability for the acts, omissions and conduct of any third parties in connection with or related to your use of the site. All postings and use of the content on this site are subject to the Apple Support Communities Terms of Use.