Adding new data to excel table from another range based on a cell content (Macros)

[drawings2]

Very new to Macros!

I need to add new lines in my table automatically with data from another table.
Example Table 1:
PB&J
Apple Pie
Banana cake
Bread

Second sheet data:
PB&J Apple Pie
Peanut Butter Apple
jelly Sugar
Bread Flour


Every time i run the code I want table 1 to change to

PB&J
Peanut Butter
jelly
Bread
Apple Pie
Apple
Sugar
Flour
Banana cake
Bread




Can anyone please help!

 

[OaklandJim]

I can't promise that I help but I'll try. If not me then possibly other people who are smarter or more perceptive.

The logic to get from the two lists to the result is not totally obvious. For example, you list Peanut Butter Apple as content of the Table 2 but the results include Peanut Butter and Apple separately.

If the data is not confidential post a link to your workbook. If necessary you can enter fake-but-realistic data before providing the link. Put the file on Dropbox, Box, 1Drive, Google Drive etc. Use the link icon above the Mr Excel message area. Make sure that other people can access the file!

Or, consider sharing relevant data using Mr Excel's excellent XL2BB addin that enables you to post a portion of a worksheet. See XL2BB - Excel Range to BBCode for details.

 

[drawings2]

Hi,


Please see if this helps? I cannot share the excel sheet unfortunately.


Thanks

 

[OaklandJim]

So there are three tables? 1. Food Items, 2. Food Items' Contents, 3 Summary Table.

 

[drawings2]

So there are three tables? 1. Food Items, 2. Food Items' Contents, 3 Summary Table.

No the third one is the first table again. The third one shows what I want the first table to change to.

So basically, every time PB&J is encountered or Apple Pie is encountered, the first table needs to add extra rows below the encountered word and print the contents. Does that make sense?

 

[OaklandJim]

If I understand what is needed I'll try for a solution. But I am confused about what is supposed to happen.

The way you laid out the tables it sure seems like there are two "input tables" -- 1. Food Items and 2. "Food Contents." -- and a "Results" table that includes each Food Item with contents also listed below each Food Item entry?

From what I can understand it seems like you want the Results table to be updated as new items are entered into the Food Items Table. And, the Results table is updated to include the newly added Food Item and its contents (if there are contents for the Food Item listed in the Food Contents table).

What is wrong with my understanding?

 

[drawings2]

not a results table, but the original food items table to get updated with new lines containing the contents.
Not to create a third results table.
So we need the code to identify the number of contents within each food item encountered in the first column and add the required extra lines underneath then copy the contents to the created new lines.

 

[drawings2]

"From what I can understand it seems like you want the Results table to be updated as new items are entered into the Food Items Table. And, the Results table is updated to include the newly added Food Item and its contents (if there are contents for the Food Item listed in the Food Contents table)." ------ this is also fine

 

[OaklandJim]

This workbook may not be exactly what you want but I can probably adjust it if needed. FYI it uses the Worksheet_Activate event handler for the Food Items worksheet. I also used "Code Names" for the worksheets so if you rename the worksheet "tabs" the code will still work.

Option Explicit

Private Sub Worksheet_Activate()
    Call ProcessFoodItems
End Sub

Option Explicit
Option Base 1

' ----------------------------------------------------------------
' Procedure Name: ProcessFoodItems
' Purpose: Transfer data from Food Items worksheet to Contents worksheet.
' Procedure Kind: Sub
' Procedure Access: Public
' Author: Jim
' Date: 9/27/2023
' ----------------------------------------------------------------

Sub ProcessFoodItems()

'   Header cell in results range in FoodItems worksheet.
    Dim rResultsAnchorCell As Range
    
'   Table object for the Food Items table.
    Dim oFoodsContentTable As ListObject
    
'   Table row item object for Food Items table.
    Dim oFoodContentRow As ListRow
    
'   Count of rows in Food Items Table.
    Dim iFoodItemsCount As Long
    
'   Used to iterate through Food Items.
    Dim iFoodItem As Long
    
'   Count of EXISTING rows in Results range (i.e., before clearing the values).
    Dim iResultsRowsCount As Long
    
'   Use to keep count f results rows processed -- Food Items and Contents.
    Dim iResultsRow As Long
    
'   Count of Content Items for the Food Item being procesed.
    Dim iContentsCount As Long
    
'   Used to iterate through each Food Items' Content items.
    Dim iContentItem As Long

