Transpose data from row/column that matches certain criteria

Question

I conducted a survey that asked for a Score and a Priority for each question.

The response data lists each component in individual rows, so that it's treating Score and Priority as different questions, instead of as different responses for the same question, like this:

Currently, if the question ID is x.x.1, it is a Score, but if the ID is x.x.2, it is a Priority.

I need it to be presented so that the Score and Priority are listed as values in columns for each question, like this:

Any ideas how to get there?

Ultimately I'm trying to make this a flat table that is pivotable. But right now if I try to pivot it, I can't separate out Score and Priority. It treats them as separate questions, instead of two different scores for the same question, as it should be.

Answer 1

Solution: I used a multi-step method similar to what @gtwebb suggested, except something less elegant.

1. Use Text-to-Columns to split out the last decimal place of the Question ID (this is really the key to it). The last decimal tells me it is either 1=Score or 2=Priority.
2. Use an if() statement to look for the value "2" in my new column. If there, then copy the associated priority value to a new Priority column. If not there, leave it blank. Copy formula down.
3. Then filter and delete all the rows that had a value of "2" because my rows with value "1" now have both Score and Priority in two columns within the same row.
4. Rename columns as needed.
5. Pivot, ???, and profit!!

Answer 2

Something like this will work, depending on your data

Option Explicit
Sub ScorePriority()
    Dim lastRow As Long
    lastRow = Cells(Rows.Count, 1).End(xlUp).Row
    Dim i As Long

    For i = lastRow To 2 Step -1
        If Cells(i, 2) = Cells(i - 1, 2) And _
        Cells(i, 3) = Cells(i - 1, 3) And _
        Left(Cells(i, 4), 4) = Left(Cells(i - 1, 4), 4) Then
            Cells(i - 1, 6) = Cells(i, 5)
            Cells(i).EntireRow.Delete shift:=xlUp
        End If
    Next

End Sub

Answer 3

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:

1. Import the data in Power Query (Ribbon Power Query -> from Table, cursor must be somewhere in your data)
2. (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)
3. Right-Click the column Question -> Split Column by delimiter -> at the right most delimiter, delimiter --Custom-- = .
4. Select the new column question.2, under the ribbon Transform, click on Pivot Column, define Score as value
5. Rename the columns "Question.1", "1", "2" to "Question", "Score", "Priority" (with double click on the column name)
6. 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"