Copy Every 5th Row
 

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

Copy Every 5th Row
 

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!
EditGotoAdvancedCurrentRegion
EditGotoAdvancedVisible cells only

Copy and paste!


Regards,
Stefi

€žHardeep_kanwar€� ezt Ã*rta:

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

Copy Every 5th Row
 

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


"Stefi" wrote:

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!
EditGotoAdvancedCurrentRegion
EditGotoAdvancedVisible cells only

Copy and paste!


Regards,
Stefi

€žHardeep_kanwar€� ezt Ã*rta:

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

Copy Every 5th Row
 

Thanks Stefi

It Works like a treat

Hardeep kanwar

"Stefi" wrote:

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!
EditGotoAdvancedCurrentRegion
EditGotoAdvancedVisible cells only

Copy and paste!


Regards,
Stefi

€žHardeep_kanwar€� ezt Ã*rta:

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

Copy Every 5th Row
 

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" wrote:

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


"Stefi" wrote:

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!
EditGotoAdvancedCurrentRegion
EditGotoAdvancedVisible cells only

Copy and paste!


Regards,
Stefi

€žHardeep_kanwar€� ezt Ã*rta:

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

Copy Every 5th Row
 

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


"Hardeep_kanwar" wrote:

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" wrote:

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


"Stefi" wrote:

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!
EditGotoAdvancedCurrentRegion
EditGotoAdvancedVisible cells only

Copy and paste!


Regards,
Stefi

€žHardeep_kanwar€� ezt Ã*rta:

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

Copy Every 5th Row
 

You are welcome! Thanks for the feedback!
Stefi

€žHardeep_kanwar€� ezt Ã*rta:

Thanks Stefi

It Works like a treat

Hardeep kanwar

"Stefi" wrote:

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!
EditGotoAdvancedCurrentRegion
EditGotoAdvancedVisible cells only

Copy and paste!


Regards,
Stefi

€žHardeep_kanwar€� ezt Ã*rta:

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