VBA - generate a new table based on conditional values in an existing table

g00ber 

To generate a new table based on conditional values in an existing table using VBA, you can use the following steps:

1. Open your Excel workbook and press Alt + F11 to open the VBA editor.
2. In the VBA editor, click on Insert and then click on Module.
3. In the new module window, paste the following code:

Untested, needs to be adapted to your spreadsheets.

Sub GenerateTable()
    Dim ws As Worksheet
    Dim tbl As ListObject
    Dim rng As Range
    Dim dict As Object
    Dim i As Long
    
    Set ws = ThisWorkbook.Sheets("Sheet1")
    Set tbl = ws.ListObjects("Table1")
    Set rng = tbl.ListColumns("Month").DataBodyRange
    
    Set dict = CreateObject("Scripting.Dictionary")
    
    For i = 1 To rng.Rows.Count
        If tbl.DataBodyRange(i, 2) = "Red" Then
            If Not dict.Exists(rng(i)) Then
                dict.Add rng(i), New Collection
            End If
            
            dict(rng(i)).Add tbl.DataBodyRange(i, 1)
        End If
    Next i
    
    Set rng = ws.Range("A1").Resize(dict.Count + 1, 2)
    
    rng(1, 1) = "Month"
    rng(1, 2) = "Person(s)"
    
    i = 2
    
    For Each key In dict.Keys
        rng(i, 1) = key
        rng(i, 2) = Join(CollectionToArray(dict(key)), ", ")
        i = i + 1
    Next key
    
End Sub

Function CollectionToArray(col As Collection) As Variant()
    Dim arr() As Variant
    Dim i As Long
    
    ReDim arr(col.Count - 1)
    
    For i = 0 To col.Count - 1
        arr(i) = col.Item(i + 1)
    Next i
    
    CollectionToArray = arr
End Function

VBA code, sometime from the internet, no idea where long ago.

4. Replace "Sheet1" with your sheet name and "Table1" with your table name.

5. Save your workbook as a macro-enabled workbook

6. Press Alt + F8 to open the Macro dialog box

7. Select GenerateTable and click on Run.

8. The new table will be generated with a count for each month that contains a value of “Red” and the “Person(s)” for which this applies.

9. You can update this table by clicking a button to refresh the data in the new table.

I hope this helps!