The Supplied Survey Data needs to be transformed. However, this can't be done directly as each column contains essential data.
Using Record ID tool add record number to the data. After that filter only the necessary data rows. Use Transpose and Crosstab tool then to transform data into necessary format. Then using Select tool change the column names as well as the data types.
After that using Summarize tool group data by Departments and find the average score of questions and then again transform using Transpose tool. This is the first part.
The Supplied Benchmark Data contains the questions and Benchmark scores. Use Transpose tool to transform data to vertical axis. After using Transpose tool the output table contains another column "Name2" with values 1, 2, 3 and 4. Replace the values in the Name2 field with the respective department names. Use Sample tool to skip the first 4 rows as this is not required. Then use the Text to Column to split the "Name" column which contains questions into two, # (question number) and Question. Rename and change the datatypes using the Select tool. This is the second part.
Now using the Join tool join the First part and Second part. Then use Filter tool to calculate "Delta" and "Review". Finally sort by "Review" (Descending) and "Delta" (Ascending).
