Populate Rows & Columns by Comma Split (DYNAMIC VERSION ver.2)

[airforceone]

Populate Rows & Columns by Comma Split version 1

Good day mate, got the problem above solved!
but it would be a bit of a problem to implement since my sheet column is dynamic starting from 96 columns and 6000 rows on a quarterly period
would anyone kindly help this poor guy to implement a dynamic version based on given set of records

CODE	eDate	Col03	Col04	Col05	Col06	Col07	Col08	Col09	Col10	Col11	Col12	RepDate	RepTime	ComDate	ComTime	Col13	Col14	Col15	Col16	Col17	Col18	Col19	SELLER	BUYER	Col19	Col20	Col21	Col22	Col23	Col24	Col25	Col26	Col27	Col28	Col29	Col30	Col31	Col32	Col33	Col34	Col35	Col36	Col37	Col38	Col39	Col40	Col41	FPDate	Col41	Col42	Col43	FCDate	Col43	Col44	Col45	Col46	Col47	Col48	Col49	Col50	Col51	Col52	Col53	Col54	Col55	Col56	Col57	Col58	Col59	Col60	Col61	Col62	Col63	Col64	Col65	Col66	Col67	Col68	Col69	Col70	Col71
A0001	2024-03-07 11:25:11	Data 00	Data 01	Data 02	Data 03	Data 04	Data 05	Data 06	Data 07	Data 08	Data 09	2024-03-06	13:40:00	2024-03-05	16:09:00	Data 00	Data 01	Data 02	Data 03	Data 04	Data 05	Data 06	Robert Smith (18/Male/Hospitalized/Alien/STUDENT), Maria Garcia (43/Female/Hospitalized/Alien/FARMER)	Mary Smith (2/Male/Hospitalized/Alien/JOBLESS)	Data 00	Data 00	Data 00	Data 00	Data 00	Data 00	Data 00	Data 00	Data 00	Data 00	Data 00	Data 00	Data 00	Data 00	Data 00	Data 00	Data 00	Data 00	Data 00	Data 00	Data 00	Data 00	Data 00	2024-03-07	Data 00	Data 01	Data 02	2024-03-07	Data 00	Data 01	Data 02	Data 03	Data 04	Data 05	Data 06	Data 07	Data 08	Data 09	Data 10	Data 11	Data 12	Data 13	Data 14	Data 15	Data 16	Data 17	Data 18	Data 19	Data 20	Data 21	Data 22	Data 23	Data 24	Data 25	Data 26	Data 27	Data 28
A0002	2024-03-07 11:25:12	Data 00	Data 01	Data 02	Data 03	Data 04	Data 05	Data 06	Data 07	Data 08	Data 09	2024-03-07	13:40:01	2024-03-06	16:09:01	Data 00	Data 01	Data 02	Data 03	Data 04	Data 05	Data 06	Gerald Golbuno (22/Male/Hospitalized/Alien/STUDENT)	Maria Hernandez (34/Female/Hospitalized/Alien/JOBLESS), James Johnson (40/Male/Hospitalized/Alien/JOBLESS), Maria Martinez (37/Female/Hospitalized/Alien/JOBLESS)	Data 01	Data 01	Data 01	Data 01	Data 01	Data 01	Data 01	Data 01	Data 01	Data 01	Data 01	Data 01	Data 01	Data 01	Data 01	Data 01	Data 01	Data 01	Data 01	Data 01	Data 01	Data 01	Data 01	2024-03-08	Data 00	Data 01	Data 02	2024-03-08	Data 00	Data 01	Data 02	Data 03	Data 04	Data 05	Data 06	Data 07	Data 08	Data 09	Data 10	Data 11	Data 12	Data 13	Data 14	Data 15	Data 16	Data 17	Data 18	Data 19	Data 20	Data 21	Data 22	Data 23	Data 24	Data 25	Data 26	Data 27	Data 28
A0003	2024-03-07 11:25:13	Data 00	Data 01	Data 02	Data 03	Data 04	Data 05	Data 06	Data 07	Data 08	Data 09	2024-03-08	13:40:02	2024-03-07	16:09:02	Data 00	Data 01	Data 02	Data 03	Data 04	Data 05	Data 06	Michael Smith (49/Male/Hospitalized/Alien/DRIVER), James Smith (18/Male/Hospitalized/Alien/JOBLESS)	Robert Smith (18/Male/Hospitalized/Alien/STUDENT), Maria Garcia (43/Female/Hospitalized/Alien/FARMER)	Data 02	Data 02	Data 02	Data 02	Data 02	Data 02	Data 02	Data 02	Data 02	Data 02	Data 02	Data 02	Data 02	Data 02	Data 02	Data 02	Data 02	Data 02	Data 02	Data 02	Data 02	Data 02	Data 02	2024-03-09	Data 00	Data 01	Data 02	2024-03-09	Data 00	Data 01	Data 02	Data 03	Data 04	Data 05	Data 06	Data 07	Data 08	Data 09	Data 10	Data 11	Data 12	Data 13	Data 14	Data 15	Data 16	Data 17	Data 18	Data 19	Data 20	Data 21	Data 22	Data 23	Data 24	Data 25	Data 26	Data 27	Data 28

 

