Splitting Dates with Unique ID

[GreyFox8991]

Hello Excel Community,

I have a set of data organized below. Each ID has a few dates associated in the respective column. I would like the end result to be where the ID is split with the respective Dates.... For reference:

9191370|06/09/2022
9191370|07/18/2022
9191400|05/03/2022
9191400|05/05/2022
9191400|07/12/2022

The ID's can be placed in Column A and the Dates can be placed in Column B if possible... Any insight would be greatly appreciated.

Thank you

DOS Splitter.xlsm
	A	B
1	ID	Dates
2	9191370	06/09/2022,07/18/2022
3	9191400	05/03/2022,05/05/2022,07/12/2022
4	9191409	06/03/2022,08/03/2022,10/07/2022
5	9191419	01/03/2022,05/03/2022,07/21/2022
6	9191465	02/22/2022,03/02/2022,03/08/2022,03/10/2022,07/08/2022
7	9191469	08/30/2022,09/13/2022,12/15/2022
8	9191471	02/02/2022,03/09/2022,05/12/2022
9	9069287	06/10/2022,06/11/2022,06/12/2022,06/16/222
10	9257946	08/02/2022,08/03/2022
11	9069291	08/27/2022,08/28/2022,08/29/2022
12	9069295	04/12/2022,04/13/2022,04/14/2022,04/15/2022,04/16/2022
13	9069298	09/22/2022,09/23/2022,09/24/2022,09/25/2022,12/27/2022
14	9312325	06/29/2022,07/19/2022,07/28/2022
15	9303982	10/11/2022,10/12/2022,10/13/2022,10/14/2022,10/15/2022
16	9303984	08/29/2022,08/30/2022
17	9055871	04/21/2022,12/13/2022,12/18/2022
Sheet1

 

[Rick Rothstein]

Give this macro a try...

Sub CombineLikeIDs()
  Dim X As Long, K As Variant, Data As Variant
  Data = Range("A1", Cells(Rows.Count, "B").End(xlUp))
  With CreateObject("Scripting.Dictionary")
    For X = 1 To UBound(Data)
      .Item(Data(X, 1)) = Trim(.Item(Data(X, 1)) & " " & Data(X, 2))
    Next
    K = .Keys
    Range("D1").Resize(UBound(K) + 1) = Application.Transpose(K)
    Range("E1").Resize(UBound(K) + 1) = Application.Transpose(.Items)
    Range("E1").Resize(UBound(K) + 1).Replace " ", ", ", xlPart
  End With
End Sub

 

[GreyFox8991]

Hi Rick,

Thank you for your suggestion... I ran the macro but it is not splitting the ID and the respective date... it copies the same data over to columns D and E... Am i inputting something wrong perhaps? I am including a screenshot of the result if this helps.

 

[Rick Rothstein]

Sorry, my fault... I read your post as having individual rows of data that you wanted to concatenate together. Here is a macro that will do what I now see you want. This code is more verbose than needed for your data, but it is something I wrote years ago to handle more columns that you have where the delimited column could be anywhere within the data grid.

Sub RedistributeData()
  Dim X As Long, LastRow As Long, A As Range, Table As Range, Data() As String
  Const Delimiter As String = ","
  Const DelimitedColumn As String = "B"
  Const TableColumns As String = "A:B"
  Const StartRow As Long = 2
  Application.ScreenUpdating = False
  Columns("B").Replace ", ", ",", xlPart
  LastRow = Columns(TableColumns).Find(What:="*", SearchOrder:=xlRows, _
            SearchDirection:=xlPrevious, LookIn:=xlFormulas).Row
  For X = LastRow To StartRow Step -1
    Data = Split(Cells(X, DelimitedColumn), Delimiter)
    If UBound(Data) > 0 Then
      Intersect(Rows(X + 1), Columns(TableColumns)).Resize(UBound(Data)).Insert xlShiftDown
    End If
    If Len(Cells(X, DelimitedColumn)) Then
      Cells(X, DelimitedColumn).Resize(UBound(Data) + 1) = WorksheetFunction.Transpose(Data)
    End If
  Next
  LastRow = Cells(Rows.Count, DelimitedColumn).End(xlUp).Row
  On Error Resume Next
  Set Table = Intersect(Columns(TableColumns), Rows(StartRow).Resize(LastRow - StartRow + 1))
  If Err.Number = 0 Then
    Table.SpecialCells(xlBlanks).FormulaR1C1 = "=R[-1]C"
    Columns(DelimitedColumn).SpecialCells(xlFormulas).Clear
    Table.Value = Table.Value
  End If
  On Error GoTo 0
  Application.ScreenUpdating = True
End Sub

 

[GreyFox8991]

Hi Rick,

