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:

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]question 1[/TD]
[TD]question 2[/TD]
[TD]question3[/TD]
[/TR]
[TR]
[TD]Associate1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]

Associate2​

<strike></strike>
[/TD]
[TD]3[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]

Associate3​

[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]

Associate4​

[/TD]
[TD]3[/TD]
[TD]3[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]

Associate5 ​

<strike></strike>
[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]


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

[TABLE="width: 500"]
<tbody>[TR]
[TD]Question1[/TD]
[TD]Associate1, Associate5[/TD]
[/TR]
[TR]
[TD]Question2[/TD]
[TD]Associate1, Associate2, Associate5[/TD]
[/TR]
[TR]
[TD]Question3[/TD]
[TD]Associate1, Associate5[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 292"]
<colgroup><col><col span="3"></colgroup><tbody>[TR]
[TD]Thanks for your help [/TD]
[TD="colspan: 3"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 3"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 3"][/TD]
[/TR]
</tbody>[/TABLE]

 

[sandy666]

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

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]Asc[/td][td=bgcolor:#5B9BD5]question 1[/td][td=bgcolor:#5B9BD5]question 2[/td][td=bgcolor:#5B9BD5]question3[/td][td][/td][td=bgcolor:#70AD47]Attribute[/td][td=bgcolor:#70AD47]Asc.1[/td][td=bgcolor:#70AD47]Asc.2[/td][td=bgcolor:#70AD47]Asc.3[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Associate1[/td][td=bgcolor:#DDEBF7]

1​

[/td][td=bgcolor:#DDEBF7]

1​

[/td][td=bgcolor:#DDEBF7]

2​

[/td][td][/td][td=bgcolor:#E2EFDA]question 1[/td][td=bgcolor:#E2EFDA]Associate1[/td][td=bgcolor:#E2EFDA]Associate5 [/td][td=bgcolor:#E2EFDA][/td][/tr]

[tr=bgcolor:#FFFFFF][td]Associate2[/td][td]

3​

[/td][td]

1​

[/td][td]

1​

[/td][td][/td][td]question 2[/td][td]Associate1[/td][td]Associate2[/td][td]Associate5 [/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Associate3[/td][td=bgcolor:#DDEBF7]

2​

[/td][td=bgcolor:#DDEBF7]

2​

[/td][td=bgcolor:#DDEBF7]

2​

[/td][td][/td][td=bgcolor:#E2EFDA]question3[/td][td=bgcolor:#E2EFDA]Associate2[/td][td=bgcolor:#E2EFDA]Associate5 [/td][td=bgcolor:#E2EFDA][/td][/tr]

[tr=bgcolor:#FFFFFF][td]Associate4[/td][td]

3​

[/td][td]

3​

[/td][td]

3​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Associate5 [/td][td=bgcolor:#DDEBF7]

1​

[/td][td=bgcolor:#DDEBF7]

1​

[/td][td=bgcolor:#DDEBF7]

1​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

[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