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

Employee Number	Employee	Date	Hours
1	Mr A	03/04/2018	8.00
1	Mr A	04/04/2018	8.00
1	Mr A	05/04/2018	8.00
5	Mr B	03/04/2018	8.00
5	Mr B	04/04/2018	8.00
5	Mr B	05/04/2018	8.00
5	Mr B	06/04/2018	8.00
6	Mrs C	03/04/2018	8.00
6	Mrs C	04/04/2018	8.00
6	Mrs C	05/04/2018	8.00
8	Mr D	03/04/2018	8.00

<tbody>
</tbody>

The data to be presented is as follows,

	Mr A	Mr B	Mrs C	Mr D
02/04/2018	No	No	No	No
03/04/2018	Yes	Yes	Yes	Yes
04/04/2018	Yes	Yes	Yes	No
05/04/2018	Yes	Yes	Yes	No
06/04/2018	No	Yes	No	No

<tbody>
</tbody>

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