Return the value of a header row if cell contains data

[DctheDC]

Hi all

I have a list of names in a column A and a list of categories Cols B-F

Each person can be tagged to several categories with the value of either 0 1 2 3 or 4

I am after a way to create a separate summary sheet that will tell me the person’s name in one cell (which I can do with =Sheet1!A2 ) and a string of text delimited by commas showing all the categories they have been tagged too regardless of the value used.

Example Table

[TABLE="class: grid, width: 50"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]Bob[/TD]
[TD][/TD]
[TD]0[/TD]
[TD]3[/TD]
[TD][/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]Peter[/TD]
[TD][/TD]
[TD][/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Frank[/TD]
[TD]4[/TD]
[TD]2[/TD]
[TD]0[/TD]
[TD][/TD]
[TD]3[/TD]
[/TR]
</tbody>[/TABLE]

Result:

[TABLE="width: 50"]
<tbody>[TR]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[/TR]
[TR]
[TD]Bob[/TD]
[TD]2,3,5[/TD]
[/TR]
[TR]
[TD]Peter[/TD]
[TD]3,4,5[/TD]
[/TR]
[TR]
[TD]Frank[/TD]
[TD]1,2,3,5[/TD]
[/TR]
</tbody>[/TABLE]


My full list of data has over 150 people and 160 different categories

Any Ideas?

Thanks

 

[Aladin Akyurek]

In K2 control+shift+enter, not just enter, and copy down:

=TEXTJOIN(",",TRUE,IF(ISNUMBER(B2:F2),$B$1:$F$1,""))

 

[JLGWhiz]

using VBA, puts data on second sheet

Sub t()
Dim c As Range, i As Long, lc As Long, ssh As Worksheet, sh As Worksheet, cat As String
Set ssh = ActiveSheet
Set sh = Sheets(2) 'Edit sheet name
    With ssh
        For Each c In .Range("A2", .Cells(Rows.Count, 1).End(xlUp))
            If c <> "" Then
                lc = .Cells(c.Row, Columns.Count).End(xlToLeft).Column
                    For i = 2 To lc
                        If .Cells(c.Row, i) <> "" Then
                            If cat = "" Then
                                cat = .Cells(1, i).Value
                            Else
                                cat = cat & ", " & .Cells(1, i).Value
                            End If
                        End If
                    Next
                sh.Cells(Rows.Count, 1).End(xlUp)(2) = c.Value
                sh.Cells(Rows.Count, 1).End(xlUp).Offset(, 1) = cat
                cat = ""
            End If
        Next
    End With
End Sub

 

[DctheDC]

Thanks a lot for your response Aladin, is there a way to have the Summary on another sheet? I tried prefixing the formula with =Sheet1! but it would not work

Thanks JLGWhiz, I liked the VBA option too

Cheers

 

[Aladin Akyurek]

Does the Summary sheet have the names also? If so, where?

 

[DctheDC]

The names will be in cell A1, with the text string in cell B1, but I can pull them in using a simple =Sheet1!A2 formula. They need to be in separate cells as hopefully I will be 'Counting' the digits from the cell B1

 

[Aladin Akyurek]

The names will be in cell A1, with the text string in cell B1, but I can pull them in using a simple =Sheet1!A2 formula. They need to be in separate cells as hopefully I will be 'Counting' the digits from the cell B1

If this is in Sheet1:

Book1
	A	B	C	D	E	F
1		1	2	3	4	5
2	Bob		0	3		2
3	Peter			3	4	0
4	Frank	4	2	0		3
Sheet1

do we have in Summary something like this:

Book1
	A
1
2	Bob
3	Peter
4	Frank
5
Summary

 

[DctheDC]

The Summary sheet is correct and the text string will go into Column B

Thanks

 

[Aladin Akyurek]

The Summary sheet is correct and the text string will go into Column B

Thanks

In B2 control+shift+enter, not just enter, and copy down:

=TEXTJOIN(",",TRUE,IF(ISNUMBER(Sheet1!B2:F2),Sheet1!$B$1:$F$1,""))

 

[DctheDC]

Thanks Aladin,

That's perfect and does what I want

Thanks for your help!