Combine Dates with one UniqueID

GreyFox8991

New Member
Joined
Jul 20, 2022
Messages
20
Office Version
  1. 2016
Platform
  1. Windows
Greetings Excel community,

I have a set of ID's and dates which are associated to these ID's. The ID's are repeated but there are numerous dates associated with its respective ID. What I am attempting to accomplish is combining all the dates associated with the specific ID. For example, ID 123 has four dates associated with it. I am trying to create a formula or a solution that will associate all the dates with the respective ID. One column will have the Unique ID, and the other will contain all the dates associated with that specific ID number. I am including a current state and End State mini sheet for reference if needed. Any insight would be appreciated:

Book1
AB
1CurrentState
2IDDate
312301/01/2023,01/02/2023
412301/03/2023,01/04/2023
545602/02/2023,02/01/2023,04/01/2023
645604/05/2023,06/06/2023,07/07/2023
745604/06/2023,06/07/2023,08/09/2023,07/08/2023
878901/01/2023,05/01/2023
978902/01/2023,02/02/2023
1078903/01/2023,01/04/2023,01/05/2023,01/06/2023
1178901/07/2023,01/08/2023,01/09/2023,01/10/2023
12
13
14
15
16EndState
1712301/01/2023,01/02/2023,01/03/2023,01/04/2023
1845602/02/2023,02/01/2023,04/01/2023,04/05/2023,06/06/2023,07/07/2023
1978901/01/2023,05/01/2023,02/01/2023,02/02/2023,03/01/2023,01/04/2023,01/05/2023,01/06/2023,01/07/2023,01/08/2023,01/09/2023,01/10/2023
Sheet1
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
Here's one option. Assumes your data is in columns A & B and the "EndState" doesn't exist before running the code.
VBA Code:
Sub Concat_Dates()
    Dim R As Long, LRow As Long, Data
    LRow = Cells(Rows.Count, "A").End(xlUp).Row
    Data = Sheets("Sheet1").Range("A2:B" & LRow)
    With CreateObject("Scripting.Dictionary")
        For R = 1 To UBound(Data)
            .Item(Data(R, 1)) = .Item(Data(R, 1)) & " , " & Data(R, 2)
            If Left(.Item(Data(R, 1)), 3) = " , " Then .Item(Data(R, 1)) = Mid(.Item(Data(R, 1)), 4)
        Next
        Sheets("Sheet1").Range("A" & LRow + 3).Resize(.Count) = Application.Transpose(.keys)
        Sheets("Sheet1").Range("B" & LRow + 3).Resize(.Count) = Application.Transpose(.Items)
    End With
End Sub



Before:
Book1
AB
1CurrentState
2IDDate
312301/01/2023,01/02/2023
412301/03/2023,01/04/2023
545602/02/2023,02/01/2023,04/01/2023
645604/05/2023,06/06/2023,07/07/2023
745604/06/2023,06/07/2023,08/09/2023,07/08/2023
878901/01/2023,05/01/2023
978902/01/2023,02/02/2023
1078903/01/2023,01/04/2023,01/05/2023,01/06/2023
1178901/07/2023,01/08/2023,01/09/2023,01/10/2023
12
13
14
15
16
17
18
Sheet1


After:
Book1
AB
1CurrentState
2IDDate
312301/01/2023,01/02/2023
412301/03/2023,01/04/2023
545602/02/2023,02/01/2023,04/01/2023
645604/05/2023,06/06/2023,07/07/2023
745604/06/2023,06/07/2023,08/09/2023,07/08/2023
878901/01/2023,05/01/2023
978902/01/2023,02/02/2023
1078903/01/2023,01/04/2023,01/05/2023,01/06/2023
1178901/07/2023,01/08/2023,01/09/2023,01/10/2023
12
13
14IDDate
1512301/01/2023,01/02/2023 , 01/03/2023,01/04/2023
1645602/02/2023,02/01/2023,04/01/2023 , 04/05/2023,06/06/2023,07/07/2023 , 04/06/2023,06/07/2023,08/09/2023,07/08/2023
1778901/01/2023,05/01/2023 , 02/01/2023,02/02/2023 , 03/01/2023,01/04/2023,01/05/2023,01/06/2023 , 01/07/2023,01/08/2023,01/09/2023,01/10/2023
18
Sheet1
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,186
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top