Copy Every 5th Row


H

Hardeep_kanwar

Hi! Experts

What is the Easiest way to copy Every 5th Row. I have 5000 row and i want to
Copy every 5th Row. Using Formulas not Macros.

Thanks in Advance

Hardeep kanwar
 
Ad

Advertisements

S

Stefi

Enter this formula in the 1st row of an empty helper column:

=MOD(ROW()/5,1)=0

Fill it down as necessary!
Autofilter TRUEs in helper column!
Edit>Goto>Advanced>CurrentRegion
Edit>Goto>Advanced>Visible cells only

Copy and paste!


Regards,
Stefi

„Hardeep_kanwar†ezt írta:
 
D

Dave Curtis

Hi,
Another method:
Assuming your data is in column A starting in A1, then in B1 enter

=INDIRECT("A"&5*ROW()-4)

and drag down. This will copy the 1st, 6th, 11th 16th etc.

Dave
 
H

Hardeep_kanwar

Hey Dave When i insert your Function it give me the result like this:

5
10
15
20
25
30
35
40
45
50
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0

Hardeep kanwar
 
D

Dave Curtis

HI,

Sorry, I'll have another go. I misread what you needed.

Assume your data is in Sheet1.
Insert a defined name, and define Sheet1!$A$1 as "reference" (without the
quotes)
Then on Sheet2, in A1, enter the following formula:

=OFFSET(reference,5*ROW()-5,COLUMN()-1)

and copy down and across as far as necessary.

This should display ever fifth row from Sheet1.

Dave
 
Ad

Advertisements

S

Stefi

You are welcome! Thanks for the feedback!
Stefi

„Hardeep_kanwar†ezt írta:
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top