Creating a training matrix from a dataset

[cderaaf]

Trying to create an employee training matrix from a dataset of employee training records. A simple pivot table would suffice if only it would insert the "course date" as text, and not summarize it as either count/sum. Are tables the solution? Some other tool? I manually transferred the below data (left) to the desired matrix format (right), below. 

[#2]

@cderaaf 

Dataset:

Name	Course	Course Date
Smith	WHMIS	15/03/2020
D'Amico	Working at Heights	20/02/2016
D'Amico	Asbestos	25/04/2019
Culbertson	Basics of Supervising	04/02/2020
Culbertson	Lockout & Tagout	17/07/2016
Culbertson	WHMIS	31/03/2019
Culbertson	Asbestos	19/03/2020
Culbertson	Working at Heights	04/05/2019
Mastroluisi

[#3 · Accepted solution]

@cderaaf 

Two methods.

First:
With Pivot Table. Click one of the cells of your data (for example cell A2) and then create a Pivot Table in same sheet when you are working. In my example I have created it in cell E13.

• Draw the fields as I have made (see the printscreen).
• In the VALUES box right click on Count of Course Date and in Value Field Settings choose Product.
• Select cells F13:J18 and format them as date.

Second:
In colum E create a list with unique names.
In cell F2 apply below formula:

=IFERROR(INDEX($C$2:$C$20,MATCH(1,INDEX(($A$2:$A$20=$E2)*($B$2:$B$20=F$1),),0)),"")

drag it right and than down. Format cells F2:J5 as date.

You can download my file at this link:  https://1drv.ms/x/s!Ah_p7p0xkR5RnWoDDtSELLY1zuHA?e=pfd5SJ

Hope this hepls.

Regards,

IlirU

[#4]

Thank you! That helped. For some reason my Mac version of Excel was returning "0"'s and converting to dates was resulting in a 1900-01-01 date. Using my Windows version of excel and your above solution worked (both methods)