Find Rows with Single Dates

[pwill]

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

	A	B	C	D			A	B	C	D
1	Date					1	Date
2	01/01/17					2	01/01/17
3	01/01/17					3	01/01/17
4	02/01/17					4	02/01/17
5	02/01/17					5	02/01/17
6	03/01/17					6	03/01/17
7	03/01/17					7	03/01/17
8	04/01/17					8	04/01/17
9	04/01/17					9	04/01/17
10	05/01/17					10	05/01/17
11	05/01/17					11	05/01/17
12	06/01/17					12	06/01/17
13	06/01/17					13	06/01/17
14	07/01/17					14	07/01/17	Single Date on Row 14
15	07/01/17					15	08/01/17
16	08/01/17					16	08/01/17
17	08/01/17					17	09/01/17
18	09/01/17					18	09/01/17
19	09/01/17					19	10/01/17
20	10/01/17					20	10/01/17
21	10/01/17					21
22						22
23						23
24						24

<colgroup><col style="width:48pt" width="64" span="11"> </colgroup><tbody>
</tbody>

Any help would be appreciated

Regards
pwill

 

[Scott T]

In column B try and copy down

=IF(AND(COUNTIF(A2:A3,A3)=1,COUNTIF(A2:A4,A3)=1),"missing date","OK")

 

[pwill]

In column B try and copy down

=IF(AND(COUNTIF(A2:A3,A3)=1,COUNTIF(A2:A4,A3)=1),"missing date","OK")

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

 

[oldbrewer]

01/01/2017	2	####
01/01/2017	2
02/01/2017	2
02/01/2017	2
03/01/2017	1
04/01/2017	2
04/01/2017	2
	#####
	=COUNTIF($A$1:$A$8,A1)
	if you applied conditional formatting to every cell
	=COUNTIF($A$1:$A$8,A1)<2
	in A1 and copy down with format painter
	single dates would turn red

<colgroup><col width="64" span="7" style="width:48pt"> </colgroup><tbody>
</tbody>

 

[Rick Rothstein]

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","")

 

[pwill]

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

 

[pwill]

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","")

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

 

[Rick Rothstein]

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?

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.

 

[pwill]

Thanks Rick,

That works great, much appreciated

regards
pwill

 

[C with no eyes]

Press CTRL+F.

That's one method but there is a quicker way so you can view all blanks cells in a column all at once...

Use the filter in the data menu to show only blank cells.