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

nicole,

This may work for you:

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

select B2 and fill down as needed

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.

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

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
                set {j1, j2} to {column 1's address, column -1's address}
            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
                set {i1, i2} to {row 1's address, row -1's address}
            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

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
                set {j1, j2} to {column 1's address, column -1's address}
            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
                set {i1, i2} to {row 1's address, row -1's address}
            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

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>

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

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