Split items into Rows

[GreyFox8991]

Greetings Excel Community,

Below I have some Test data where the Unique ID has a relationship with the "Value" column. For each UniqueID, there must be a corresponding row from the "value" Column. What I am trying to accomplish is a formula or VBA Script that will split the UniqueID into its respective column followed by the value in the "value" column related to the ID. For example Unique ID 123 should have five values with 101,102,103,104,105 in its respective row/column. A caveat to this would be if the VBA Script/Formula take into consideration date formatting if possible. Any insight would be greatly appreciated... I am including some sample data for context. Please review the "End Results" for what the result should possibly be:

Book9
	A	B
1	Unformatted Data
2	Unique Id	Value
3	123	101,102,103,104,105
4	456	200,201,203,204,205
5	789	09/11/2023,09/12/2023,09/13/2023
6	101112	10/01/2023,10/02/2023,10/03/2023
7
8
9
10	End Result
11	UniqueID	Value
12	123	101
13	123	102
14	123	103
15	123	104
16	123	105
17	456	200
18	456	201
19	456	203
20	456	204
21	456	205
22	789	9/11/2023
23	789	9/12/2023
24	789	9/13/2023
25	101112	10/1/2023
26	101112	10/2/2023
27	101112	10/3/2023
Sheet1

 

[lrobbo314]

Here is one way.

ROWSPLIT
	A	B	C	D	E
1	Unique Id	Value		Unique Id	Value
2	123	101,102,103,104,105		123	101
3	456	200,201,203,204,205		123	102
4	789	09/11/2023,09/12/2023,09/13/2023		123	103
5	101112	10/01/2023,10/02/2023,10/03/2023		123	104
6				123	105
7				456	200
8				456	201
9				456	203
10				456	204
11				456	205
12				789	9/11/2023
13				789	9/12/2023
14				789	9/13/2023
15				101112	10/1/2023
16				101112	10/2/2023
17				101112	10/3/2023
Sheet1

Sub ROWSPLIT()
Dim RNG As Range:       Set RNG = Range("A2:B" & Range("A" & Rows.Count).End(xlUp).Row)
Dim AR() As Variant:    AR = RNG.Value2
Dim AL As Object:       Set AL = CreateObject("System.Collections.ArrayList")
Dim SP() As String

For i = 1 To UBound(AR)
    SP = Split(AR(i, 2), ",")
    For j = 0 To UBound(SP)
        AL.Add Join(Array(AR(i, 1), SP(j)), ";")
    Next j
Next i

Set RNG = Range("D2").Resize(AL.Count)

With RNG
    .Value = Application.Transpose(AL.toArray)
    .TextToColumns DataType:=xlDelimited, Semicolon:=True
End With

End Sub

 

[GreyFox8991]

Greetings lrobbo314,

Thank you for your reply... I have tested this and works as noted. Appreciate your diligent suggestion!