How do I filter a worksheet by every 60 rows in excel

[Guest]

I have an Excel spreadsheet that is 60,000 rows of data long but I only need
the data from every 60 rows and need to know how to filter it and delete all
the useless data so that the file is easier to work with.
thx

 

[Don Guillett]

A fairly easy way is to sort by what is the same on each row or
data>filter>autofilter

 

[Dave Peterson]

You could insert another column and put a formula like:

=mod(row(),60)

and drag down.

Apply the data|filter|autofilter and filter by the value you want--depends on
the first row you want--you'll see it when you do it!

 

[Max]

Just another option to play with ..

This strips out the required source data
into a new sheet.

An example ..

Assume the source data below is
in Sheet1, cols A to C, data from row2 down

SN Field1 Field2
1 Data1 Data11
2 Data2 Data12
3 Data3 Data13
4 Data4 Data14
5 Data5 Data15
6 Data6 Data16
7 Data7 Data17
8 Data8 Data18
9 Data9 Data19
10 Data10 Data20
11 Data11 Data21
12 Data12 Data22
13 Data13 Data23
14 Data14 Data24
etc

and we want to strip out every 3rd row of data,
i.e. rows with serial nos 1, 4, 7, 10, 13, ...

In Sheet2
-------------
Put the same col labels in A1:C1
(SN, Field1, Field2)

Put in A2:

=OFFSET(Sheet1!$A$2,ROW(A1)*3-3,COLUMN(A1)-1)

Copy A2 across to C2,
fill down until zeros appear,
signalling exhaustion of data

The resulting table will be:

SN Field1 Field2
1 Data1 Data11
4 Data4 Data14
7 Data7 Data17
10 Data10 Data20
13 Data13 Data23
etc

Then jsut do a copy > paste special > values elsewhere
to freeze the results, or kill the formulas in Sheet2
with an in-place: copy > paste special > values

If your source data set-up is identical to the example above,
and you want to strip data in every 60 rows,
i.e. rows with serial nos 1, 61, 121, ... etc
just change the formula in A2 in Sheet2 to:

=OFFSET(Sheet1!$A$2,ROW(A1)*60-60,COLUMN(A1)-1)

(Change the rows param in the OFFSET, i.e.
"ROW(A1)*3-3" to "ROW(A1)*60-60")

Copy A2 across as many cols
as there is in the source table,
then just fill down by 1,000+ rows or so
(since you have 60K+ rows)
or until zeros appear ..