# Sequential number rows based on Cluster, Date and Time

#### cogumelo

##### Board Regular
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

 Date Hour Cluster Sequential 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.

### Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

#### Marcelo Branco

##### MrExcel MVP
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.

#### cogumelo

##### Board Regular
Hi Marcelo,

Great help @Marcelo Branco. Thanks!

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

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 , Date Hour Cluster Sequential Numbering Date Hour Cluster Sequential 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

#### Marcelo Branco

##### MrExcel MVP
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.

#### cogumelo

##### Board Regular
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!

#### Marcelo Branco

##### MrExcel MVP
You're welcome. Thanks for the feedback.

M.

Replies
2
Views
655
Replies
1
Views
259
Replies
3
Views
91
Replies
1
Views
89
Replies
2
Views
116

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,152,570
Messages
5,770,915
Members
425,652
Latest member
Pemby

### 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.

### Which adblocker are you using?

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

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