Insert duplicate rows based on numeric value in column
 

Is there a way to insert new duplicate rows in an excel worksheet based
on a value in a column? For example I have 2 columns as follows

Place Number
London 3
Paris 5
Lisbon 2
France 2

I want to achieve the following

Place Number
London 3
London 3
London 3
Paris 5
Paris 5
Paris 5
Paris 5
Paris 5
Lisbon 2
Lisbon 2...and so on

So I basically want the first column and its value repeated the number
of times specified by the associated value in the Number column. I hope
that makes sense. I hope someone can help as this is driving me nuts and
I am not too clever with Excel


--
Nu2Excel
------------------------------------------------------------------------
Nu2Excel's Profile: http://www.excelforum.com/member.php...o&userid=27523
View this thread: http://www.excelforum.com/showthread...hreadid=470432

How about a little macro.

Save your work first--it destroys the original data. If it doesn't work
correctly, you can just close and reopen and no harm done.

Make sure that there's numbers in column B (the code doesn't check!).

Option Explicit
Sub testme()

Dim wks As Worksheet
Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long
Dim HowManyMore As Long

Set wks = Worksheets("Sheet1")
With wks
FirstRow = 2 'headers in row 1???
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

For iRow = LastRow To FirstRow Step -1
HowManyMore = .Cells(iRow, "B").Value - 1
If HowManyMore 0 Then
.Rows(iRow + 1).Resize(HowManyMore).Insert
.Cells(iRow + 1, "A").Resize(HowManyMore, 1).Value _
= .Cells(iRow, "A").Value
.Cells(iRow + 1, "b").Resize(HowManyMore, 1).Value _
= .Cells(iRow, "b").Value
End If
Next iRow

End With
End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Nu2Excel wrote:

Is there a way to insert new duplicate rows in an excel worksheet based
on a value in a column? For example I have 2 columns as follows

Place Number
London 3
Paris 5
Lisbon 2
France 2

I want to achieve the following

Place Number
London 3
London 3
London 3
Paris 5
Paris 5
Paris 5
Paris 5
Paris 5
Lisbon 2
Lisbon 2...and so on

So I basically want the first column and its value repeated the number
of times specified by the associated value in the Number column. I hope
that makes sense. I hope someone can help as this is driving me nuts and
I am not too clever with Excel

--
Nu2Excel
------------------------------------------------------------------------
Nu2Excel's Profile: http://www.excelforum.com/member.php...o&userid=27523
View this thread: http://www.excelforum.com/showthread...hreadid=470432

--

Dave Peterson

Dave this is brilliant, it does exactly what I wanted - thanks.


--
Nu2Excel
------------------------------------------------------------------------
Nu2Excel's Profile: http://www.excelforum.com/member.php...o&userid=27523
View this thread: http://www.excelforum.com/showthread...hreadid=470432