I have a workbook with four source data columns, Column A "Company", B "CC", C "EXP" and D "EXP Description. The expected output will be in sister columns of the same titles from F to I.
What I want to achieve is for each value in G to be copied "X" number of times based on the range of C & D and also populate Column H I with the copied EXP and EXP Description. The expected copied number for output column Company (Column F) will be count of Column C x count of column B
Apologies for my wording, its quite hard to explain. I have attached a spreadsheet highlighting in yellow the first two results I would expect.
Last edited by DevfromCorrie; 11-21-2019 at 11:05 AM.
According to your attachment a starter demonstration :
PHP Code:
Sub Demo1() Dim L&, Rg(2) As Range, R& L = 2 Set Rg(2) = Range("C2", [D1].End(xlDown)) R = Rg(2).Rows.Count [F1].CurrentRegion.Offset(1).Clear Application.ScreenUpdating = False For Each Rg(0) In Range("B2", [B1].End(xlDown)) Rg(0).Copy Cells(L, 7).Resize(R) L = L + R Next Set Rg(1) = Range("G2:G" & L - 1) R = 2 For Each Rg(0) In Range("A2", [A1].End(xlDown)) Rg(0).Copy Cells(R, 6).Resize(Rg(1).Count) R = R + Rg(1).Count Next Rg(1).Copy Range(Cells(L, 7), Cells(R - 1, 7)) Rg(2).Copy Range("H2:I" & R - 1) Application.ScreenUpdating = True Erase Rg End Sub
Do you like it ? So thanks to click on bottom left star icon « ★ Add Reputation » !
Last edited by Marc L; 11-21-2019 at 08:30 AM.
Reason: optimization …
Bookmarks