Excel - Populate data according to variable

Ask a question





Issue


I want to populate an Excel sheet with a variable number of rows according with a number inserted on a fixed column.


column A B C D  
r1 2009096 1001 19 5  
r2 2009097 1002 12 2  
r3 2009097 1003 36 6  
r4 2009099 1004 10 3  
r5 2009099 1005 11 3  
r6 2009099 1006 12 2  
r7 2009099 1007 13 4  
r8 2009101 1008 25 5  
r9 2009102 1009 19 5 



According with the numbers inserted in column D, i intend to obtain the following result:

column A B C  
r1 2009096 1001 19  
r2 2009096 1001 19  
r3 2009096 1001 19  
r4 2009096 1001 19  
r5 2009096 1001 19  
r6 2009097 1002 12  
r7 2009097 1002 12  
r8 2009097 1003 36  
r9 2009097 1003 36  
r10 2009097 1003 36  
r11 2009097 1003 36  
r12 2009097 1003 36  
r13 2009097 1003 36 

Solution


Assuming that data is in sheet1, starting at row 1 (no header).
Result will be displayed in sheet2.

Correct sheet names and make backup of file first, then see if this macro yield the desired result:
Sub Test() 
Dim lRow, lRow2, x, y As Integer 
lRow = Sheets("sheet1").Range("E" & Rows.Count).End(xlUp).Row 
For Each cell In Sheets("Sheet1").Range("E1:E" & lRow) 
x = cell.Value 
y = 0 
    Do 
Sheets("Sheet1").Select 
Range(Cells(cell.Row, "A"), Cells(cell.Row, "D")).Copy 
lRow2 = Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Row 
Sheets("Sheet2").Range("A" & lRow2).PasteSpecial 
y = y + 1 
    Loop Until x = y 
Next cell 
End Sub


Thanks to TrowaD for this tip.
Jean-François Pillou

CCM is a leading international tech website. Our content is written in collaboration with IT experts, under the direction of Jeff Pillou, founder of CCM.net. CCM reaches more than 50 million unique visitors per month and is available in 11 languages.

Learn more about the CCM team