Excel - Populate data according to variable

December 2016






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.

Related :

This document entitled « Excel - Populate data according to variable » from CCM (ccm.net) is made available under the Creative Commons license. You can copy, modify copies of this page, under the conditions stipulated by the license, as this note appears clearly.