# Find missing dates in column A

#### jackt05

##### Board Regular
Hi Everybody,

I have two columns

Date Cab No

1 4501
2 4501
3 4501
5 4501
1 3603
2 3603
5 3603

The cab no 4501 is absent on 4th & cab 3603 is absent on 3rd and 4th
i have more than four thousand entries with 50 cabs how to find with count if or any other formula the missing dates of the cabs

Jackt05

### Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

#### pgc01

##### MrExcel MVP
Hi

2 questions.

- are you only interested in the missing dates in your sequences or do you also want to consider that date sequences should always start at 1.

In the examples I post, how many dates are missing, the 3 you referred in your post, or 6, because the third sequence starts at 4 (considering, therefore, that dates 1,2,3 are missing in that sequence)

- can we consider that the data is sorted like you posted, grouped by Cab No and sorted by Date in each Cab No group, ascending?

#### jackt05

##### Board Regular
Thanks pgc01,

Yes you can consider that the data is sorted like posted, grouped by Cab No and sorted by Date in each Cab No group, ascending.
My idea is creating a pivot table but it does not take the data which is not filled if cab is absent on any date the date is not entered but I want it in pivot table to show 0 sum on that day therefore I will insert the missing dates If I find them exactly the date on which the cab is absent

Sorry for Late posting Electricity was shut down in our area

#### pgc01

##### MrExcel MVP
This code will write the missing dates:

Code:
``````Sub MissigDates()
Dim lLastRow As Long, lRow As Long, lIns As Long

lLastRow = Range("A" & Rows.Count).End(xlUp).Row
Application.ScreenUpdating = False
For lRow = lLastRow - 1 To 2 Step -1
If Range("B" & lRow) = Range("B" & lRow + 1) Then
lIns = Range("A" & lRow + 1).Value - Range("A" & lRow).Value - 1
If lIns <> 0 Then
Rows(lRow + 1).Resize(lIns).Insert
Range("A" & lRow).DataSeries rowcol:=xlColumns, stop:=Range("A" & lRow + lIns + 1)
Range("B" & lRow).AutoFill Range("B" & lRow).Resize(lIns + 1), xlFillValues
End If
End If
Next lRow
Application.ScreenUpdating = True
End Sub``````

Before and after:

Last edited:

#### jackt05

##### Board Regular
Hi pgc01,

I copied your code but what formula should I use and where it should be entered how does the dat fill in And in your example cab 2000 is from 3rd so in next update it is not showing 1st and 2nd Date

Thanks

#### pgc01

##### MrExcel MVP
Now if the first date in a cab no is not 1 the missing dates are inserted.

Try:

Code:
``````Sub MissigDates()
Dim lLastRow As Long, lRow As Long, lIns As Long
lLastRow = Range("A" & Rows.Count).End(xlUp).Row

Application.ScreenUpdating = False
For lRow = lLastRow - 1 To 2 Step -1
lIns = 0
If Range("B" & lRow) = Range("B" & lRow + 1) Then
lIns = Range("A" & lRow + 1).Value - Range("A" & lRow).Value - 1
ElseIf Range("B" & lRow) <> Range("B" & lRow + 1) And Range("A" & lRow + 1) <> 1 Then
lIns = Range("A" & lRow + 1).Value - 2
lRow = lRow + 1
Rows(lRow).Insert
Range("A" & lRow) = 1
Range("B" & lRow) = Range("B" & lRow + 1)
End If
If lIns <> 0 Then
Rows(lRow + 1).Resize(lIns).Insert
Range("A" & lRow).DataSeries rowcol:=xlColumns, stop:=Range("A" & lRow + lIns + 1)
Range("B" & lRow).AutoFill Range("B" & lRow).Resize(lIns + 1), xlFillValues
End If
Next lRow
Application.ScreenUpdating = True
End Sub``````

To execute the code
- go to the vb editor (alt-F11)
- insert a module (module1 if it's the first)
- paste the code
- click inside the code and press F5

#### jackt05

##### Board Regular
Hi Sir,

After Copying the code and pressing F5 it is showing a debugging box after pressing the debug tab the line
Rows(lRow + 1).Resize(lIns).Insert is highlighted in yellow.

#### pgc01

##### MrExcel MVP
Hi again

I can only see that error happening if the code could not insert a row, for example because it was crossing a pivot table.

This would fix it, as now instead of inserting rows, it just inserts cells in columns A:B.

Please test this first in a worksheet that has only the initial table I posted.

Code:
``````Sub MissigDates()
Dim lLastRow As Long, lRow As Long, lIns As Long

lLastRow = Range("A" & Rows.Count).End(xlUp).Row
Application.ScreenUpdating = False
For lRow = lLastRow - 1 To 2 Step -1
lIns = 0
If Range("B" & lRow) = Range("B" & lRow + 1) Then
lIns = Range("A" & lRow + 1).Value - Range("A" & lRow).Value - 1
ElseIf Range("B" & lRow) <> Range("B" & lRow + 1) And Range("A" & lRow + 1) <> 1 Then
lIns = Range("A" & lRow + 1).Value - 2
lRow = lRow + 1
Rows(lRow).Insert
Range("A" & lRow) = 1
Range("B" & lRow) = Range("B" & lRow + 1)
End If
If lIns <> 0 Then
Range("A" & lRow + 1).Resize(lIns, 2).Insert shift:=xlShiftDown
Range("A" & lRow).DataSeries rowcol:=xlColumns, stop:=Range("A" & lRow + lIns + 1)
Range("B" & lRow).AutoFill Range("B" & lRow).Resize(lIns + 1), xlFillValues
End If
Next lRow
Application.ScreenUpdating = True
End Sub``````

#### jackt05

##### Board Regular
Hello Mr. PGC,

I copied your pasted data and the code and it worked very well as desired
but after copying my data in that sheet it come out with another debugging tab I don't understand how this can happen.

If you wish I can send you my sheet of data to try on, but where

Thanks jackt05

#### jackt05

##### Board Regular
and Mr. PGC,

I am sorry to mention that maybe it is showing an error because I think so there is one point to be considered that the cab can do 4 to 10 duties in a day and that is mentioned in the data eg.

date cab no

<TABLE style="WIDTH: 96pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=128 border=0><COLGROUP><COL style="WIDTH: 48pt" span=2 width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right width=64 height=20>1</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right width=64>461</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20>1</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right>461</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20>2</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right>461</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20>6</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right>461</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20>6</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right>461</TD></TR></TBODY></TABLE>
<TABLE style="WIDTH: 96pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=128 border=0><COLGROUP><COL style="WIDTH: 48pt" span=2 width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right width=64 height=20>9</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right width=64>1445</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20>10</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right>1445</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20>10</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right>1445</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20>12</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right>1445</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20>12</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right>1445</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20>12</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right>1445</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20>12</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right>1445</TD></TR></TBODY></TABLE><TABLE style="WIDTH: 96pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=128 border=0><COLGROUP><COL style="WIDTH: 48pt" span=2 width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right width=64 height=20>1</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right width=64>1772</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20>1</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right>1772</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20>2</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right>1772</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20>2</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right>1772</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20>2</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right>1772</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20>2</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right>1772</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20>2</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right>1772</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20>2</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right>1772</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20>5</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right>1772</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20>5</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right>1772</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20>5</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right>1772</TD></TR></TBODY></TABLE>

jackt05

Replies
7
Views
280
Replies
8
Views
343
Replies
5
Views
373
Replies
2
Views
266
Replies
4
Views
3K

1,191,584
Messages
5,987,479
Members
440,097
Latest member
Wint

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