'   Array holding Food Item types (names).
    Dim asFoodItems() As String
    
'   String array holding contents for one Food Item.
    Dim asContentsList() As String
    
    Set rResultsAnchorCell = [FoodItems].Range("B2") '<= change to relocate the Contents table.
    
    iResultsRowsCount = rResultsAnchorCell.Offset(10000).End(xlUp).Row - rResultsAnchorCell.Row
        
    On Error Resume Next
    rResultsAnchorCell.Offset(1).Resize(iResultsRowsCount).Value = ""
    On Error GoTo 0
    
'   Set table object.
    Set oFoodsContentTable = [Contents].ListObjects("FoodItems")
    
'   Load array asFoodItems with Food Items
    Call GetFoodItemsList(oFoodsContentTable, asFoodItems)
    
'   Get count of Food Items (no Contents).
    iFoodItemsCount = UBound(asFoodItems)
    
    For iFoodItem = 1 To iFoodItemsCount
    
        iResultsRow = iResultsRow + 1
        
        rResultsAnchorCell.Offset(iResultsRow).Value = oFoodsContentTable.DataBodyRange(iFoodItem, 1).Value
        
'       Get Content items for the respective Food Item by loading arrayasContentsList.
        Call GetFoodItemContentsList(iFoodItem, oFoodsContentTable, asContentsList)

'       Get count of Content items for the respective Food Item
        iContentsCount = UBound(asContentsList)
'
        For iContentItem = 1 To iContentsCount

            iResultsRow = iResultsRow + 1

            rResultsAnchorCell.Offset(iResultsRow).Value = asContentsList(iContentItem)

        Next iContentItem
        
    Next iFoodItem
    
End Sub

Option Explicit
Option Base 1

' ----------------------------------------------------------------
' Procedure Name: GetFoodItemsList
' Purpose: Load a list of all Food Items, as text, into an array.
' Procedure Kind: Function
' Procedure Access: Public
' Parameter poFoodItemsTable (ListObject): Data table where the Food Items are listed.
' Parameter pasList (String): String array to be filled is passed by caller as a ByRef parameter.
' Return Type: String)
' Author: Jim
' Date: 9/27/2023
' ----------------------------------------------------------------

Function GetFoodItemsList(ByVal poFoodItemsTable As ListObject, ByRef pasList() As String)

    Dim oRow As ListRow
    
'   Used to iterate through rows in poFoodItemsTable.
    Dim iRow As Long

    ReDim pasList(1)
    
    For Each oRow In poFoodItemsTable.ListRows
    
        iRow = iRow + 1
    
        ReDim Preserve pasList(iRow)
        
        pasList(iRow) = poFoodItemsTable.DataBodyRange(iRow, 1).Value
    
    Next oRow
    
End Function

Option Explicit
Option Base 1

' ----------------------------------------------------------------
' Procedure Name: GetFoodItemContentsList
' Purpose: Load a list of Contents for a specific Food Item, as text, into an array.
' Procedure Kind: Function
' Procedure Access: Public
' Parameter piTableRow (Long): Data table row where the Food Item's Contents are listed.
' Parameter poFoodItemsTable (ListObject): Data table where the Food Items are listed.
' Parameter pasList (String): String array to be filled is passed by caller as a ByRef parameter.
' Return Type: String)
' Author: Jim
' Date: 9/27/2023
' ----------------------------------------------------------------

Function GetFoodItemContentsList( _
    ByVal piTableRow As Long, _
    ByVal poFoodItemsTable As ListObject, _
    ByRef pasContentsList() As String)

    ReDim pasContentsList(1)
    
    Dim iColumnsCount As Long
    
    Dim iColumn As Long
    
    Dim sCellContent As String
    
'   Get count of columns in the table, less one to account for the first column
'   containing the table's "row headers" (i.e., Food Item types/names).
    iColumnsCount = poFoodItemsTable.ListColumns.Count - 1

'   Iterate through data table columns.
    For iColumn = 1 To iColumnsCount
    
'       Get the string value for the name of the individual content item.
        sCellContent = poFoodItemsTable.DataBodyRange(piTableRow, iColumn + 1)
        
'       If sCellContent is not empty then put it into the array.
        If sCellContent <> "" Then _
            
            ReDim Preserve pasContentsList(iColumn)
            
            pasContentsList(iColumn) = sCellContent
            
        Else
'           If sCellContent = "" Then done, return to the caller.
            Exit Function
        End If
    
    Next iColumn

End Function