ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   New Users to Excel (https://www.excelbanter.com/new-users-excel/)
-   -   How to copy every Nth column to a new sheet (https://www.excelbanter.com/new-users-excel/446979-how-copy-every-nth-column-new-sheet.html)

Ceywood

How to copy every Nth column to a new sheet
 
Hi everyone - apologies in advance, I am completely ignorant about excel outside of the really basic!

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 (or something else!). If possible I'd like to avoid using VBL or making macros.

I've done a lot of net surfing to try and find a solution but everyone's doing something slightly different and 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.

Ceywood

Actually - after much trial and error I think I've just solved this:

=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!


All times are GMT +1. The time now is 12:32 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com