Populating a table in excel to assess missing timesheet submissions

[Northster]

Hello,

I am attempting to set up table which will allow me to assess employees who have not submitted a time-sheet.

The raw data is as follows

[TABLE="width: 352"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Employee Number[/TD]
[TD]Employee[/TD]
[TD]Date[/TD]
[TD]Hours[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Mr A[/TD]
[TD]03/04/2018[/TD]
[TD]8.00[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Mr A[/TD]
[TD]04/04/2018[/TD]
[TD]8.00[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Mr A[/TD]
[TD]05/04/2018[/TD]
[TD]8.00[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Mr B[/TD]
[TD]03/04/2018[/TD]
[TD]8.00[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Mr B[/TD]
[TD]04/04/2018[/TD]
[TD]8.00[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Mr B[/TD]
[TD]05/04/2018[/TD]
[TD]8.00[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Mr B[/TD]
[TD]06/04/2018[/TD]
[TD]8.00[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Mrs C[/TD]
[TD]03/04/2018[/TD]
[TD]8.00[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Mrs C[/TD]
[TD]04/04/2018[/TD]
[TD]8.00[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Mrs C[/TD]
[TD]05/04/2018[/TD]
[TD]8.00[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Mr D[/TD]
[TD]03/04/2018[/TD]
[TD]8.00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
The data to be presented is as follows,

[TABLE="width: 343"]
<tbody>[TR]
[TD="colspan: 2"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Mr A[/TD]
[TD]Mr B[/TD]
[TD]Mrs C[/TD]
[TD]Mr D[/TD]
[/TR]
[TR]
[TD]02/04/2018[/TD]
[TD]No[/TD]
[TD]No[/TD]
[TD]No[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD]03/04/2018[/TD]
[TD]Yes[/TD]
[TD]Yes[/TD]
[TD]Yes[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]04/04/2018[/TD]
[TD]Yes[/TD]
[TD]Yes[/TD]
[TD]Yes[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD]05/04/2018[/TD]
[TD]Yes[/TD]
[TD]Yes[/TD]
[TD]Yes[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD]06/04/2018[/TD]
[TD]No[/TD]
[TD]Yes[/TD]
[TD]No[/TD]
[TD]No[/TD]
[/TR]
</tbody>[/TABLE]

I was wondering if this is possible without using a pivot table

Any help or pointers would be greatly appreciated.

Thanks

 

[BarryL]

A simple formula would do the trick. I had your dataset in A1:D12 with headers. Then B18:D18 were the names and A19:A23 were the dates.

then in B19 i put the below formula and dragged it down and accross and got the output you desire.

​=IF(COUNTIFS($B$2:$B$12,B$18,$C$2:$C$12,$A19)>0,"Yes","No")

 

[MickG]

This code creates a second table starting "F1", and based on dates and Names in the first table.

[COLOR=navy]Sub[/COLOR] MG21Aug25
[COLOR=navy]Dim[/COLOR] Rng [COLOR=navy]As[/COLOR] Range, Dn [COLOR=navy]As[/COLOR] Range, n [COLOR=navy]As[/COLOR] [COLOR=navy]Long,[/COLOR] num [COLOR=navy]As[/COLOR] [COLOR=navy]Long,[/COLOR] Fdate [COLOR=navy]As[/COLOR] Date, Ldate [COLOR=navy]As[/COLOR] Date, Rw [COLOR=navy]As[/COLOR] [COLOR=navy]Long[/COLOR]
[COLOR=navy]Dim[/COLOR] Dt [COLOR=navy]As[/COLOR] Date, Dic [COLOR=navy]As[/COLOR] Object, col [COLOR=navy]As[/COLOR] [COLOR=navy]Long,[/COLOR] K [COLOR=navy]As[/COLOR] Variant, P [COLOR=navy]As[/COLOR] Variant
[COLOR=navy]Set[/COLOR] Rng = Range(Range("B2"), Range("B" & Rows.Count).End(xlUp))
Fdate = Format(Application.Min(Rng.Offset(, 1)), "dd/mm/yyyy")
Ldate = Format(Application.Max(Rng.Offset(, 1)), "dd/mm/yyyy")
Rw = Ldate - Fdate
[COLOR=navy]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
    [COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] Dn [COLOR=navy]In[/COLOR] Rng
        [COLOR=navy]If[/COLOR] Not .Exists(Dn.Value) [COLOR=navy]Then[/COLOR] .Add Dn.Value, New Collection
            .Item(CStr(Dn.Value)).Add CStr(Dn.Offset(, 1).Value)
    [COLOR=navy]Next[/COLOR]
    ReDim ray(1 To Rw + 2, 1 To .Count + 1)
        [COLOR=navy]Set[/COLOR] Dic = CreateObject("scripting.dictionary")
            Dic.CompareMode = vbTextCompare
    [COLOR=navy]For[/COLOR] Dt = Fdate To Ldate
        n = n + 1
        Dic(Dt) = (n + 1)
        ray(n + 1, 1) = Dt
    [COLOR=navy]Next[/COLOR] Dt
col = 1
    [COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] K [COLOR=navy]In[/COLOR] .keys
        col = col + 1
        ray(1, col) = K
            [COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] P [COLOR=navy]In[/COLOR] .Item(K)
                Dt = P
                ray(Dic(Dt), col) = "Yes"
            [COLOR=navy]Next[/COLOR] P
    [COLOR=navy]Next[/COLOR] K
[COLOR=navy]With[/COLOR] Range("F1").Resize(UBound(ray, 1), UBound(ray, 2))
    .Value = ray
    .Offset(, 1).Resize(, UBound(ray, 2) - 1).SpecialCells(xlCellTypeBlanks).Value = "No"
[COLOR=navy]End[/COLOR] With
[COLOR=navy]End[/COLOR] With
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]

Regards Mick