I recently had an e-mail request asking for a method of finding unique columns in an Excel table consisting of either blank cells or an entry of 1.  An easy way to do this would be to convert the columns to a binary value, then use the Countif function to count how many copies there were of each column.  Excel does not provide a convenient way to combine the contents of many cells into a single value, but a simple User Defined Function (UDF) to perform this task can be downloaded from Binary Functions.xls.

The Binary Functions spreadsheet uses code from: Visual Basic Code Examples , which provides code to convert binary to and from decimal, octal and hex values.  I have added code to create a binary value or text string from an Excel column (or row) of values.  Output from this spreadsheet is shown in the screen shot below:

Note that:

• Excel has functions to convert between binary and decimal, but these are limited to a very small maximum value.
• The BinRangeToString function assigns each cell a value of 0 for cells containing 0 or empty cells, or 1 for anything else.
• The BinRangeToDec function returns a floating point decimal value, and will not be reliable for columns with more that 48 rows.
• The TextString function returns a concatenated string of the contents of each cell.

The TextString function was previously presented at: Stringing more than two words together.  The TextString function has now been modified to display values formatted as “general” correctly, and to optionally allow blank cells to either be ignored, or included in the returned string.  The revised file may be downloaded from TextString.xls