Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
duplicate the each row twice in one worksheet
I have 89 rows data (first name, last name) in one worksheet, for some
reason I need to repeat their name 3 times for some reasons. Is there any easy way to do so wihtout running copy 3 times of 89 rows. Any tip would be much appreciated. Regards, MH |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
duplicate the each row twice in one worksheet
Select the whole lot, copy and then paste at the bottom of your data.
Repeat then sort on name. -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ------------------------------*------------------------------*---------------- It's easier to beg forgiveness than ask permission :-) ------------------------------*------------------------------*---------------- "L.J." wrote in message ... I have 89 rows data (first name, last name) in one worksheet, for some reason I need to repeat their name 3 times for some reasons. Is there any easy way to do so wihtout running copy 3 times of 89 rows. Any tip would be much appreciated. Regards, MH |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
duplicate the each row twice in one worksheet
Another play to try, which auto-triplicates lines
in exactly the same order as the source data .. Assume source data is in Sheet1, cols A and B, from row1 down In a new Sheet2, Put in A1: =OFFSET(Sheet1!$A$1,INT((ROWS($A$1:A1)-1)/3),) Put in B1: =OFFSET(Sheet1!$B$1,INT((ROWS($A$1:A1)-1)/3),) Select A1:B1, fill down by 3 times as many rows as there is data in Sheet1 (e.g.: since you have 89 lines in Sheet1, fill down to B267) Or, we could simply overfill to say, B300, to dynamically cater for new data additions in Sheet1 (It'll appear as zeros once the existing data in Sheet1 is exhausted) If required, kill the formulas in cols A & B with an in-place: Copy Paste special check "Values" OK -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- "L.J." wrote in message ... I have 89 rows data (first name, last name) in one worksheet, for some reason I need to repeat their name 3 times for some reasons. Is there any easy way to do so wihtout running copy 3 times of 89 rows. Any tip would be much appreciated. Regards, MH |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
duplicate the each row twice in one worksheet
In a new Sheet2,
Put in A1: =OFFSET(Sheet1!$A$1,INT((ROWS($A$1:A1)-1)/3),) Put in B1: =OFFSET(Sheet1!$B$1,INT((ROWS($A$1:A1)-1)/3),) The above "starting cell" formulas can be placed in any 2 starting cells (next to each other) in the new sheet, not necessarily in A1 & B1, and filled down from there. Adjust the number "3" within the part: .. INT((ROWS($A$1:A1)-1)/3) to suit as required. Eg: If we want each line to repeat 4 times, change the "3" to "4", then just fill down to cover at least 4 x the number of lines in the source data. And so on. -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- |
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
duplicate the each row twice in one worksheet
And if you wanted it all to stay in the same order, then simply add another
column to your data, fill down with 1,2,3,4,5 etc Copy all, paste under and then repeat and then sort on the number column. You can then delete it if you want. Regards Ken..................... "Ken Wright" wrote in message ... Select the whole lot, copy and then paste at the bottom of your data. Repeat then sort on name. -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ------------------------------*------------------------------*---------------- It's easier to beg forgiveness than ask permission :-) ------------------------------*------------------------------*---------------- "L.J." wrote in message ... I have 89 rows data (first name, last name) in one worksheet, for some reason I need to repeat their name 3 times for some reasons. Is there any easy way to do so wihtout running copy 3 times of 89 rows. Any tip would be much appreciated. Regards, MH |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
advanced: synchronizing data value across two worksheet drop boxes | Excel Worksheet Functions | |||
Search/Match between 2 x separate Worksheets and populate result in third worksheet | Excel Discussion (Misc queries) | |||
Copy from worksheet to another x times | Excel Discussion (Misc queries) | |||
Weekly Transaction Processing | Excel Worksheet Functions | |||
copyright and worksheet protection | Excel Discussion (Misc queries) |