Table Custom ID Automation

DEksel

Board Regular
Joined
Oct 5, 2019
Messages
52
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.

IDDate/TimeData
17/1/21 5:07 AMA
27/1/21 5:09 AMB
27/1/21 5:25 AMB
37/1/21 5:30 AMC
27/1/21 9:14 AMB
17/1/21 9:27 AMA
17/1/21 10:35 AMA
47/2/21 11:40 AMCC
47/2/21 11:49 AMCC
57/2/21 12:12 PMAA
57/2/21 12:17 PMAA
67/2/21 6:00 PMBB
77/2/21 6:02 PMDD
47/2/21 6:05 PMCC
57/2/21 6:21 PMAA
67/2/21 6:29 PMBB
87/3/21 12:01 AMFFF
97/3/21 12:18 AMGGG
107/3/21 11:32 AMHHH
117/3/21 11:36 AMIII
107/3/21 12:04 PMHHH
127/3/21 1:01 PMFFF
137/3/21 1:35 PMAAA
97/3/21 1:44 PMGGG
147/3/21 3:58 PMJJJ
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
How about this? Using Power Query.

New__Document - 2021-07-15T105651.831.xlsx
LMN
1IndexCount.Date/TimeData
217/1/2021 5:07A
327/1/2021 5:09B
427/1/2021 5:25B
537/1/2021 5:30C
627/1/2021 9:14B
717/1/2021 9:27A
817/1/2021 10:35A
947/2/2021 11:40CC
1047/2/2021 11:49CC
1157/2/2021 12:12AA
1257/2/2021 12:17AA
1367/2/2021 18:00BB
1477/2/2021 18:02DD
1547/2/2021 18:05CC
1657/2/2021 18:21AA
1767/2/2021 18:29BB
1887/3/2021 0:01FFF
1997/3/2021 0:18GGG
20107/3/2021 11:32HHH
21117/3/2021 11:36III
22107/3/2021 12:04HHH
2387/3/2021 13:01FFF
24127/3/2021 13:35AAA
2597/3/2021 13:44GGG
26137/3/2021 15:58JJJ
Sheet2


Power Query:
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
 
Upvote 0
Solution
How about this? Using Power Query.

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?
 
Upvote 0
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.
 
Upvote 0
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!!!
 
Upvote 0
I can' create that query. SImple copying the code to advanced editor and error all the time.
 

Attachments

  • eratas_01.png
    eratas_01.png
    60.8 KB · Views: 12
Upvote 0
And lrobbo314, do we understand each other well_ The column ID in source table should be empty at the start.
 
Upvote 0

Forum statistics

Threads
1,213,520
Messages
6,114,101
Members
448,548
Latest member
harryls

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