Copy Every 5th Row

[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

 

[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:

 

[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

 

[Hardeep_kanwar]

Thanks Stefi

It Works like a treat

Hardeep kanwar

 

[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

 

[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

 

[Stefi]

You are welcome! Thanks for the feedback!
Stefi

„Hardeep_kanwar†ezt írta: