Search code examples
excelexcel-formulaexcel-2010excel-2007vba

Excel - Counting number of occurrence in a bit string large range

I have 13-bit strings data in a column, I want to count the number of cells with unique combination of bits. The total number of cells in that column is 209066. I am stuck on how to make it possible. Since, 2^13 = 8192 cells, so that's quite hectic too , to find the unique combinations statistically, and then write in the adjacent cell, how much times that value occurs.

13 Bit String   Occurrence
1001111101011   
0011111010110   
0111110101101   
1111101011011   
1111010110110   
1110101101100   
1101011011000   
1010110110000   
0101101100001   
1011011000010   
0110110000101   
1101100001011   
1011000010111   
0110000101110   
1100001011101   
1000010111011   
0000101110111   
0001011101110   
0010111011100   
0101110111001   
1011101110011   
0111011100111   
1110111001110   
1101110011101   
1011100111010   
0111001110101   
1110011101011   
1100111010110   
1001110101100   
0011101011001   
0111010110011   
1110101100110   
1101011001100   
1010110011000   
0101100110000   
1011001100001   
0110011000011   
1100110000110   
1001100001100   
0011000011001   
0110000110011   
1100001100110
.......
[continued upto cell 209066]
Solution
    1. Highlight the column with the bits
    2. Data--> Sort&Filter --> Advanced
    3. Enable "Copy to another location" and "Unique records only" List
    4. Range should be where all the bits are (already highlighted)
    5. Leave Criteria Range blank
    6. Copy to should be a blank cell, in the same worksheet, with nothing in the rows below it.
    7. Assume bits are in column A, Unique in column D. Put this formula in E1 and fill down along the unique values =COUNTIF($A:$A,"="&D1) `