Thank you for providing this code as this is greatly appreciated... The code works to split dates according to its respective ID however, I am wondering if there is a way to include the Unique ID in its respective row for each DOS. The Code seems to work for a smaller dataset but upon using it for a slightly bigger dataset, the Unique ID is shown for the dates followed by what looks to be a formula value in the place of the ID... I am including an example for reference. Could the code be changed to include the respective ID's in this case?

Book6
	A	B	C	D	E	F	G	H	I
1	ID	Dates					ID	Dates	Unique Dates Count
2	C-HF2236	02/08/2022					C-HF2236	02/08/2022,02/15/2022,03/02/2022,02/15/2022,03/09/2022,03/16/2022,03/23/2022,03/28/2022,03/29/2022,04/04/2022,04/05/2022,04/11/2022,04/12/2022,04/18/2022,04/19/2022,04/25/2022,04/26/2022,04/27/2022,04/28/2022,05/02/2022,05/03/2022,05/04/2022,05/09/2022,05/10/2022,05/11/2022,05/16/2022,05/17/2022,05/18/2022,05/24/2022,05/25/2022,05/31/2022,06/01/2022,06/09/2022,06/15/2022,06/22/2022,06/29/2022,07/06/2022,07/13/2022,07/21/2022,08/04/2022,08/18/2022,09/01/2022,09/08/2022,09/15/2022,09/22/2022,09/29/2022,10/06/2022,10/13/2022,10/19/2022,10/26/2022,11/07/2022,11/09/2022,11/23/2022,11/30/2022,12/07/2022,12/14/2022,12/21/2022,12/28/2022	58
3	=R[-1]C	02/15/2022					C-HF23CN	01/03/2022,01/10/2022,01/17/2022,01/10/2022,01/26/2022,02/02/2022,02/08/2022,02/15/2022,02/22/2022,03/01/2022,03/09/2022,03/22/2022,03/29/2022,04/05/2022,04/13/2022,04/20/2022,04/26/2022,05/04/2022,05/11/2022,05/18/2022,05/21/2022,05/25/2022,06/01/2022,06/08/2022,06/14/2022,06/21/2022,06/27/2022,06/30/2022,07/07/2022,07/14/2022,07/18/2022,07/21/2022,07/25/2022,07/27/2022,07/28/2022,08/01/2022,08/02/2022,08/11/2022,08/15/2022,08/17/2022,08/24/2022,08/29/2022,08/31/2022,09/12/2022,09/14/2022,09/16/2022,09/19/2022,09/21/2022,09/26/2022,09/29/2022,10/03/2022,10/06/2022,10/10/2022,10/13/2022,10/17/2022,10/18/2022,10/26/2022,11/02/2022,11/09/2022,11/16/2022,11/23/2022,11/29/2022,12/06/2022,12/13/2022,12/16/2022,12/20/2022,12/27/2022	67
4	=R[-1]C	03/02/2022
5	=R[-1]C	02/15/2022
6	=R[-1]C	03/09/2022
7	=R[-1]C	03/16/2022
8	=R[-1]C	03/23/2022
9	=R[-1]C	03/28/2022
10	=R[-1]C	03/29/2022
11	=R[-1]C	04/04/2022
12	=R[-1]C	04/05/2022
13	=R[-1]C	04/11/2022
14	=R[-1]C	04/12/2022
15	=R[-1]C	04/18/2022
16	=R[-1]C	04/19/2022
17	=R[-1]C	04/25/2022
18	=R[-1]C	04/26/2022
19	=R[-1]C	04/27/2022
20	=R[-1]C	04/28/2022
21	=R[-1]C	05/02/2022
22	=R[-1]C	05/03/2022
23	=R[-1]C	05/04/2022
24	=R[-1]C	05/09/2022
25	=R[-1]C	05/10/2022
26	=R[-1]C	05/11/2022
27	=R[-1]C	05/16/2022
28	=R[-1]C	05/17/2022
29	=R[-1]C	05/18/2022
30	=R[-1]C	05/24/2022
31	=R[-1]C	05/25/2022
32	=R[-1]C	05/31/2022
33	=R[-1]C	06/01/2022
34	=R[-1]C	06/09/2022
35	=R[-1]C	06/15/2022
36	=R[-1]C	06/22/2022
37	=R[-1]C	06/29/2022
38	=R[-1]C	07/06/2022
39	=R[-1]C	07/13/2022
40	=R[-1]C	07/21/2022
41	=R[-1]C	08/04/2022
42	=R[-1]C	08/18/2022
43	=R[-1]C	09/01/2022
44	=R[-1]C	09/08/2022
45	=R[-1]C	09/15/2022
46	=R[-1]C	09/22/2022
47	=R[-1]C	09/29/2022
48	=R[-1]C	10/06/2022
49	=R[-1]C	10/13/2022
50	=R[-1]C	10/19/2022
51	=R[-1]C	10/26/2022
52	=R[-1]C	11/07/2022
53	=R[-1]C	11/09/2022
54	=R[-1]C	11/23/2022
55	=R[-1]C	11/30/2022
56	=R[-1]C	12/07/2022
57	=R[-1]C	12/14/2022
58	=R[-1]C	12/21/2022
59	=R[-1]C	12/28/2022
60	C-HF23CN	01/03/2022
61	=R[-1]C	01/10/2022
62	=R[-1]C	01/17/2022
63	=R[-1]C	01/10/2022
64	=R[-1]C	01/26/2022
65	=R[-1]C	02/02/2022
66	=R[-1]C	02/08/2022
67	=R[-1]C	02/15/2022
68	=R[-1]C	02/22/2022
69	=R[-1]C	03/01/2022
70	=R[-1]C	03/09/2022
71	=R[-1]C	03/22/2022
72	=R[-1]C	03/29/2022
73	=R[-1]C	04/05/2022
74	=R[-1]C	04/13/2022
75	=R[-1]C	04/20/2022
76	=R[-1]C	04/26/2022
77	=R[-1]C	05/04/2022
78	=R[-1]C	05/11/2022
79	=R[-1]C	05/18/2022
80	=R[-1]C	05/21/2022
81	=R[-1]C	05/25/2022
82	=R[-1]C	06/01/2022
83	=R[-1]C	06/08/2022
84	=R[-1]C	06/14/2022
85	=R[-1]C	06/21/2022
86	=R[-1]C	06/27/2022
87	=R[-1]C	06/30/2022
88	=R[-1]C	07/07/2022
89	=R[-1]C	07/14/2022
90	=R[-1]C	07/18/2022
91	=R[-1]C	07/21/2022
92	=R[-1]C	07/25/2022
93	=R[-1]C	07/27/2022
94	=R[-1]C	07/28/2022
95	=R[-1]C	08/01/2022
96	=R[-1]C	08/02/2022
97	=R[-1]C	08/11/2022
98	=R[-1]C	08/15/2022
99	=R[-1]C	08/17/2022
100	=R[-1]C	08/24/2022
101	=R[-1]C	08/29/2022
102	=R[-1]C	08/31/2022
103	=R[-1]C	09/12/2022
104	=R[-1]C	09/14/2022
105	=R[-1]C	09/16/2022
106	=R[-1]C	09/19/2022
107	=R[-1]C	09/21/2022
108	=R[-1]C	09/26/2022
109	=R[-1]C	09/29/2022
110	=R[-1]C	10/03/2022
111	=R[-1]C	10/06/2022
112	=R[-1]C	10/10/2022
113	=R[-1]C	10/13/2022
114	=R[-1]C	10/17/2022
115	=R[-1]C	10/18/2022
116	=R[-1]C	10/26/2022
117	=R[-1]C	11/02/2022
118	=R[-1]C	11/09/2022
119	=R[-1]C	11/16/2022
120	=R[-1]C	11/23/2022
121	=R[-1]C	11/29/2022
122	=R[-1]C	12/06/2022
123	=R[-1]C	12/13/2022
124	=R[-1]C	12/16/2022
125	=R[-1]C	12/20/2022
126	=R[-1]C	12/27/2022
Sheet1

 

