1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Trying to copy every other 15 columns in excel...

Discussion in 'General Scripting Chat' started by jonvital, Sep 4, 2012.

  1. jonvital

    jonvital Junior Member

    Joined:
    Nov 13, 2011
    Messages:
    148
    Likes Received:
    48
    Location:
    Brooklyn, NY
    I have a spreadsheet of about 150k columns and I'm trying to copy every other 15 column to make an even 10k anyone know how to do this, tried youtube didn't find much of anything on this...
     
  2. SonicSam

    SonicSam Registered Member

    Joined:
    Aug 21, 2012
    Messages:
    57
    Likes Received:
    5
    Location:
    X
    You want to copy every 15th column and do what with it? In PHP I'm sure you could use an existing XLS/XLSX parser and then go from there, depends what you want to do with the information.

    Like, for each 15th column, do what?
     
  3. jonvital

    jonvital Junior Member

    Joined:
    Nov 13, 2011
    Messages:
    148
    Likes Received:
    48
    Location:
    Brooklyn, NY
    I just want to copy it , and extract it as a new excel sheet.
     
  4. SonicSam

    SonicSam Registered Member

    Joined:
    Aug 21, 2012
    Messages:
    57
    Likes Received:
    5
    Location:
    X
    After searching around, I found a way to copy every nth row using a macro.

    Since you want columns, you'll want to Transpose Rows/Columns, apply the following Macro, then Transpose Again.

    Code:
    Dim strValue As String
    Dim strCellNum As String
    Dim x As String
    x = 1
    
    
    For i = 1 To 150000 Step 15
        strCellNum = "A" & i
        strValue = Worksheets("Sheet1").Range(strCellNum).Value
        Debug.Print strValue
        Worksheets("Sheet2").Range("A" & x).Value = strValue
        x = x + 1
    Next
    Notice where I put 150000 and Step 15.

    It'll take "Sheet1" stuff, and put every 15th row into Sheet2.
     
    • Thanks Thanks x 1
  5. ijof9

    ijof9 Power Member

    Joined:
    Mar 27, 2010
    Messages:
    536
    Likes Received:
    594
    Occupation:
    CTO
    Location:
    Western Europe
    • Thanks Thanks x 1
  6. jonvital

    jonvital Junior Member

    Joined:
    Nov 13, 2011
    Messages:
    148
    Likes Received:
    48
    Location:
    Brooklyn, NY
    thanks a million, i just wanna say i love you guys mann lol ... now i just need to learn how to make it work lol + rep given -

    - and my mistake its every 15th ROW** not Column - my bad
     
    Last edited: Sep 6, 2012
  7. SonicSam

    SonicSam Registered Member

    Joined:
    Aug 21, 2012
    Messages:
    57
    Likes Received:
    5
    Location:
    X
    Did you end up figuring it out? ijof9's link mentions how to use a macro.
     
  8. kvmcable

    kvmcable Supreme Member

    Joined:
    Dec 28, 2010
    Messages:
    1,355
    Likes Received:
    2,815
    Occupation:
    24 year business owner - old school dude
    Location:
    KFC - BW3
    Get ASAP Utilities for Excel. It makes short work of all this kind of stuff. Best part is it's FREE.
     
  9. jonvital

    jonvital Junior Member

    Joined:
    Nov 13, 2011
    Messages:
    148
    Likes Received:
    48
    Location:
    Brooklyn, NY
    I forgot to mention Im using a MAC, trying to use @sonicsams advice but Im unfamiliar with how to use macros in that way, any assistance?
     
  10. sockpuppet

    sockpuppet Junior Member

    Joined:
    Nov 7, 2011
    Messages:
    155
    Likes Received:
    145
    if every row is in a new line you can use awk:
    Code:
    awk 'NR % 15 == 0' inputfilename > outputfilename 
     
  11. jonvital

    jonvital Junior Member

    Joined:
    Nov 13, 2011
    Messages:
    148
    Likes Received:
    48
    Location:
    Brooklyn, NY
    how can i use this on a mac?
     
  12. sockpuppet

    sockpuppet Junior Member

    Joined:
    Nov 7, 2011
    Messages:
    155
    Likes Received:
    145
    open a terminal window, it is in Applications/Utilities/Terminal

    navigate to the folder of your file and run awk
    Code:
    cd /path/to/excelfile
    awk 'NR % 15 == 0' name_of_excelfile > outputfilename