You may try the free Microsoft Add-In Power Query (from Excel 2010). It's quite intuitive for Loading and transforming your data as represented in the second table. Up Excel 2016 it's fully integrated in Excel (Get and Transform).
Follow these steps in the UI of Power Query:
- Import the data in Power Query (Ribbon Power Query -> from Table, cursor must be somewhere in your data)
- (optional) change the type of the column Question to text (depending of your OS settings for number formating, PQ would recognize 1.1.1 as a date)
- Right-Click the column Question -> Split Column by delimiter -> at the right most delimiter, delimiter --Custom-- = .
- Select the new column question.2, under the ribbon Transform, click on Pivot Column, define Score as value
- Rename the columns "Question.1", "1", "2" to "Question", "Score", "Priority" (with double click on the column name)
- Close and load to Excel (Ribbon Home)
here the Power Query script, which is produced by the UI.
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"FirstName", type text}, {"LastName", type text}, {"Question", type text}, {"Score", Int64.Type}}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type","Question",Splitter.SplitTextByEachDelimiter({"."}, QuoteStyle.Csv, true),{"Question.1", "Question.2"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Question.1", type number}, {"Question.2", Int64.Type}}),
#"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Changed Type1", {{"Question.2", type text}}, "de-CH"), List.Distinct(Table.TransformColumnTypes(#"Changed Type1", {{"Question.2", type text}}, "de-CH")[Question.2]), "Question.2", "Score", List.Sum),
#"Renamed Columns" = Table.RenameColumns(#"Pivoted Column",{{"Question.1", "Question"}, {"1", "Score"}, {"2", "Priority"}})
in
#"Renamed Columns"
Add a flag =if(right(D2,1)=1,"Score","Priority") and look into unpivoting from there. – gtwebb – 2016-05-09T18:32:42.057
@gtwebb right, that's assuming the data structure here is the actual data structure, which makes it much more difficult – Raystafarian – 2016-05-09T18:35:51.713