[alansidman]

You could also use Power Pivot as shown in this magazine article.

https://sfmagazine.com/articles/2018/july/excel-reporting-text-in-a-pivot-table/?psso=true

 

[Akuini]

@GreyFox8991
Another option:

Sub GreyFox8991()

Dim i As Long, j As Long, k As Long, n As Long
Dim va, vb

va = Range("A1", Cells(Rows.Count, "A").End(xlUp)).Resize(, 2)
ReDim vb(1 To UBound(va, 1), 1 To 2)
For i = 2 To UBound(va, 1)
 j = i
    Do
        i = i + 1
        If i > UBound(va, 1) Then Exit Do
    Loop While va(i, 1) = va(i - 1, 1)
    
        i = i - 1
        k = k + 1
        vb(k, 1) = va(i, 1)
        For n = j To i
            vb(k, 2) = vb(k, 2) & "," & va(n, 2)
        Next
        vb(k, 2) = Mid(vb(k, 2), 2)
Next

Range("D2").Resize(k, 2) = vb
End Sub

Example:
Result in col D:E

Book1
	A	B	C	D	E
1
2	9191370	06/09/2022		9191370	06/09/2022,07/18/2022
3	9191370	07/18/2022		9191400	05/03/2022,05/05/2022,07/12/2022
4	9191400	05/03/2022
5	9191400	05/05/2022
6	9191400	07/12/2022
7
Sheet4