Can I retrieve the column values?

[JoanneBV]

Hello I am hoping I could ask for some help

I am looking to see if I can retrieve the values of a column indicated in a row.

The research is where I have spoken to 200 people who are specialists and I have asked them to choose 3-4 factors out of about 50. Each row is a person and I have listed the factors in the column headings (going across) and simply put an x in the column of the factor they chose.

This has allowed me to count which factors are the most popular but it would be great if I could use a formula (maybe a hlookup or indexing) if at the end of each row I can show what each persons choices were.

So for example if at the end of row 3 I can see that they chose temporary intrusive device and co-morbidity for example would be great

[/URL][/IMG]

Any help much appreciated

Cheers

 

[Dendro]

Hi,

As i can see on the picture you table ends in column R, so we will put the formula(output) on column S. It's not an easy formula as we will use array-formula's, but you can use evaluate formula to understand what it does:

=INDEX($F$1:$R$4,1,SMALL(IF($F2:$R2="x",COLUMN($F$1:$R$4)-5),COLUMN()-18))

1.Put this formula in cell "S2" and press ctrl+shift+enter<-very important not to just hit enter!!!
2.copy to the right to cell "T2" and "U2"
3. copy down for each column S,T and U
Let me know if it works

 

[Gingertrees]

If you are hoping for just a summary of the choices, like "Chemo last 3 mos, Age Over 65, Permanent Devices" in column Z, you can use this VBA formula to achieve it:

NOTE: for the values of i and j, customize this to your sheet. I just used the values in your picture. For example, if your actual sheet goes down to row 125, change i to "For i = 2 to 125".

Sub summarizechoices()
Dim v As Variant
Dim i As Integer
Dim j As Integer
For i = 2 To 12 '//row numbers, to "code 11" in example
    For j = 6 To 18 '//column numbers, out to R in example
        If Cells(i, j) <> "" Then
            If Len(v) = 0 Then
                v = Cells(1, j).Value
            Else: v = v & ", " & Cells(1, j).Value
            End If
        End If
    Next j
    Cells(i, 26).Value = v '//puts the summary in column Z for the row in question
    v = ""
Next i
End Sub