Insert Rows Based On Cell Content and expand

Question

I've done next to no vba coding for excel but I am familiar with vba coding for access. Any and all help is very much appreciated!

The example used in this question is just a snipped of the amount of data is actually present, so please keep in mind the code will need to accommodate for up to 100+ rows.

I need to expand and fill the following table, tbl1:

with data from the following table, tbl2:

I would like the output to look like this, tbl3:

I have attempted to break this into parts, first I had used countif to determine now many times the suburb appears in tbl2 and then insert rows of that amount under the respective row, however I found that it doesn't actually insert the rows but more so appends data to existing rows. I wanted to insert rows in the hopes that column team can remain merged for that particular district. I cant seem to 'insert' rows, like you would when you right click, insert row.

Another problem im having is getting the additional data in tbl2 into the output tbl3. I know I cant use vlookup because there are multiple matches for each value, i'm not sure how to proceed.

EDIT tbl2 in reality will contain much more data than tbl1 requires, each row will have different combinations of values for example: Cat, Red, South but we are only interesting in the values that are in tbl1 to expand and fill tbl1.

Answer 1

Let's have a look at the Powerquery solution. I started with two tables (listobjects resp. intelligent tables in Excel) which I called tblAnimals and tblSuburbs.

I imported these tables into Power Query (Goto Data and From Table/Range) and saved them in PowerQuery as connection only. Then I joined these two queries (Data, combine queries) with Suburb as the column for the join.

The result is

And this is the M-Code to do it

let
    Source = Table.NestedJoin(tblAnimals, {"Suburb"}, tblSuburbs, {"Suburb"}, "tblSuburbs", JoinKind.LeftOuter),
    combineSuburbs = Table.ExpandTableColumn(Source, "tblSuburbs", {"District", "Team"}, {"District", "Team"}),
    reorderColumns = Table.ReorderColumns(combineSuburbs,{"Suburb", "Animal", "Colour", "District", "Team"})
in
    reorderColumns

Answer 2

I found a partial solution to the above.

This is a solution to expand tbl1 to include the data from tbl2.

For this to work, I needed to count how many times a suburb in tbl1 appeared in tbl2, i did this using =countif() then appended each count next to the column like below:

Then I used code found in this post Insert 'n' number of rows to get the following table:

I then used the following code:

Sub test()

Dim FindEnd As Range
 
Dim rng As Range
Dim rng2 As Range
Dim rng3 As Range
Dim text5 As String
 
Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim Text As String
Dim Text4 As String
Dim text2 As String
Dim text3 As String
Dim l As Integer
    
Dim LastRow As Long
Dim address1 As String
Dim address2 As String
 
i = 1
j = 1
k = 1
 
text2 = "start"
text3 = "start2"

Set rng = Sheets("Sheet3").Range("e1:e332")
 
For Each rng2 In rng
    text2 = rng2
    
    If text2 = text3 Then
        i = i + 1
    Else
        i = 1
    End If
    
    Set rng3 = Sheets("test").Range("E:E")
    Set FindEnd = rng3.Find(What:=rng2, LookIn:=xlValues, LookAt:=xlWhole)
    
    address1 = FindEnd.address
    text5 = "E" + CStr(rng2.Row)
    l = Application.WorksheetFunction.CountIf(Range("E1:" + text5), rng2)
    
    Do While j < l
        Set FindEnd = rng3.Find(What:=rng2, LookIn:=xlValues, LookAt:=xlWhole, After:=FindEnd)
        j = j + 1
    Loop
     
    j = 1
    LastRow = FindEnd.Row
    Text = "G" + CStr(LastRow)
    Text4 = "G" + CStr(rng2.Row)
            
    Sheets("Sheet3").Range(Text4).Value = Sheets("test").Range(Text).Value
    k = k + 1
    
    If k = 500 Then
        Exit For
    End If
    
    text3 = rng2

Next rng2
End Sub

To get a table like this:

This will need to be repeated/aletered slightly to get the 'Animal' column.

Now as advised by @storax in the comments above, the merged columns are probably going to be a big problem. So I unmerged them all and then used @siddharth solution to fill the district and team columns.

From here I will need to write some code to merge duplicates in column. Using @siddharth full solution will work for the purpose of this example, however as stated, the full dataset is far bigger and hence this was a more viable solution for that. Thank you to everyone for all your help. If there is still interest Ill post the solution for the merging too but I think there will be an answer for that somewhere on stackoverflow.

Thanks agains and much appreciated for all your input.

Answer 3

If I have understood you correctly then you do not need VBA for this.

Logic

1. Use VLOOKUP in 4th column of tbl2 and pull data from tbl1. That will give you the District. Use Location i.e the 3rd column as lookup text for VLOOKUP.

2. In 5th column of tbl2 again pull data from tbl1 using VLOOKUP to get your Team. Use District i.e the 4th column as lookup text for VLOOKUP.

Let's say your data looks like this

Now copy tbl2 and paste it as shown below and add 2 columns District and Team

In cell D19 put the formula =VLOOKUP(C19,$C$1:$E$7,2,0)

And in E19 put the formula =VLOOKUP(D19,$D$1:$E$7,2,0)

Simply drag the formula and we are done.