8 Replies Latest reply: May 8, 2014 12:15 PM by SGIII
mtnicole Level 1 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?
    Wayne Contello Level 6 Level 6 (13,615 points)

    nicole,

     

    This may work for you:

    Screen Shot 2013-03-19 at 12.25.59 PM.png

     

    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?
    Badunit Level 6 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?
    Barry Level 7 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.

    Picture 1.png

    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?
    Hiroto Level 5 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
                    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
    
  • 5. Re: How do I transfer every nth row in a column to another sheet?
    Hiroto Level 5 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
                    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
    
  • 6. Re: How do I transfer every nth row in a column to another sheet?
    rrvc Level 1 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?
    Barry Level 7 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?
    SGIII Level 5 Level 5 (4,115 points)

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

     

    Screen Shot 2014-05-08 at 3.05.33 PM.png

     

     

      =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