| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| move or copy sheets doesn't copy format | Excel Worksheet Functions | |||
| Copy and paste versus copy and insert copied cells | New Users to Excel | |||
| I copy a formula and the results copy from the original cell | Excel Discussion (Misc queries) | |||
| Copy/Paste how to avoid the copy of formula cells w/o calc values | Excel Discussion (Misc queries) | |||
| EXCEL FILE a copy/a copy/a copy ....filename | New Users to Excel | |||
![Hardeep_kanwar[_2_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
