VBA dynamic concatenate

[skinlo]

Hi,

First of all, my level of VBA knowledge is hacking together things I find on the internet, so I apolgise if this is a stupid question.

I have the following table (made up details, but same format):

Server	Patch No	Date
Server 1	1	27/04/22
Server 1	2	27/04/22
Server 1	3	27/04/22
Server 1	4	25/04/22

I want to turn it into something that looks like this:

Server	Patch Nos	Date
Server 1	1, 2, 3	27/04/22
Server 1	4	25/04/22

Basically I want a way to concatenate based on the date, which could change depending the data coming through (so it could be 20 entries with 5 different dates). Looking online, it seems you have to statically define what you want to be concatenate in VBA., I was wondering if there was a way you could do it dynamically, where it wouldn't matter how long the data is and where you could filter it by date.

Thanks

 

[sijpie]

Read the comments, act on those starting with <<<<

Option Explicit

Sub CondenseTable()
    Dim vIn As Variant, vOut As Variant
    Dim lRi As Long, UB As Long, lRo As Long, lRi2 As Long
    Dim sPatch As String, sServ As String
    Dim dtDate As Date
    
    'read input table into array for fast processing
    vIn = Range("A1").CurrentRegion '<<<< set to top left of table
    UB = UBound(vIn, 1) 'number of rows in table
    
    ReDim vOut(1 To UB, 1 To 3) '<<< assuming 3 columns as per example
    lRo = 1
    'copy header row
    For lRi = 1 To 3
        vOut(1, lRi) = vIn(1, lRi)
    Next lRi
    'now go through input table
    For lRi = 2 To UB   'skip header
        sServ = vIn(lRi, 1)
        If Len(sServ) Then
            sPatch = vIn(lRi, 2)
            dtDate = vIn(lRi, 3)
            For lRi2 = lRi + 1 To UB
                If vIn(lRi2, 1) Like sServ And vIn(lRi2, 3) = dtDate Then
                    sPatch = sPatch & ", " & vIn(lRi2, 2)
                    vIn(lRi2, 1) = ""   'clear server name so won't be processed again
                End If
            Next lRi2
            lRo = lRo + 1
            vOut(lRo, 1) = sServ
            vOut(lRo, 2) = sPatch
            vOut(lRo, 3) = dtDate
            
        End If
    Next lRi
    
    Range("E1").Resize(UB, 3) = vOut    '<<<< set address to where you want your output table to begin
End Sub