Find Missing Date Values

Question

I have the below subset of data in column A and B:

Name (cell A1)	Date (cell B1)
Smith,John	4/18/2015
Smith,John	4/25/2015
Smith,John	5/2/2015
Smith,John	5/9/2015
Smith,John	5/16/2015
Doe,Jane	4/18/2015
Doe,Jane	4/25/2015
Doe,Jane	5/9/2015
Doe,Jane	5/16/2015
Curious,George	4/18/2015
Curious,George	5/2/2015
Curious,George	5/16/2015

I am trying to find a way that will produce the results for the missing date values relative to the name in column A. For example: Doe,Jane is missing 5/2/2015 and Curious,George is missing 4/25/2015 and 5/9/2015. 

The possible values in the date column are as follows: 4/18/2015, 4/25/2015, 5/2/2015, 5/9/2015, 5/16/2015

I have over 5,000 rows of data for this, so filtering one name at a time and manually searching is not ideal. Thanks in advance for any suggestions!

Answer 1

You could also use a VBA macro.  In the code below, change the worksheet names as appropriate.

To enter this Macro (Sub), <alt-F11> opens the Visual Basic Editor.

Ensure your project is highlighted in the Project Explorer window.

Then, from the top menu, select Insert/Module and

paste the code below into the window that opens.

To use this Macro (Sub), <alt-F8> opens the macro dialog box. Select the macro by name, and <RUN>.

===================================

Option Explicit

Sub MissingDates()

    Dim colNames As Collection, colDates As Collection

    Dim wsSrc As Worksheet, wsRes As Worksheet, rRes As Range

    Dim vSrc As Variant, vRes() As Variant

    Dim I As Long, J As Long

'Change worksheet names as needed

Set wsSrc = Worksheets("Sheet1")

Set wsRes = Worksheets("Sheet2")

    Set rRes = wsRes.Cells(1, 1)

With wsSrc

    vSrc = .Range(.Cells(2, 1), .Cells(.Rows.Count, "A").End(xlUp)).Resize(columnsize:=2)

End With

'Get Unique Name and Date lists

Set colNames = New Collection

Set colDates = New Collection

On Error Resume Next

For I = 1 To UBound(vSrc, 1)

    colNames.Add vSrc(I, 1), CStr(vSrc(I, 1))

    colDates.Add vSrc(I, 2), CStr(vSrc(I, 2))

Next I

On Error GoTo 0

'consider if sorting required

'Results array

ReDim vRes(0 To colNames.Count * colDates.Count, 1 To 2)

vRes(0, 1) = "Name"

vRes(0, 2) = "Date"

For I = 1 To colNames.Count

    For J = 1 To colDates.Count

        vRes((I - 1) * colDates.Count + J, 1) = colNames(I)

        vRes((I - 1) * colDates.Count + J, 2) = colDates(J)

    Next J

Next I

'Write the results to worksheet

Set rRes = rRes.Resize(UBound(vRes, 1) + 1, UBound(vRes, 2))

With rRes

    .EntireColumn.Clear

    .Value = vRes

    With .Rows(1)

        .Font.Bold = True

        .HorizontalAlignment = True

    End With

    .EntireColumn.AutoFit

End With

End Sub

==================================

Answer 2

Hi,

Create a pivot table:

Count of Name (cell A1)	Column Labels
Row Labels	04/18/2015	04/25/2015	05/02/2015	05/09/2015	05/16/2015	Grand Total
Curious,George	1		1		1	3
Doe,Jane	1	1		1	1	4
Smith,John	1	1	1	1	1	5
Grand Total	3	2	2	2	3	12

Answer 3

So copy the name column to another worksheet with the header.  Then use the Data=>Remove Duplicates on the new column to get a unique list of names.

Now in B1 put the header

B2:  =OFFSET($A$2,TRUNC((ROW()-2)/5),0)

Now copy this down column B until you run out of names

In another set of cells enter your five dates vertically (in a single column, 5 rows)

select these five cells and copy  (or use a sequence of five cells from your original sheet that contains all the dates)

Select from C2 down to the last row that has a name in column B and paste. 

Select column B and C and do copy, then paste=>values

delete column A