Copying every Nth column to a new sheet (Excel 2007)

[Ceywood]

Hi everyone,

I'm new to anything that's not basic unfortunately, so apologies right off!

I'm currently trying to analyse a huge amount of data (each file contains a minimum of columns A --> BRL) and I'm trying to extract certain columns to the next sheet to simplify the analysis. For one example in the original sheet "Raw Data" I need the contents of column B (starting from cell B1) and then every 8th column after that (J, R, Z etc) and copy them into Sheet 1.

I'm unsure if I should be using the indirect or index function for this. I've done a lot of net surfing to try and find a solution but most posts provide the equation without an explanation of what each part of the code means. I'll have other files that will require slightly different column manipulations (eg copying every 12th column starting from column F etc), so if anyone has time to provide a brief explanation with the answer I'd be extremely grateful. (In the last months of a PhD and this data analysis stands between me and the END! )

Thanks very much.

 

[John Davis]

Perhaps a macro solution?

Sub Ceywood()

Dim i As Long

For i = 2 To 100 Step 8

Sheets("Sheet1").Columns(i).Value = Sheets("Raw Data").Columns(i).Value

Next i

End Sub

 

[Ceywood]

Thanks for your reply John. I've never written a macro either (sorry, very good with spectrophometric fitting software, highly ignorant with excel!) but I'll give it a go. Thanks again.

 

[Ceywood]

Actually - after much trial and error I think I've just solved this with a relatively simply code that's easily changeable for slight variations of where the data starts and how many columns to skip etc:

=INDEX(Me6TrenZnCu!$B1:$BRL1,8*(COLUMNS($A$1:A$1)-1)+1)

(The sheet with all the raw data is called Me6TrenZnCu adn the data goes out to column BRL.)

Cheers!