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
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
