Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Hiding rows based on choice in a listbox | Excel Discussion (Misc queries) | |||
How do I find duplicate rows in a list in Excel, and not delete it | Excel Discussion (Misc queries) | |||
Protect Worksheet but allow to insert or delete rows | Excel Discussion (Misc queries) | |||
Get External Data - Insert Rows | Excel Discussion (Misc queries) | |||
How do I delete duplicate rows in an excel spreadsheet? | Excel Discussion (Misc queries) |