Combine columns based on data from multiple rows in VBA

Question

I am trying to merge rows which are the same from A-K, but where L is different I would like them in the same cell.

Example data

A	B	C	D	E	F	G	H	I	J	K	L
ABC123	8981	1/12/2020	77777	Site Name 1	123 Lane		London	UK	SW14	Chrome	Manufacturer
ABC123	8981	1/12/2020	77777	Site Name 1	123 Lane		London	UK	SW14	Edge	Developer
ABC123	8981	1/12/2020	77777	Site Name 1	123 Lane		London	UK	SW14	Chrome	Importer
BGC123	1234	1/1/2019	22222	Site Name 2	123 Street		Manchester	UK	CH13	IE	Importer
BGC123	1234	1/1/2019	22222	Site Name 2	123 Street		Manchester	UK	CH13	Safari	Developer
BGC123	1234	1/1/2019	22222	Site Name 2	123 Street		Manchester	UK	CH13	Safari	Developer
BGC123	1234	1/1/2019	22222	Site Name 2	123 Street		Manchester	UK	CH13	Safari	Importer
XXX345	9999	25/10/2023	55555	Site Name 3	123 Road		Liverpool	UK	LI12	Edge	Importer

Trying to achieve this

A	B	C	D	E	F	G	H	I	J	K	L
ABC123	8981	1/12/2020	77777	Site Name 1	123 Lane		London	UK	SW14	Chrome	Manufacturer, Importer
ABC123	8981	1/12/2020	77777	Site Name 1	123 Lane		London	UK	SW14	Edge	Developer
BGC123	1234	1/1/2019	22222	Site Name 2	123 Street		Manchester	UK	CH13	IE	Importer
BGC123	1234	1/1/2019	22222	Site Name 2	123 Street		Manchester	UK	CH13	Safari	Developer, Importer
XXX345	9999	25/10/2023	55555	Site Name 3	123 Road		Liverpool	UK	LI12	Edge	Importer

The next step

A	B	C	D	E	F	G	H	I	J	K	L	M	N
ABC123	8981	1/12/2020	77777	Site Name 1	123 Lane		London	UK	SW14	Chrome	Manufacturer, Importer	Edge	Developer
BGC123	1234	1/1/2019	22222	Site Name 2	123 Street		Manchester	UK	CH13	IE	Importer	Safari	Developer, Importer
XXX345	9999	25/10/2023	55555	Site Name 3	123 Road		Liverpool	UK	LI12	Edge	Importer

I will try to figure this out once the first part is done, unless it can be done at the same time.

Answer 1

VBA version:

Sub Tester()
    Const N_KEY_COLS = 10 'Columns which identify a unique 
                          '  row in output data
    Dim dict As Object, rw As Range, k As String, cOut As Range, brws, role, c As Range, v
    Set dict = CreateObject("scripting.dictionary")
    
    Set cOut = ThisWorkbook.Sheets("Data").Range("N2") 'first output cell
    
    Set rw = ThisWorkbook.Sheets("Data").Range("A2").Resize(1, N_KEY_COLS) 'first address
    Do While Application.CountA(rw) > 0
        k = RowKey(rw) 'get a "key" for the address info
        If Not dict.Exists(k) Then
            cOut.Offset(dict.count).Resize(1, N_KEY_COLS).Value = rw.Value
            dict.Add k, dict.count
        End If
        brws = rw.Cells(N_KEY_COLS).Offset(0, 1).Value
        role = rw.Cells(N_KEY_COLS).Offset(0, 2).Value
        Set c = cOut.Offset(dict(k), N_KEY_COLS)
        Do
            v = c.Value
            If v = brws Or Len(v) = 0 Then 'adding content?
                If Len(v) = 0 Then c.Value = brws
                AddIfNewValue c.Offset(0, 1), role
                Exit Do
            End If
            Set c = c.Offset(0, 2)
        Loop
        
        Set rw = rw.Offset(1) 'next input row
    Loop
End Sub

'Join all values in row range `rw` with "~~" to create a "key"
Function RowKey(rw As Range)
    RowKey = Join(Application.Transpose(Application.Transpose(rw.Value)), "~~")
End Function

'Add value `vNew` to range `c` if not already present
'  Values in c are separated by vbLf
Sub AddIfNewValue(c As Range, vNew)
    Dim v, arr, el
    v = c.Value
    If Len(v) = 0 Then
        c.Value = vNew
    Else
        arr = Split(v, vbLf)
        For Each el In arr
            If el = vNew Then Exit Sub 'nothing to add
        Next
        c.Value = v & vbLf & vNew
    End If
End Sub