Forum Discussion
Creating a training matrix from a dataset
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
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 |
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
- 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)