Sequential number rows based on Cluster, Date and Time

cogumelo

Board Regular
Joined
Mar 23, 2006
Messages
181
Office Version
  1. 365
Platform
  1. Windows
Hi guys,

i'm trying to get a formula done where i can get a sequential numbering inside each cluster (example in the table below).

Basically, inside each cluster the first date and the earliest hour will be numbered 1 and so on.
When the next cluster starts the sequential numbering restarts from 1 and so on.

As an example to explain a little better what i'm trying to accomplish:
Cluster one (which is sorted by date and time) shows a sequential numbering of 1-9.
On Cluster 2, that's not sorted, the sequential will be 1,2,5,6,3,7...
And cluster 3 and so on will be numbered accordingly

DateHourClusterSequential Numbering
01/01/2021​
08:00​
1​
1​
02/01/2021​
08:00​
1​
2​
02/01/2021​
12:00​
1​
3​
02/01/2021​
15:00​
1​
4​
03/01/2021​
08:00​
1​
5​
03/01/2021​
10:00​
1​
6​
03/01/2021​
12:00​
1​
7​
03/01/2021​
16:00​
1​
8​
03/01/2021​
18:00​
1​
9​
02/02/2021​
08:00​
2​
1​
02/02/2021​
08:00​
2​
2​
04/02/2021​
08:00​
2​
5​
05/02/2001​
10:00​
2​
6​
03/02/2021​
12:00​
2​
3​
05/02/2001​
12:00​
2​
7​
03/02/2021​
15:00​
2​
4​
05/02/2001​
16:00​
2​
8​
05/02/2001​
18:00​
2​
9​

any suggestions on how to do it? i'm struggling with the date and time part of it.

thx in advance
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
In your sample there seem to be some wrong dates (year = 2001) but I believe, from the expected results you showed, that the year should be 2021.
I adjusted them by changing the year.

See if this does what you're looking for

Pasta1
ABCD
1DateHourClusterSequential Numbering
201/01/202108:00:0011
302/01/202108:00:0012
402/01/202112:00:0013
502/01/202115:00:0014
603/01/202108:00:0015
703/01/202110:00:0016
803/01/202112:00:0017
903/01/202116:00:0018
1003/01/202118:00:0019
1102/02/202108:00:0021
1202/02/202108:00:0022
1304/02/202108:00:0025
1405/02/202110:00:0026
1503/02/202112:00:0023
1605/02/202112:00:0027
1703/02/202115:00:0024
1805/02/202116:00:0028
1905/02/202118:00:0029
Plan1
Cell Formulas
RangeFormula
D2:D19D2=SUMPRODUCT(--(C$2:C$19=C2),--(A$2:A$19+B$2:B$19<A2+B2))+COUNTIFS(A$2:A2,A2,B$2:B2,B2)


Hope this helps

M.
 
Upvote 0
Solution
Hi Marcelo,

Great help @Marcelo Branco. Thanks!

One problem though, that i think is related to the decimal separator used in Excel.

1628499381640.png


If i change it to "." the formula will work.
The problem seems to be when the decimal separator is "," which happens to be the case in the computer i'm working on right now.
With "," as the separator it won't recognize the hour.

HOUR WITH .HOUR WITH ,
DateHourClusterSequential NumberingDateHourClusterSequential Numbering
44197​
0.333333333333333
1​
#VALUE!​
44197​
0,333333333​
1​
1​
44198​
0.333333333333333
1​
#VALUE!​
44198​
0,333333333​
1​
2​
44198​
0.5
1​
#VALUE!​
44198​
0,5​
1​
3​
44198​
0.625
1​
#VALUE!​
44198​
0,625​
1​
4​
44199​
0.333333333333333
1​
#VALUE!​
44199​
0,333333333​
1​
5​
44199​
0.416666666666667
1​
#VALUE!​
44199​
0,416666667​
1​
6​
44199​
0.5
1​
#VALUE!​
44199​
0,5​
1​
7​
44199​
0.666666666666667
1​
#VALUE!​
44199​
0,666666667​
1​
8​
44199​
0.75
1​
#VALUE!​
44199​
0,75​
1​
9​
44229​
0.333333333333333
2​
#VALUE!​
44229​
0,333333333​
2​
1​
44229​
0.333333333333333
2​
#VALUE!​
44229​
0,333333333​
2​
2​
44231​
0.333333333333333
2​
#VALUE!​
44231​
0,333333333​
2​
5​
44232​
0.416666666666667
2​
#VALUE!​
44232​
0,416666667​
2​
6​
44230​
0.5
2​
#VALUE!​
44230​
0,5​
2​
3​
44232​
0.5
2​
#VALUE!​
44232​
0,5​
2​
7​
44230​
0.625
2​
#VALUE!​
44230​
0,625​
2​
4​
44232​
0.666666666666667
2​
#VALUE!​
44232​
0,666666667​
2​
8​
44232​
0.75
2​
#VALUE!​
44232​
0,75​
2​
9​

Any ideas on how this can be worked around?

thx
 
Upvote 0
This is a problem of your data.
In order for the formula to work the dates and times must be numbers , not texts that look like numbers.

M.
 
Upvote 0
This is a problem of your data.
In order for the formula to work the dates and times must be numbers , not texts that look like numbers.

M.
You're absolutely right. Had some crazy formatting in the date cells. Sorted!

Thanks for you great help!
 
Upvote 0

Forum statistics

Threads
1,223,164
Messages
6,170,435
Members
452,326
Latest member
johnshaji

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