duplicate the each row twice in one worksheet

  • Thread starter Thread starter L.J.
  • Start date Start date
L

L.J.

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
 
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 :-)
------------------------------­------------------------------­----------------
 
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
 
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),)
Click to expand...

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.
 
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.....................
 
Back
Top