Sequential number rows based on Cluster, Date and Time

cogumelo

Board Regular
Joined
Mar 23, 2006
Messages
179
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

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
Joined
Aug 23, 2010
Messages
17,000
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
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.
 
Solution

cogumelo

Board Regular
Joined
Mar 23, 2006
Messages
179
Office Version
  1. 365
Platform
  1. Windows
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
 

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
17,000
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
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
Joined
Mar 23, 2006
Messages
179
Office Version
  1. 365
Platform
  1. Windows
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!
 
Master Excel Bundle

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.

Forum statistics

Threads
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.
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
Top