Old "long" format CSV format to new "Wide" CSV format

The Key Function:

1. =IFERROR(INDEX('RawResponses - QuickSight'!K:K, MATCH(1, ('RawResponses - QuickSight'!D:D=$A2)*('RawResponses - QuickSight'!J:J=B$1), 0)), "")

1. 'RawResponses - QuickSight' - references your primary dataset (your long format dataset) 
   
2. !K:K - references the learner responses to each question
   
3. !D:D - references the Learner GUID you are matching with each question
   
4. !J:J - references the Question you are matching with each Learner GUID
   
5. =$A2 - references back to !D:D, which is the specific Learner GUID you want to compare against
   
1. Example: In the provided templated spreadsheet, cell C5's full function is: =IFERROR(INDEX('RawResponses - QuickSight'!K:K, MATCH(1, ('RawResponses - QuickSight'!D:D=$A5)*('RawResponses - QuickSight'!J:J=C$1), 0)), "")
   
1. $A5 references the specific Learner GUID in cell A5
   
2. C$1 references the specific Question in C1
6. =$B1 - references back to J:J, which is the specific Question you want to compare against
   
1. Example: In the provided templated spreadsheet, cell E12's full function is: =IFERROR(INDEX('RawResponses - QuickSight'!K:K, MATCH(1, ('RawResponses - QuickSight'!D:D=$A12)*('RawResponses - QuickSight'!J:J=E$1), 0)), "")
   
1. $A12 references the specific Learner GUID in cell A12
   
2. E$1 references the specific Question in cell E1

Key Steps:

1. Download the dataset you'd like to convert from "long" format to "wide" format
1. Through your Premium Analytics Dashboard, locate "Course Answers" at the bottom of the dashboard.
   
2. Set your "controls" (filters) to a specific content title. We recommend only utilizing one specific course / resource when creating a "wide formatted" CSV
   
3. Optionally, you can also set your date ranges and/or state(s) and/or facility(s) to limit the data you are initially working with
   
4. Once your filters are set, ideally to one single content title, wait for your Course Answers table to fully load, and then select "Export to CSV" from the menu in the upper right corner of that chart.
2. Import your exported CSV into Google Sheets
   
3. Rename the tab to this data set to be "RawResponses - QuickSight" (without the quotes)
   
1. This will be important later on!
4. Create a PIVOT table for your unique Learner GUID and Questions that you wish to have in your "Wide" format CSV
   
1. Creating a list of unique Learner GUIDs
1. In your only tab "RawResponses - QuickSight", select Insert > Pivot Table
   
2. Ensure you have your whole data range select (by default it should be), set it as "Insert to New Sheet" and click Create
   
3. In your new PIVOT table tab, select "ADD" next to "Rows". Select "Learner GUIDs"
   
2. Creating a list of unique Questions
   
1. Follow the steps above to create another PIVOT table tab, or, once you have your Learner GUIDs copied to your "Wide" format tab below, clear out your "Learner GUID" selection under "Rows" and select "Question"
5. Starting your "Wide" format dataset
   
1. Create a new tab in your existing spreadsheet and call it something relevant to you
   
2. In cell A1, type in Learner GUID
3. From your PIVOT table listing your Learner GUIDs, copy all of the Learner GUIDs and paste them into your new "Wide" format spreadsheet tab (pasting them in starting at cell A2)
   
4. From your PIVOT table listing your Questions, copy all of your questions and paste them into your new "Wide" format spreadsheet tab (pasting them in starting at cell B1)
   
1. Note - When pasting in this data that is copied across multiple cells, you can easily "transpose" that data across columns instead by right clicking into cell B1 and selecting the option "Paste Special > Transposed". This will paste your questions across rows B1, C1, D1, etc.
   
5. Lastly, take the time now to reorganize your questions into the order you'd like to review Learner responses. You can always do this later, but once you are actively pulling in data from your dataset, your spreadsheet will need to be refreshed to repopulate data again.
   
6. Pulling data from your "RawResponses - QuickSight" tab that is set in the "Long" format into your new "Wide" format tab
   
1. Starting out simple, paste the following into cell B2: =IFERROR(INDEX('RawResponses - QuickSight'!K:K, MATCH(1, ('RawResponses - QuickSight'!D:D=$A2)*('RawResponses - QuickSight'!J:J=B$1), 0)), "")
2. If everything up to this point has been configured correctly, you should see the matched learner answer in column B2 (matches that specific Learner GUID in A2 to the Question in B1)
   
1. If you do not see a learner answer filled in at this step, ensure first that you have the function properly pasted in and operating.
   
2. If you do and you still do not see any responses, double check that you have your first tab labeled exactly as "RawResponses - QuickSight" (without the quotes)
   
3. If data is still not appearing, please double check the steps above to ensure everything is set properly and that you did not miss a step
   
3. With data now appearing in cell B2, you can take that same function and paste it into C2: =IFERROR(INDEX('RawResponses - QuickSight'!K:K, MATCH(1, ('RawResponses - QuickSight'!D:D=$A2)*('RawResponses - QuickSight'!J:J=B$1), 0)), "")
   
1. This time though, we need to change one piece of this function, otherwise, we'll just be repeating the same data that we see in cell B2 in C2. 
   
2. (Reference back to "The Key Function" section above if you need more clarity on how this works) - update your function to change out the J:J=B$1 at the very end of the function to be J:J=C$1
   
3. You should now see the correct answer response in cell C2
   
4. Repeat step C here across all of your questions, updating the J:J=B$1 where "B" will be updated to the column you are in
   
5. Once you have the function set properly across your first row of data, select all rows where you have input your function in that first row, B2 - ???) and click & drag and/or copy / paste your function down across all rows where you have a Learner GUID
   
1. If done properly, you will start seeing your responses populating in the "Wide" format! 
   
2. If you have a large dataset (10K or above rows from your source data) this may take several minutes or more to run. You will see a progress bar as well as data slowly trickling in though