[LongToast]

I assume :
1. Your Seller column always in ("X" column which is 24)
2. Your Buyer column always in ("Y" column which is 25)
3. Your Data always start at "A2" (A1 Header)

Try this :

Option Explicit

Private Sub Sync()
    Dim oWs As Worksheet
    Dim arrData, arrSeller, arrBuyer, arrReport As Variant
    Dim iLastColumn, iLastRow, arrayCounter, i, j, k, l As Long
  
    Set oWs = ThisWorkbook.Worksheets("Sheet1")'YOUR SHEET NAME
    iLastColumn = oWs.Cells.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
    iLastRow = oWs.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
  
    arrData = oWs.Range("A2:" & Split(Cells(1, iLastColumn).Address, "$")(1) & iLastRow).Value
  
    For i = LBound(arrData) To UBound(arrData)
        arrSeller = Split(arrData(i, 24), ",")
        arrBuyer = Split(arrData(i, 25), ",")

        arrayCounter = arrayCounter + ((UBound(arrSeller) + 1) * (UBound(arrBuyer) + 1))
    Next i

    ReDim arrReport(1 To arrayCounter, 1 To iLastColumn)

    arrayCounter = 1

    For i = LBound(arrData) To UBound(arrData)
        arrSeller = Split(arrData(i, 24), ",")
        arrBuyer = Split(arrData(i, 25), ",")

        For j = LBound(arrSeller) To UBound(arrSeller)
            For k = LBound(arrBuyer) To UBound(arrBuyer)
                For l = 1 To iLastColumn
                    If l = 24 Then
                        arrReport(arrayCounter, l) = arrSeller(j)
                    ElseIf l = 25 Then
                        arrReport(arrayCounter, l) = arrBuyer(k)
                    Else
                        arrReport(arrayCounter, l) = arrData(i, l)
                    End If
                Next l
              
                arrayCounter = arrayCounter + 1
            Next k
        Next j
    Next i

    oWs.Range("A2").Resize(UBound(arrReport, 1), UBound(arrReport, 2)).Value = arrReport
End Sub

 

[airforceone]

I assume :
1. Your Seller column always in ("X" column which is 24)
2. Your Buyer column always in ("Y" column which is 25)
3. Your Data always start at "A2" (A1 Header)

safe to say yes, but in the event I need to search such header I used below code to locate

    For iCtr = 1 To ColSource
        If UCase(Cells(1, iCtr).Value) Like "*Seller*" Then
        End If

also, if the need arise I just have to change the following line of code right?

arrSeller = Split(arrData(i, 24), ",")
arrBuyer = Split(arrData(i, 25), ",")

 

[LongToast]

safe to say yes, but in the event I need to search such header I used below code to locate

    For iCtr = 1 To ColSource
        If UCase(Cells(1, iCtr).Value) Like "*Seller*" Then
        End If

also, if the need arise I just have to change the following line of code right?

To make it more flexible, you can populate Header row to an array first and find the column index through looping with your code above, after you get the proper index column for "Seller" and "Buyer" you can combine with code from me :

arrSeller = Split(arrData(i, 24), ",")
arrBuyer = Split(arrData(i, 25), ",")

 

[airforceone]

To make it more flexible, you can populate Header row to an array first and find the column index through looping with your code above, after you get the proper index column for "Seller" and "Buyer" you can combine with code from me :

arrSeller = Split(arrData(i, 24), ",")
arrBuyer = Split(arrData(i, 25), ",")

thanks mate