SOLVED

Creating a training matrix from a dataset

%3CLINGO-SUB%20id%3D%22lingo-sub-2207589%22%20slang%3D%22en-US%22%3ECreating%20a%20training%20matrix%20from%20a%20dataset%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2207589%22%20slang%3D%22en-US%22%3E%3CP%3ETrying%20to%20create%20an%20employee%20training%20matrix%20from%20a%20dataset%20of%20employee%20training%20records.%20A%20simple%20pivot%20table%20would%20suffice%20if%20only%20it%20would%20insert%20the%20%22course%20date%22%20as%20text%2C%20and%20not%20summarize%20it%20as%20either%20count%2Fsum.%20Are%20tables%20the%20solution%3F%20Some%20other%20tool%3F%20I%20manually%20transferred%20the%20below%20data%20(left)%20to%20the%20desired%20matrix%20format%20(right)%2C%20below.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screen%20Shot%202021-03-13%20at%203.07.35%20AM.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F263397iA2858DA7A9AF1213%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Screen%20Shot%202021-03-13%20at%203.07.35%20AM.png%22%20alt%3D%22Screen%20Shot%202021-03-13%20at%203.07.35%20AM.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2207589%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3ECharting%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2207593%22%20slang%3D%22en-US%22%3ERe%3A%20Creating%20a%20training%20matrix%20from%20a%20dataset%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2207593%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F996068%22%20target%3D%22_blank%22%3E%40cderaaf%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDataset%3A%3C%2FP%3E%3CTABLE%20width%3D%22252%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2265%22%3EName%3C%2FTD%3E%3CTD%20width%3D%2265%22%3ECourse%3C%2FTD%3E%3CTD%20width%3D%22122%22%3ECourse%20Date%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3ESmith%3C%2FTD%3E%3CTD%3EWHMIS%3C%2FTD%3E%3CTD%3E15%2F03%2F2020%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3ED'Amico%3C%2FTD%3E%3CTD%3EWorking%20at%20Heights%3C%2FTD%3E%3CTD%3E%3CDIV%20class%3D%22mceNonEditable%20lia-copypaste-placeholder%22%3E%26nbsp%3B%3C%2FDIV%3E%3CP%3E%26nbsp%3B%3C%2FP%3E20%2F02%2F2016%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3ED'Amico%3C%2FTD%3E%3CTD%3EAsbestos%3C%2FTD%3E%3CTD%3E25%2F04%2F2019%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3ECulbertson%3C%2FTD%3E%3CTD%3EBasics%20of%20Supervising%3C%2FTD%3E%3CTD%3E04%2F02%2F2020%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3ECulbertson%3C%2FTD%3E%3CTD%3ELockout%20%26amp%3B%20Tagout%3C%2FTD%3E%3CTD%3E17%2F07%2F2016%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3ECulbertson%3C%2FTD%3E%3CTD%3EWHMIS%3C%2FTD%3E%3CTD%3E31%2F03%2F2019%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3ECulbertson%3C%2FTD%3E%3CTD%3EAsbestos%3C%2FTD%3E%3CTD%3E19%2F03%2F2020%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3ECulbertson%3C%2FTD%3E%3CTD%3EWorking%20at%20Heights%3C%2FTD%3E%3CTD%3E04%2F05%2F2019%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EMastroluisi%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3C%2FLINGO-BODY%3E
New Contributor

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. 

 

Screen Shot 2021-03-13 at 3.07.35 AM.png

 

 

3 Replies

@cderaaf 

Dataset:

NameCourseCourse Date
SmithWHMIS15/03/2020
D'AmicoWorking at Heights
 

 

20/02/2016
D'AmicoAsbestos25/04/2019
CulbertsonBasics of Supervising04/02/2020
CulbertsonLockout & Tagout17/07/2016
CulbertsonWHMIS31/03/2019
CulbertsonAsbestos19/03/2020
CulbertsonWorking at Heights04/05/2019
Mastroluisi  
best response confirmed by cderaaf (New Contributor)
Solution

Untitled.png

 

 

@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

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)