Table Custom ID Automation

[DEksel]

I have next table (growing each day). In each date/time moment (row), I have some event which may be repeated within one day (or not). Problem is ID column: I am entering IDs values manually and making mistakes of course. How to make some kind of automation:
a) within one day (date),
b) within several days.
VBA or worksheet formula(s)... never mind. Thanks in advance.

P. S.
So far, the only my idea is to filter table per each date, re-sort table by Data and create IDs by comparission previous and next data. But maybe there are some more ellegant way... some indexes, matches, coffees... dunno.

ID	Date/Time	Data
1	7/1/21 5:07 AM	A
2	7/1/21 5:09 AM	B
2	7/1/21 5:25 AM	B
3	7/1/21 5:30 AM	C
2	7/1/21 9:14 AM	B
1	7/1/21 9:27 AM	A
1	7/1/21 10:35 AM	A
4	7/2/21 11:40 AM	CC
4	7/2/21 11:49 AM	CC
5	7/2/21 12:12 PM	AA
5	7/2/21 12:17 PM	AA
6	7/2/21 6:00 PM	BB
7	7/2/21 6:02 PM	DD
4	7/2/21 6:05 PM	CC
5	7/2/21 6:21 PM	AA
6	7/2/21 6:29 PM	BB
8	7/3/21 12:01 AM	FFF
9	7/3/21 12:18 AM	GGG
10	7/3/21 11:32 AM	HHH
11	7/3/21 11:36 AM	III
10	7/3/21 12:04 PM	HHH
12	7/3/21 1:01 PM	FFF
13	7/3/21 1:35 PM	AAA
9	7/3/21 1:44 PM	GGG
14	7/3/21 3:58 PM	JJJ

 

[lrobbo314]

How about this? Using Power Query.

New__Document - 2021-07-15T105651.831.xlsx
	L	M	N
1	Index	Count.Date/Time	Data
2	1	7/1/2021 5:07	A
3	2	7/1/2021 5:09	B
4	2	7/1/2021 5:25	B
5	3	7/1/2021 5:30	C
6	2	7/1/2021 9:14	B
7	1	7/1/2021 9:27	A
8	1	7/1/2021 10:35	A
9	4	7/2/2021 11:40	CC
10	4	7/2/2021 11:49	CC
11	5	7/2/2021 12:12	AA
12	5	7/2/2021 12:17	AA
13	6	7/2/2021 18:00	BB
14	7	7/2/2021 18:02	DD
15	4	7/2/2021 18:05	CC
16	5	7/2/2021 18:21	AA
17	6	7/2/2021 18:29	BB
18	8	7/3/2021 0:01	FFF
19	9	7/3/2021 0:18	GGG
20	10	7/3/2021 11:32	HHH
21	11	7/3/2021 11:36	III
22	10	7/3/2021 12:04	HHH
23	8	7/3/2021 13:01	FFF
24	12	7/3/2021 13:35	AAA
25	9	7/3/2021 13:44	GGG
26	13	7/3/2021 15:58	JJJ
Sheet2

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    toDate = Table.AddColumn(Source, "Custom", each Date.From([#"Date/Time"])),
    Group = Table.Group(toDate, {"Data", "Custom"}, {{"Count", each _, type table [#"Date/Time"=nullable datetime, Data=nullable text, Custom=date]}}),
    Index = Table.AddIndexColumn(Group, "Index", 1, 1, Int64.Type),
    Expand = Table.ExpandTableColumn(Index, "Count", {"Date/Time"}, {"Count.Date/Time"}),
    Sort = Table.Sort(Expand,{{"Count.Date/Time", Order.Ascending}}),
    RC = Table.RemoveColumns(Sort,{"Custom"}),
    Reorder = Table.ReorderColumns(RC,{"Index", "Count.Date/Time", "Data"})
in
    Reorder

 

[DEksel]

How about this? Using Power Query.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
in
    Reorder

Thank you lrobbo314, it was fast.
I am with excel for about my last 28 years and I never used Power Query. and know nothing about that. When I need some kind of report, I am writing macros which proceed all data and then I am creating new, independent table (for analyzing, charts etc.).

Your solution (I didn't find any error) suggests that it is time to deal with PQ. Now I'll give a try, only few questions remains:
1. My real life table has 14 columns with values and formulas and will have many, many rows (perhaps a couple of thousands...). Power Query can handle that?
2. What is the output of that Power Query? Is it table which can be coppied and become simple, independent table with values and formulas?

 

[lrobbo314]

Excel has a row limit of ~1M rows. Power Query can handle a lot more than that, so it should be fine with what you have to throw at it.

And yes, the output is a table that you can then use just like any other table object.

 

[DEksel]

Excel has a row limit of ~1M rows. Power Query can handle a lot more than that, so it should be fine with what you have to throw at it.

And yes, the output is a table that you can then use just like any other table object.

Seems to me that they now have built-in that feature which am writing all these years by myself ) Need to pass through syntax... I'll let you know about result. Thanks again very mucho!!!

 

[DEksel]

I can' create that query. SImple copying the code to advanced editor and error all the time.

 

[DEksel]

And lrobbo314, do we understand each other well_ The column ID in source table should be empty at the start.