Help with formula Number serial based on criteria monthly

Lukma

Active Member
Joined
Feb 12, 2020
Messages
259
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Hi Friends

I have my Month in Column A
And i have My Vessel name In Column C
and i Have the MUS No Column D
The solution formula i need in Column E is to Number for each month and when it get to the following month number should start from One again

so my Example i placed in Dec-22 she 123456, so in Jan-2023 i should have 12345

Appreciate and formula that could number based on criteria

REgards

Onshore Vessel Jetty activity Tracking From 2022-2023.xlsx
ABCDEFG
1MonthZoneSupply VesselMus NoTripChannel Waiting Time & VTS DelayStart Date & Time (Arrival Time at MOSB)
2Dec 22A-CHLOE3206103/12/22 17:20
3Dec 22A-CHLOE3250208/12/22 07:00
4Dec 22A-CHLOE3290314/12/22 06:05
5Dec 22A-CHLOE3345418/12/22 05:30
6Dec 22A-CHLOE3376522/12/22 00:25
7Dec 22A-CHLOE3431627/12/22 00:15
8Jan 23A-CHLOE1202/01/23 06:20
9Jan 23A-CHLOE5507/01/23 01:20
10Jan 23A-CHLOE9911/01/23 18:30
11Jan 23A-CHLOE16119/01/23 01:10
12Jan 23A-CHLOE22025/01/23 08:50
13Feb 23A-CHLOE27802/02/23 07:00
14Feb 23A-CHLOE31906/02/23 01:30
15Feb 23A-CHLOE39114/02/23 02:40
16Feb 23A-CHLOE43818/02/23 07:35
17Feb 23A-CHLOE46123/02/23 14:15
18Feb 23A-CHLOE50427/02/23 04:10
19Mar 23A-CHLOE54502/03/23 01:15
20Mar 23A-CHLOE60007/03/23 14:15
21Mar 23A-CHLOE65212/03/23 02:45
22Mar 23A-CHLOE72518/03/23 08:35
23Mar 23A-CHLOE78225/03/23 01:25
24Mar 23A-CHLOE82729/03/23 01:30
2023_Data_Vessel_Trip
Cell Formulas
RangeFormula
A2:A24A2=IFERROR(EOMONTH(G2,-1)+1,"")
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
Like this ?

Excel Formula:
=COUNTIF($A$2:A2,A2)

1710401994075.png
 
Upvote 0
If the post #2 results are what you want and your data is reasonably large, the following formula would be faster and consume less calculation resources.

24 03 14.xlsm
ABCDE
1MonthZoneSupply VesselMus NoTrip
2Dec 22A-CHLOE32061
3Dec 22A-CHLOE32502
4Dec 22A-CHLOE32903
5Dec 22A-CHLOE33454
6Dec 22A-CHLOE33765
7Dec 22A-CHLOE34316
8Jan 23A-CHLOE121
9Jan 23A-CHLOE552
10Jan 23A-CHLOE993
11Jan 23A-CHLOE1614
12Jan 23A-CHLOE2205
13Feb 23A-CHLOE2781
14Feb 23A-CHLOE3192
15Feb 23A-CHLOE3913
16Feb 23A-CHLOE4384
17Feb 23A-CHLOE4615
18Feb 23A-CHLOE5046
19Mar 23A-CHLOE5451
20Mar 23A-CHLOE6002
21Mar 23A-CHLOE6523
22Mar 23A-CHLOE7254
23Mar 23A-CHLOE7825
24Mar 23A-CHLOE8276
Counter
Cell Formulas
RangeFormula
A2:A24A2=IFERROR(EOMONTH(G2,-1)+1,"")
E2:E24E2=1+IF(A2=A1,E1,0)
 
Upvote 0
Solution

Forum statistics

Threads
1,223,885
Messages
6,175,183
Members
452,615
Latest member
bogeys2birdies

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