How to insert duplicate rows

[btamulis]

Interesting scenario -

I have a spreadsheet with one column - 35,000 unique item numbers

Example:

item a
item b
item c
item d
item e

I need to duplicate each row - making a spreadshhet with one column 70,000 rows as such:

item a
item a
item b
item b
item c
item c
item d
item d
item e
item e

I tried a formula - couldn't come up with anything - looked for some kind of merge type functionality - did find anything........

Any guidance would be appreciated........

[ChemistB]

Does it need to be in the same order?
You can copy your range (i.e. A2:A1000) and paste it below the original and then Sort by that column to get the duplicate values back together again. Would that work for you?

[HenryCrun]

Here's a simple VBA solution

Please Login or Register  to view this content.

[btamulis]

I tried your solution and ran into the following: Copied my rows (38,365 rows)

I tried to paste my selected at the 1st blank row after my last record - get that ugly message that I cannot paste and that I must select a single cell (which I did) or I must choose a range equivalent to my selected range.........

My range is 38,365 rows - so I try to select a range below my range with 38,375 rows (76,730 total) - the spreadsheet maxes at 65,000 - I am on 2007 - how to change the default of 65,000 rows available?

[ChemistB]

how to change the default of 65,000 rows available

Save the file as an xlsx (or xlsm) and then close and reopen the file. Did that fix the problem?