Find Rows with Single Dates

P

pwill

Active Member
Joined
Nov 22, 2015
Messages
406
Hi can anyone help?

I have Dates in column A2:A sheet1 that keep a record of two inputs of data per day.
There should be the same date every two rows.

Is there an easy way to check column A to find any rows where dates might be missing

ie
the left example has two dates per row A2:A21
the right example has a date missing A14


<colgroup><col style="width:48pt" width="64" span="11"> </colgroup><tbody>
[TD="width: 64, align: center"]
[/TD]
[TD="width: 64, align: center"] A [/TD]
[TD="width: 64, align: center"] B [/TD]
[TD="width: 64, align: center"] C [/TD]
[TD="width: 64, align: center"] D [/TD]
[TD="width: 64, align: center"]
[/TD]
[TD="width: 64, align: center"]
[/TD]
[TD="width: 64, align: center"] A [/TD]
[TD="width: 64, align: center"] B [/TD]
[TD="width: 64, align: center"] C [/TD]
[TD="width: 64, align: center"] D [/TD]

[TD="align: center"] 1 [/TD]
[TD="align: center"] Date [/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"] 1 [/TD]
[TD="align: center"] Date [/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]

[TD="align: center"] 2 [/TD]
[TD="class: xl65, align: center"] 01/01/17 [/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"] 2 [/TD]
[TD="class: xl65, align: center"] 01/01/17 [/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]

[TD="align: center"] 3 [/TD]
[TD="class: xl65, align: center"] 01/01/17 [/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"] 3 [/TD]
[TD="class: xl65, align: center"] 01/01/17 [/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]

[TD="align: center"] 4 [/TD]
[TD="class: xl65, align: center"] 02/01/17 [/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"] 4 [/TD]
[TD="class: xl65, align: center"] 02/01/17 [/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]

[TD="align: center"] 5 [/TD]
[TD="class: xl65, align: center"] 02/01/17 [/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"] 5 [/TD]
[TD="class: xl65, align: center"] 02/01/17 [/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]

[TD="align: center"] 6 [/TD]
[TD="class: xl65, align: center"] 03/01/17 [/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"] 6 [/TD]
[TD="class: xl65, align: center"] 03/01/17 [/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]

[TD="align: center"] 7 [/TD]
[TD="class: xl65, align: center"] 03/01/17 [/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"] 7 [/TD]
[TD="class: xl65, align: center"] 03/01/17 [/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]

[TD="align: center"] 8 [/TD]
[TD="class: xl65, align: center"] 04/01/17 [/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"] 8 [/TD]
[TD="class: xl65, align: center"] 04/01/17 [/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]

[TD="align: center"] 9 [/TD]
[TD="class: xl65, align: center"] 04/01/17 [/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"] 9 [/TD]
[TD="class: xl65, align: center"] 04/01/17 [/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]

[TD="align: center"] 10 [/TD]
[TD="class: xl65, align: center"] 05/01/17 [/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"] 10 [/TD]
[TD="class: xl65, align: center"] 05/01/17 [/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]

[TD="align: center"] 11 [/TD]
[TD="class: xl65, align: center"] 05/01/17 [/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"] 11 [/TD]
[TD="class: xl65, align: center"] 05/01/17 [/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]

[TD="align: center"] 12 [/TD]
[TD="class: xl65, align: center"] 06/01/17 [/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"] 12 [/TD]
[TD="class: xl65, align: center"] 06/01/17 [/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]

[TD="align: center"] 13 [/TD]
[TD="class: xl65, align: center"] 06/01/17 [/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"] 13 [/TD]
[TD="class: xl65, align: center"] 06/01/17 [/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]

[TD="align: center"] 14 [/TD]
[TD="class: xl65, align: center"] 07/01/17 [/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"] 14 [/TD]
[TD="class: xl65, align: center"] 07/01/17 [/TD]
[TD="colspan: 3, align: center"] Single Date on Row 14 [/TD]

[TD="align: center"] 15 [/TD]
[TD="class: xl65, align: center"] 07/01/17 [/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"] 15 [/TD]
[TD="class: xl65, align: center"] 08/01/17 [/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]

[TD="align: center"] 16 [/TD]
[TD="class: xl65, align: center"] 08/01/17 [/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"] 16 [/TD]
[TD="class: xl65, align: center"] 08/01/17 [/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]

[TD="align: center"] 17 [/TD]
[TD="class: xl65, align: center"] 08/01/17 [/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"] 17 [/TD]
[TD="class: xl65, align: center"] 09/01/17 [/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]

[TD="align: center"] 18 [/TD]
[TD="class: xl65, align: center"] 09/01/17 [/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"] 18 [/TD]
[TD="class: xl65, align: center"] 09/01/17 [/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]

[TD="align: center"] 19 [/TD]
[TD="class: xl65, align: center"] 09/01/17 [/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"] 19 [/TD]
[TD="class: xl65, align: center"] 10/01/17 [/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]

[TD="align: center"] 20 [/TD]
[TD="class: xl65, align: center"] 10/01/17 [/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"] 20 [/TD]
[TD="class: xl65, align: center"] 10/01/17 [/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]

[TD="align: center"] 21 [/TD]
[TD="class: xl65, align: center"] 10/01/17 [/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"] 21 [/TD]
[TD="class: xl65, align: center"] [/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]

[TD="align: center"] 22 [/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"] 22 [/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]

[TD="align: center"] 23 [/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"] 23 [/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]

[TD="align: center"] 24 [/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"] 24 [/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]

</tbody>


Any help would be appreciated

Regards
pwill
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
Sort by date Sort by votes
In column B try and copy down
Code: 
=IF(AND(COUNTIF(A2:A3,A3)=1,COUNTIF(A2:A4,A3)=1),"missing date","OK")
 
Upvote 0
Scott T said:
In column B try and copy down
Code: 
=IF(AND(COUNTIF(A2:A3,A3)=1,COUNTIF(A2:A4,A3)=1),"missing date","OK")
Click to expand...

Hi Scott T,

Thank you, is there a way to leave the OK cells blank so I can "ctrl Shift and down in column B to goto the missing row?

regards
pwill
 
Upvote 0
[TABLE="width: 448"]
<colgroup><col width="64" span="7" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl22, width: 64"]01/01/2017[/TD]
[TD="width: 64, align: right"]2[/TD]
[TD="width: 64"]####[/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[/TR]
[TR]
[TD="class: xl22, width: 64"]01/01/2017[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl22, width: 64"]02/01/2017[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl22, width: 64"]02/01/2017[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl22, width: 64"]03/01/2017[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl22, width: 64"]04/01/2017[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl22, width: 64"]04/01/2017[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]#####[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 3"]=COUNTIF($A$1:$A$8,A1)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 5"]if you applied conditional formatting to every cell[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 3"]=COUNTIF($A$1:$A$8,A1)<2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 4"]in A1 and copy down with format painter[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 3"]single dates would turn red[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Another way... assuming your dates start on Row 2, put this formula in cell B3 and copy down to the end of your data...

=IF(AND(A1<>A2,A2<>A3),"missing date","")
 
Last edited:
Upvote 0
Thanks oldbrewer,

I am trying to find a way to quickly go to the missing dates ie "ctrl Shift and down" as there are hundreds of rows

regards
pwill
 
Upvote 0
Rick Rothstein said:
Another way... assuming your dates start on Row 2, put this formula in cell B3 and copy down to the end of your data...

=IF(AND(A1<>A2,A2<>A3),"missing date","")
Click to expand...



Thanks Rick, yes thats what i am looking for but it doesn't allow me to goto the missing row when I use ctrl Shift and down in column B, would there be a way around this?

regards
pwill
 
Last edited:
Upvote 0
pwill said:
Thanks Rick, yes thats what i am looking for but it doesn't allow me to goto the missing row when I use ctrl Shift and down in column B, would there be a way around this?
Click to expand...
I don't think there is a direct shortcut like that. If you select cell B2, press CTRL+F to bring up the Find dialog box, put an asterisk in the "Find what" field, click the "Options>>" button and change the "Look in" drop down to "Values" and then press the "Find Next" button, it will take you to the first occurrence of the word "missing"... click the "Find Next" button and it will take you to the second occurrence of the word "missing" if there is one.
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Solved
Age Group Ranging by gender
Replies
2
Views
275
Raj1979
R
L
  • Solved
Help Please
2 3
Replies
21
Views
532
Asbestos_Jen
Asbestos_Jen
A
  • Solved
Count entry by time
Replies
3
Views
253
admvlad
A
SFCChase
  • Question Question
Use Dropdown to search other tabs
Replies
4
Views
167
SFCChase
SFCChase
D
  • Question Question
Formula help needed for working with days and times.
Replies
0
Views
423
dshtesy
D

Forum statistics

Threads
1,224,824
Messages
6,181,187
Members
453,020
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

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