[ACCEPTED]-Copy every nth line from one sheet to another-excel

In A1 of your new sheet, put this:

=OFFSET(Sheet1!$A$1,(ROW()-1)*7,0)

... and 6 copy down. If you start somewhere other 5 than row 1, change ROW() to ROW(A1) or some 4 other cell on row 1, then copy down again.

If 3 you want to copy the nth line but multiple 2 columns, use the formula:

=OFFSET(Sheet1!A$1,(ROW()-1)*7,0)

This can be copied 1 right too.

In my opinion the answers given to this 57 question are too specific. Here's an attempt 56 at a more general answer with two different 55 approaches and a complete example.

The OFFSET approach

OFFSET takes 54 3 mandatory arguments. The first is a given 53 cell that we want to offset from. The next 52 two are the number of rows and columns we 51 want to offset (downwards and rightwards). OFFNET returns 50 the content of the cell this results in. For 49 instance, OFFSET(A1, 1, 2) returns the contents of cell 48 C2 because A1 is cell (1,1) and if we add (1,2) to that 47 we get (2,3) which corresponds to cell C2.

To get 46 this to return every nth row from another 45 column, we can make use of the ROW function. When 44 this function is given no argument, it returns 43 the row number of the current cell. We can 42 thus combine OFFSET and ROW to make a function that 41 returns every nth cell by adding a multiplier 40 to the value returned by ROW. For instance 39 OFFSET(A$1,ROW()*3,0). Note the use of $1 in the target cell. If 38 this is not used, the offsetting will offset 37 from different cells, thus in effect adding 36 1 to the multiplier.

The ADDRESS + INDIRECT approach

ADDRESS takes two integer inputs 35 and returns the address/name of the cell 34 as a string. For instance, ADDRESS(1,1) return "$A$1". INDIRECT takes 33 the address of a cell and returns the contents. For 32 instance, INDIRECT("A1") returns the contents of cell 31 A1 (it also accepts input with $'s in it). If 30 we use ROW inside ADDRESS with a multiplier, we can 29 get the address of every nth cell. For instance, ADDRESS(ROW(), 1) in 28 row 1 will return "$A$1", in row 2 will return 27 "$A$2" and so on. So, if we put this inside INDIRECT, we 26 can get the content of every nth cells. For 25 instance, INDIRECT(ADDRESS(1*ROW()*3,1)) returns the contents of every 24 3rd cell in the first column when dragged 23 downwards.

Example

Consider the following screenshot 22 of a spreadsheet. The headers (first row) contains 21 the call used in the rows below. Column 20 A contains our example data. In this case, it's 19 just the positive integers (the counting 18 continues outside the shown area). These 17 are the values that we want to get every 16 3rd of, that is, we want to get 1, 4, 7, 10, and 15 so on.

Column B contains an incorrect attempt 14 at using the OFFSET approach but where we forgot 13 to use $. As can be seen, while we multiply 12 by 3, we actually get every 4th row.

Column 11 C contains an incorrect attempt at using 10 the OFFSET approach where we remembered to use 9 $, but forgot to subtract. So while we do 8 get every 3rd value, we skipped some values 7 (1 and 4).

Column D contains a correct function 6 using the OFFSET approach.

Column E contains an 5 incorrect attempt at using the ADDRESS + INDRECT approach, but 4 where we forgot to subtract. Thus we skipped 3 some rows initially. The same problem as 2 with column C.

Column F contains a correct 1 function using the ADDRESS + INDRECT approach.

If I were confronted with extracting every 11 7th row I would “insert” a column before 10 Column “A” . I would then (assuming that 9 there is a header row in row 1) type in 8 the numbers 1,2,3,4,5,6,7 in rows 2,3,4,5,6,7,8, I 7 would highlight the 1,2,3,4,5,6,7 and paste 6 that block to the end of the sheet (700 5 rows worth). The result will be 1,23,4,5,6,7,1,2,3,4,5,6,7,1,2,3,4,5,6,7……. Now 4 do a data sort ascending on column “A”. After 3 the sort all of the 1’s will be the first 2 in the series, all of the 7’s will be the 1 seventh item.

insert a new column and put a series in 4 1,2,3,4, etc. Then create another new column 3 and use the command =if(int(a1/7)=(a1/7),1,0) you 2 should get a 1 in every 7th row, filter 1 the column on the 1

Highlight the 7th line. Paintbrush the 6 format for the first 7 lines a few times. Then 5 do a bigger chunk of paintbrush copying 4 the format until you are done. Every 7th 3 line should be highlighted. Filter by color 2 and then copy and paste (paste the values) from 1 the highlighted cells into a new sheet.

Create a macro and use the following code 2 to grab the data and put it in a new sheet 1 (Sheet2):

Dim strValue As String
Dim strCellNum As String
Dim x As String
x = 1

For i = 1 To 700 Step 7
    strCellNum = "A" & i
    strValue = Worksheets("Sheet1").Range(strCellNum).Value
    Debug.Print strValue
    Worksheets("Sheet2").Range("A" & x).Value = strValue
    x = x + 1
Next

Let me know if this helps! JFV

If your original data is in column form 6 with multiple columns and the first entry 5 of your original data in C42, and you want 4 your new (down-sampled) data to be in column 3 form as well, but only every seventh row, then 2 you will also need to subtract out the row 1 number of the first entry, like so:

=OFFSET(C$42,(ROW(C42)-ROW(C$42))*7,0)