my excel dilemma?

[melovecookies]

How can I get the survey results below shown as second table below? Idea is to capture names of associates who answered "1" in a single cell.

Raw data:

	question 1	question 2	question3
Associate1	1	1	2
Associate2​ <strike></strike>	3	1	1
Associate3​	2	2	2
Associate4​	3	3	3
Associate5 ​ <strike></strike>	1	1	1

<tbody>
</tbody>

Desired end results: (would like to get associates names in a single cell who answered questions as "1"

Question1	Associate1, Associate5
Question2	Associate1, Associate2, Associate5
Question3	Associate1, Associate5

<tbody>
</tbody>

Thanks for your help

<colgroup><col><col span="3"></colgroup><tbody>
</tbody>

 

[sandy666]

if you are able to use PowerQuery (Get&Transform) try

Asc	question 1	question 2	question3		Attribute	Asc.1	Asc.2	Asc.3
Associate1	1​	1​	2​		question 1	Associate1	Associate5
Associate2	3​	1​	1​		question 2	Associate1	Associate2	Associate5
Associate3	2​	2​	2​		question3	Associate2	Associate5
Associate4	3​	3​	3​
Associate5	1​	1​	1​

[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table5"]}[Content],
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Asc"}, "Attribute", "Value"),
    #"Filtered Rows" = Table.SelectRows(#"Unpivoted Other Columns", each ([Value] = 1)),
    #"Grouped Rows" = Table.Group(#"Filtered Rows", {"Attribute"}, {{"Count", each _, type table}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Asc", each Table.Column([Count],"Asc")),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom", {"Asc", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Values", "Asc", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Asc.1", "Asc.2", "Asc.3"}),
    #"Removed Columns" = Table.RemoveColumns(#"Split Column by Delimiter",{"Count"})
in
    #"Removed Columns"[/SIZE]

btw. your expected result isn't correct

 

[melovecookies]

I am not familiar with powerquery but I will try. Thanks for catching my typo and correcting it. This was my first post and hoping to get better

 

[sandy666]

Look at:
PowerQuery (Get&Transform)
PowerQuery for Excel

 

[mse330]

Hi melovecookies,

​

This is another approach using VBA assuming your data starts in cell A1

Sub SurveySummary()
Dim Arr() As Variant, AnswerNumber As Integer
AnswerNumber = 1 '<-- Change this to whatever answer number you need
Arr = Application.Transpose(ActiveSheet.Range("A1").CurrentRegion.Value)
For x = 2 To UBound(Arr)
    For y = 2 To UBound(Arr, 2)
        If Arr(x, y) = AnswerNumber Then
            Arr(x, 2) = IIf(IsNumeric(Arr(x, 2)), Arr(1, y), Arr(x, 2) & ", " & Arr(1, y))
        End If
    Next
Next
With Sheets.Add(After:=ActiveSheet)
    .Range("A1").Resize(UBound(Arr), 2).Value = Arr
    .Range("A1:B1") = Array("Question #", "Associates Picked # " & AnswerNumber)
    .Columns("A:B").AutoFit
End With
End Sub