Consolidating columns into one summary column

You could use a small UDF:

Public Function Summary(rngMatch As Range, rngHeaders As Range)
Dim c As Range, s() As String, i As Integer, j As Integer
j = 0
For i = 1 To rngMatch.Cells.Count
    If Not rngMatch.Cells(i).Value = "" Then
        ReDim Preserve s(j)
        s(j) = Left(rngHeaders.Cells(i).Value, 1)
        j = j + 1
    End If
Next i
Summary = Join(s, ",")
End Function

Use as:

	I
1	SUMMARY
2	=summary($B2:$H2,$B$1:$H$1)
3	=summary($B3:$H3,$B$1:$H$1)
4	=summary($B4:$H4,$B$1:$H$1)
5	=summary($B5:$H5,$B$1:$H$1)
6	=summary($B6:$H6,$B$1:$H$1)
7	=summary($B7:$H7,$B$1:$H$1)
8	=summary($B8:$H8,$B$1:$H$1)
9	=summary($B9:$H9,$B$1:$H$1)
10	=summary($B10:$H10,$B$1:$H$1)
11	=summary($B11:$H11,$B$1:$H$1)
12	=summary($B12:$H12,$B$1:$H$1)

which returns:

	I
1	SUMMARY
2	E,H,B
3	C,H,S
4	C,L,T
5	E,H,B
6	E,C,L,T
7	C,S,B
8	E,L,B,T
9	E,S,B
10	C,L,S,T
11	C,L
12	H,S

Hi,

Here's another approach which doesn't use macros.

How to install your UDF code

1. Copy the Excel VBA code as posted in post #2
2. Select the workbook in which you want to store the Excel VBA code
3. Press Alt+F11 to open the Visual Basic Editor
4. Choose Insert > Module
5. Edit > Paste the macro into the module that appeared
6. Close the VBEditor
7. Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)

You can then use the SUMMARY formula anywhere in this workbook:

Formula:

=summary($B2:$H2,$B$1:$H$1)

The formula will check all cells in the first specified range (rngMatch), and for each non-blank cell will return the first characters of the matching cells in the second specified range (rngHeaders).