Dynamically Add rows based on formula

tpexcelst

New Member
Joined
Dec 25, 2020
Messages
3
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
  3. Mobile
Hello,
I am trying to figure out how to add a column, with a variable number of rows, based on the outcome of a formula.

Im able to get from this data:
1608920404295.png

To this:
1608920436665.png


But the step I need help with is getting the information from the second table into the final table which looks like this:

1608920457842.png


I would need to add a certain number of rows depending on how many names are added to Column B. So for Class1 I would need to add 5 rows, Class2 2 rows, Class6 2 rows, etc.

What formula would I use in B2, B3, B4 to pull in the correct names and automatically add each name to a new row so that the final table looks like this?

1608920686638.png


Thank you!
 

Attachments

  • 1608920268340.png
    1608920268340.png
    13 KB · Views: 2
  • 1608920296546.png
    1608920296546.png
    8.6 KB · Views: 2
  • 1608920336247.png
    1608920336247.png
    6.4 KB · Views: 2

Some videos you may like

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
6,153
Office Version
  1. 365
Platform
  1. Windows
We cannot work with pictures, so using XL2BB, please upload your sample data and sample solution so that we can manipulate the data without having to recreate your worksheet. This appears to be a Power Query join of tables solution but will need your data to test.
 

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,614
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Welcome to MrExcel Message Board.
If your first image data is in Sheet2 Columns A:B
AND you want your Result Table Sheet3 Columns A:D
Try this:
VBA Code:
Sub AddRowsBasedClass()
Dim i As Long
Dim j As Long
Dim n As Long
Dim Lr As Long
Dim Myrange As Range
Dim Myrange2 As Range
Dim Cell As Range
Lr = Sheets("Sheet2").Cells(Rows.Count, 2).End(xlUp).Row
Set Myrange = Sheets("Sheet2").Range("A1:B" & Lr)
Set Myrange2 = Sheets("Sheet3").Range("A1:D" & Lr)
Myrange.Sort key1:=Sheets("Sheet2").Columns("A"), Order1:=xlAscending, Key2:=Sheets("Sheet2").Columns("B"), Order2:=xlAscending, Header:=xlYes
Myrange2.Sort key1:=Sheets("Sheet3").Columns("A"), Order1:=xlAscending, Key2:=Sheets("Sheet3").Columns("B"), Order2:=xlAscending, Header:=xlYes


For i = 2 To Lr
For Each Cell In Sheets("Sheet2").Range("A2:A" & Lr)
If Sheets("Sheet3").Range("A" & i).Value = Cell.Value And Sheets("Sheet3").Range("A" & i).Value <> "" And Cell.Value <> Cell.Offset(-1, 0).Value Then
n = Application.WorksheetFunction.CountIf(Myrange, Cell)
If n <> 1 Then
Sheets("sheet3").Rows(i + 1).Resize(n - 1).Insert
End If
End If
Sheets("Sheet3").Range("B" & i).Value = Sheets("Sheet2").Range("B" & i).Value
Next Cell
Next i
End Sub
 
Last edited:

tpexcelst

New Member
Joined
Dec 25, 2020
Messages
3
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
  3. Mobile
We cannot work with pictures, so using XL2BB, please upload your sample data and sample solution so that we can manipulate the data without having to recreate your worksheet. This appears to be a Power Query join of tables solution but will need your data to test.
Hi @alansidman,
I downloaded xl2bb and have put my data here

Table 1
mrexcelquestion.xlsx
DE
1ClassName
2Class1David
3Alice
4Tom
5Franklin
6Rosy
7Class2James
8Peter
9Class 6William
10Sara
11Class3Cheryl
12Bard
13Nicol
Sheet1


Table 2
mrexcelquestion.xlsx
ABCD
1ClassNameLocationSemester
2Class1West HallSummer
3Class2North HallFall
4Class6AuditoriumFall
Sheet2


Table 3
mrexcelquestion.xlsx
ABCD
1ClassNameLocationSemester
2Class1DavidWest HallSummer
3Alice
4Tom
5Franklin
6Rosy
7Class2JamesNorth HallFall
8Peter
9Class6WilliamAuditoriumFall
10Sara
Sheet3


Id like to take the information in table 1, and combine it with table 2, so that table 2 looks like table 3 as the final output.
 

tpexcelst

New Member
Joined
Dec 25, 2020
Messages
3
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
  3. Mobile

ADVERTISEMENT

Welcome to MrExcel Message Board.
If your first image data is in Sheet2 Columns A:B
AND you want your Result Table Sheet3 Columns A:D
Try this:
VBA Code:
Sub AddRowsBasedClass()
Dim i As Long
Dim j As Long
Dim n As Long
Dim Lr As Long
Dim Myrange As Range
Dim Myrange2 As Range
Dim Cell As Range
Lr = Sheets("Sheet2").Cells(Rows.Count, 2).End(xlUp).Row
Set Myrange = Sheets("Sheet2").Range("A1:B" & Lr)
Set Myrange2 = Sheets("Sheet3").Range("A1:D" & Lr)
Myrange.Sort key1:=Sheets("Sheet2").Columns("A"), Order1:=xlAscending, Key2:=Sheets("Sheet2").Columns("B"), Order2:=xlAscending, Header:=xlYes
Myrange2.Sort key1:=Sheets("Sheet3").Columns("A"), Order1:=xlAscending, Key2:=Sheets("Sheet3").Columns("B"), Order2:=xlAscending, Header:=xlYes


For i = 2 To Lr
For Each Cell In Sheets("Sheet2").Range("A2:A" & Lr)
If Sheets("Sheet3").Range("A" & i).Value = Cell.Value And Sheets("Sheet3").Range("A" & i).Value <> "" And Cell.Value <> Cell.Offset(-1, 0).Value Then
n = Application.WorksheetFunction.CountIf(Myrange, Cell)
If n <> 1 Then
Sheets("sheet3").Rows(i + 1).Resize(n - 1).Insert
End If
End If
Sheets("Sheet3").Range("B" & i).Value = Sheets("Sheet2").Range("B" & i).Value
Next Cell
Next i
End Sub
hi, would I just put this code into each cell ?
 

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,614
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Are you don't familiar with VBA? Hold ALT key and Press F11 . Then go to Insert menu, Module and Paste Code at right window.
Then Hit Play Key (Green small right arrow) at the Above of window and run it. (or back to Excel Window, Save as file as .xlsm (Macro-enabled Workbook). Then Go to View Tab, Select Last item, Macros, then View Macros, Select AddRowsBasedClass and Run.
Now your table is ready.
 

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
6,153
Office Version
  1. 365
Platform
  1. Windows
With Power Query, bring each table into the Power Query Editor. PQ is called Get and Transform in 365 and is located on the Data Tab.

Once you have each table in Power Query, then join the two tables with a Left Outer Join. Here is the Mcode for that

Power Query:
let
    Source = Table.NestedJoin(Table1, {"Class"}, Table3, {"Class"}, "Table3", JoinKind.LeftOuter),
    #"Expanded Table3" = Table.ExpandTableColumn(Source, "Table3", {"Location", "Semester"}, {"Location", "Semester"})
in
    #"Expanded Table3"

and your end result looks like the following

Book2
ABCD
1ClassNameLocationSemester
2Class1DavidWest HallSummer
3Class1AliceWest HallSummer
4Class1TomWest HallSummer
5Class1FranklinWest HallSummer
6Class1RosyWest HallSummer
7Class2JamesNorth HallFall
8Class2PeterNorth HallFall
9Class6WilliamAuditoriumFall
10Class6SaraAuditoriumFall
11Class3Cheryl
12Class3Bard
13Class3Nicol
Merge1


Your Mcode for table 1 before merging is

Power Query:
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Filled Down" = Table.FillDown(Source,{"Class"})
in
    #"Filled Down"
 

Watch MrExcel Video

Forum statistics

Threads
1,127,387
Messages
5,624,402
Members
416,026
Latest member
melvic69

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top