[ACCEPTED]-Copy every nth line from one sheet to another-excel
In A1 of your new sheet, put this:
... 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:
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.
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
A1 is cell
(1,1) and if we add
(1,2) to that 47 we get
(2,3) which corresponds to cell
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
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.
ADDRESS takes two integer inputs 35 and returns the address/name of the cell 34 as a string. For instance,
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
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.
Consider the following screenshot 22 of a spreadsheet. The headers (first row) contains 21 the call used in the rows below.
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.
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.
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).
D contains a correct function 6 using the
E contains an 5 incorrect attempt at using the
INDRECT approach, but 4 where we forgot to subtract. Thus we skipped 3 some rows initially. The same problem as 2 with column
F contains a correct 1 function using the
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:
More Related questions