How do you create table that automatically add or delete rows?

[Mailutoronto]

Suppose I have the tools list and its changes tabulated below:

Then in the bottom table I recorded a change of adding 2 bolts. How do you make the tool table automatically add a row in the top table and appear like this:

In reverse, if I used up 5 nails, how do you make the tool table automatically delete a row in the top table and appear like this:

The table that record changes should be the one that increases forever, while the top table will expand or contract depending on the availability of tools. 

 

 

 

[#2]

@Mailutoronto Why not build one table with beginning quantities and all subsequent changes and then summarize that one with a pivot table? Example attached.

[#3 · Accepted solution]

@Mailutoronto 

One way is to use VBA in order to achieve this functionality.

In the attached, you will find two tables named "Main" and "Change" where table "Main' contains some records. And once you start adding records in the Change Table, the Main Table will update accordingly.

The Sheet1 Module Contains the following Change_Event Code in it.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.CountLarge > 1 Then Exit Sub

Dim tblMain     As ListObject
Dim tblChange   As ListObject
Dim n           As Variant
Dim r           As ListRow
Set tblMain = ActiveSheet.ListObjects("Main")
Set tblChange = ActiveSheet.ListObjects("Change")
On Error GoTo Skip

If Not Intersect(Target, tblChange.DataBodyRange.Cells) Is Nothing Then
    Application.EnableEvents = False
    If Target.Column = tblChange.DataBodyRange.Columns(1).Column Then
    
        n = Application.Match(Target.Value, tblMain.DataBodyRange.Columns(1), 0)
        If IsError(n) Then
            If tblMain.DataBodyRange.Cells(1) = "" Then
                Set r = tblMain.ListRows.Add(1)
            Else
                Set r = tblMain.ListRows.Add
            End If
            r.Range(1, 1).Value = Target
        End If
        Target.Offset(0, 2).Value = Date
        Target.Offset(0, 2).NumberFormat = "dd-mmm-yyyy"
    ElseIf Target.Column = tblChange.DataBodyRange.Columns(2).Column Then
        n = Application.Match(Target.Offset(0, -1).Value, tblMain.DataBodyRange.Columns(1), 0)
        If Not IsError(n) Then
            Set r = tblMain.ListRows(n)
            r.Range(1, 2).Value = r.Range(1, 2).Value + Target.Value
            If r.Range(1, 2).Value = 0 Then
                tblMain.ListRows(n).Delete
            End If
        End If
        Target.Offset(0, 1).Value = Date
        Target.Offset(0, 1).NumberFormat = "dd-mmm-yyyy"
    End If
End If
Skip:
Application.EnableEvents = True
End Sub

[#4]

@Mailutoronto 

Excel 365 can achieve similar goals with worksheet formulas.

= LET(
  tool,     UNIQUE(Change[Tools]),
  stock,    SUMIFS(Change[Qty], Change[Tools], tool),
  stockQty, CHOOSE({1,2}, tool, stock),
  FILTER(stockQty, stock>0))

[#5]

Pivot table does help with customizing a summary of the data. However, the reason I specifically wanted to achieve this the way I wanted is because its a small component of a much larger project that I actually want to do: stock portfolio.
The entries will be numerals but less detailed, while the summary will be really detailed but few and change according to the entries. The pivot table, as far as I know, won't be as useful.

[#6]

This includes the function that I imagined. However, it does include flows. No need to fix because I think I'll have to learn VBA now and I wasn't exactly expecting this since almost all of my previous sheets were well handled by Excel without VBA/Macros.

The rows auto add and auto deleted based on variables that is quite narrow and leads to some malfunctions. I'll have to include more rules myself.

Can you point me to certain topics in VBA to learn what you did? or do I have to learn everything from scratch?

[#7]

thanks for your response. I don't know what it is you did here. Are the codes in VBA/Macro? If so, I think I"ll have to learn it myself since I never used it in Excel before.

Are you able to point me to some sources for learning what it is you did and maybe the topics that can quickly guide me. Or should I learn whatever it is from scratch? Thank you,

[#8]

@Mailutoronto 

It is just a regular Excel formula.  OK, not so regular since most users will not recognise the formula as Excel!  The following announces that LET is now generally available on Excel 365.

LET Generally Available (microsoft.com)

Searches of the Microsoft site should give links to the introduction of Dynamic Arrays (2018), the Announcement of LET and, still on insider beta channels the announcement of Lambda functions and Lambda helper functions that feed array parameters into Lambda functions and collect the results to form an array of results.

It all represents the most dramatic change imaginable.

[#9]

I have gathered some answers to my question above, but was your Tools table adding rows and deleting rows depending on the changes in the tools?

[#10]

@Mailutoronto 

Sadly some of my naming was not helpful.  In the following

FILTER(stockQty, stock>0)

the first term was a two column array of tool name and its total quantity.  The second was the stock quantity.  What FILTER does is retain the rows that satisfy the condition

stock > 0

so giving a fully dynamic result that changes as the data is revised.  I used LET simply because that is the way I program Excel.  In this instance it would be pretty easy to do without by nesting the variable definitions within the final formula.

= FILTER(
    CHOOSE(
      {1,2}, 
      UNIQUE(Change[Tools]), 
      SUMIFS(Change[Qty], Change[Tools], tool)), 
    SUMIFS(Change[Qty], Change[Tools], tool)>0)
  )

[#11]

Where are you entering these codes? They look very much like VBA (I'm in the middle of learning).
2nd, although I recognize those formula, but I have no idea how it is supposed to be implemented on the sheet.

I'm not that great with Excel so I can't comment on whether if this works the way I wanted it to. The commentor above, however, did achieve what I wanted it to do with bugs.

Still, thanks for looking into this. I'll have to revisit this sometime later. I'm still in the middle of learning VBA.

[#12]

Without VBA you can use sequence and if with some logical cheat, see my post on aug 23 2021

[#13]

@Mailutoronto 

You are not alone.  It happens frequently that the Excel 365 formulas I publish are not even recognised as spreadsheet formulas!  I have tidied up the naming a little to make the quantities a little clearer.

= LET(
  tool,     UNIQUE(Change[Tool]),
  stockQty, SUMIFS(Change[Qty], Change[Tool], tool),
  stockTbl, CHOOSE({1,2}, tool, stockQty),
  FILTER(stockTbl, stockQty>0))

The formula that generates the table is in cell F3.   A fairly normal trick for debugging a LET formula, is to return one of the local names at a time as the result of the calculation

= LET(
  tool,     UNIQUE(Change[Tool]),
  stockQty, SUMIFS(Change[Qty], Change[Tool], tool),
  stockTbl, CHOOSE({1,2}, tool, stockQty),
  result,   FILTER(stockTbl, stockQty>0),
  stockTbl)

By inspecting each name in turn, one is able to gain insight and debug the formula.  So far, this only applies to Excel 365, but it represents the most profound change to occur in spreadsheet technology since they were first introduced in the 1980s.

This may represent the emergent butterfly, but many will continue to admire and play only with the caterpillars!