Dynamic Row copy (Create 1-4 copies of row based on count of items in a table)

[nguytravis]

Hello all,
Looking to make a dynamic tool which can take a static list and copy it 1-4 times (also inserting data from a small table)

I have a list of 30 student names in column A.

In columns C:E I have a small table with the following data:
C: Class name
D: Instructor
E: Credits
[TABLE="width: 500"]
<tbody>[TR]
[TD]Class name[/TD]
[TD]Instructor[/TD]
[TD]Credits[/TD]
[/TR]
[TR]
[TD]Baking[/TD]
[TD]smith[/TD]
[TD]22[/TD]
[/TR]
[TR]
[TD]HVAC[/TD]
[TD]Jones[/TD]
[TD]15[/TD]
[/TR]
[TR]
[TD]Accounting[/TD]
[TD]Roberts[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]Dental[/TD]
[TD]Ord[/TD]
[TD]8

[/TD]
[/TR]
</tbody>[/TABLE]

1) Assume the sample table above is reflective of our table in columns C:E. I would want 120 unique lines generated because there are 4 classes. 4 classes * 30 students = 120
The names from column A would be copied into column H(or some random column to the right of our data) 4 times, since there are four classes. Additionally, I would want the data from the table in C:E to be reflected in each. So each name shows up 4 times, but each line is reflecting a unique class name, instructor and credit.

2) If the table above only had 3 classes entered (assume Dental is removed), I would want 90 unique lines generated. (Each person from column A would show up 3 times. One line would be Baking, one would be HVAC and one would be Accounting.

3)The table above only has 2 classes entered, I would want 60 unique lines.
4)The table above only has 1 class, I still want the 30 lines to generate (adding in the class name, instructor and credits).

I am a decent user of excel. This tool is aimed at folks who are very weak at computing in general, so the fewer steps,the better.
I can have them click a macro, but not sure I trust folks to do much more than that .

Thanks in advance!

 

[MickG]

Try this:=
NB:- The number of repeating sets of data is related to the number of students in column "A"
NB:- Student names copied to column "H".

[COLOR="Navy"]Sub[/COLOR] MG17Nov15
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Rng2 [COLOR="Navy"]As[/COLOR] Range, Rws [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))
[COLOR="Navy"]Set[/COLOR] Rng2 = Range(Range("C2"), Range("C2").End(xlDown)).Resize(, 3)
Application.ScreenUpdating = False
c = 2
Rws = Rng2.Rows.Count
    [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
        Rng2.Copy Cells(c, "C")
        Cells(c, "C").Resize(Rws).Offset(, 5) = Dn.Value
        c = c + Rws + 1
    [COLOR="Navy"]Next[/COLOR] Dn
Application.ScreenUpdating = True
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

To Save and Run Code:-
Copy code from Thread
In Your Data sheet , Click "Alt+F11",:- Vb Window appears.
From the VBWindow toolbar, Click "Insert" ,"Module":- New VBwindow appears .
Paste Code into this window.
Close Vbwindow.

On sheet Click "Developer tab", Click "Macro". Macro dialog box appears.
Select Macro (with same name) from List.
On the right of Dialog box Click "Run"
The Sheet should now be updated.

Regards Mick

 

[nguytravis]

Thanks so much for the code, Mick.
This outputs all of the data points I was looking for.

The format is not quite as I intended. I'm trying to adjust your macro from here but don't understand parts of it well enough to adjust.
I intended for the outputs to begin in column H, rather than overwriting column C:E. I want to keep that table in columns C:E as is.
So column H would be student, column I would be class, Column J would be instructor and column K would be credit hours.
"Sub MG17Nov15Dim Rng As Range, Dn As Range, c As Long, Rng2 As Range, Rws As Long
Set Rng = Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))
Set Rng2 = Range(Range("C2"), Range("C2").End(xlDown)).Resize(, 3)
Application.ScreenUpdating = False
c = 2
Rws = Rng2.Rows.Count
For Each Dn In Rng
Rng2.Copy Cells(c, "C")
Cells(c, "C").Resize(Rws).Offset(, 5) = Dn.Value
c = c + Rws + 1
Next Dn
Application.ScreenUpdating = True
End Sub"
~~ So is the "Cells(c, "C").Resize(Rws).Offset(, 5) = Dn.Value" The portion of code which outputs the student name to column H?


I intended to keep the original table in columns C:E and have all of the new data appear in column H.
So this macro does output the exact data I asked for, but I am going to try reformatting it.

 

[MickG]

Try this for results in columns "H to K".

[COLOR="Navy"]Sub[/COLOR] MG18Nov56
[COLOR="Navy"]Dim[/COLOR] rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Rng2 [COLOR="Navy"]As[/COLOR] Range, Rws [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Set[/COLOR] rng = Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))
[COLOR="Navy"]Set[/COLOR] Rng2 = Range(Range("C2"), Range("C2").End(xlDown)).Resize(, 3)
Application.ScreenUpdating = False
c = 2
Rws = Rng2.Rows.Count
    Range("H1:K1") = Array("Student", "Class Name", "Instructor", "Credits")
    [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] rng
        Rng2.Copy Cells(c, "I")
        Cells(c, "H").Resize(Rws) = Dn.Value
        c = c + Rws + 1
    [COLOR="Navy"]Next[/COLOR] Dn
Application.ScreenUpdating = True
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick