Consolidating columns into one summary column

You could use a small UDF:

Please Login or Register  to view this content.

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:

Please Login or Register  to view this content.

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).