Insert duplicate rows based on numeric value in column

[Nu2Excel]

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

[Dave Peterson]

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

[Nu2Excel]

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

[blue_shoes]

Hi - Need some help! This works for me except I have more than 2 columns in my row. I need the whole row to duplicate, not just 1 column.

For instance:

Current:
CA1 Green 2
CA2 Red 3

Desired:
CA1 Green 2
CA1 Green 2
CA2 Red 3
CA2 Red 3
CA2 Red 3

With the code in the thread, I'm getting
CA1 Green 2
CA1 2
CA2 Red 3
CA2 3
CA2 3

How can I successfully modify to duplicate the whole row? I'm getting errors.

Thank you!!

[FDibbins]

Hi and welcome to the forum, blue_shoes

Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.