Selecting every Nth column

[jlpeper]

Hi,

This seems like a very trivial function, but i can't figure out how to select every nth column in excell.

my data set is very large. I would like to select every 10 column and copy them into a new sheet. I do not use macros (or know how to access, build them) I am using excel 2013.

I have read about the "offset" function, but I cannot seem to get this to work.

Please help,

JP

 

[mumps]

If you are willing to try a macro then do the following: Hold down the ALT key and press the F11 key. This will open the Visual Basic Editor. In the menu at the top click 'Insert' and then click 'Module'. Copy and paste the macro into the empty code window that opens up. I've assumed that your destination sheet is named "Sheet2". If your destination sheet name is different, simply change "Sheet2" in the code ( 2 occurrences) to match the name of your destination sheet. Press the F5 key to run the macro. Close the code module window to return to your sheet. There are other quicker ways to run the macro such as assigning it to a button that you would click on your sheet or assigning it to a short cut key. The macro copies the columns to Sheet2.

Sub CopyCols()
    Application.ScreenUpdating = False
    Dim lColumn1 As Long
    lColumn1 = Sheets("Sheet1").Cells(1, Columns.Count).End(xlToLeft).Column
    Dim lColumn2 As Long
    lColumn = Sheets("Sheet2").Cells(1, Columns.Count).End(xlToLeft).Column
    Dim x As Long
    For x = 1 To lColumn1 Step 10
        If x = 11 Then x = 10
        lColumn2 = Sheets("Sheet2").Cells(1, Columns.Count).End(xlToLeft).Column
        Columns(x).EntireColumn.Copy Sheets("Sheet2").Cells(1, lColumn2 + 1)
    Next x
    Sheets("Sheet2").Columns(1).EntireColumn.Delete
    Application.ScreenUpdating = True
End Sub

 

[Joe4]

Welcome to the Board!

Let's say that you have data on Sheet1 starting on cell A1.
And on Sheet2, you want the value from Sheet1 cell A1 in cell A1, then the value from Sheet 1 cell K1 in cell B1, etc.

Then paste this formula in cell A1 on Sheet1 and copy for all rows and as far out as you need for your columns:

=OFFSET(Sheet1!$A1,0,(COLUMN()-1)*10)