0

I have an excel file with 272,000 lines of data across 5 columns. The data looks like this:

A   B   C   D   E
1   1   1   1   1
1   0   1   0   1
1   1   1   1   1
1   0   0   0   0
1   0   1   0   1
1   0   1   1   1
1   1   1   1   1
1   0   1   0   1
1   0   1   1   1
1   1   1   1   1
1   1   1   0   1
1   0   1   1   1
1   0   0   1   1
0   1   0   0   0
1   0   0   0   1
0   0   0   0   1
1   1   1   1   1
1   1   1   0   1
0   1   0   0   0
1   0   0   0   0
1   1   1   0   1
1   1   1   1   0
1   0   0   0   0
1   1   1   0   1
0   0   0   0   1
1   0   0   0   1
1   1   1   1   1
1   1   0   1   1
1   1   1   1   1

I know what the unique combinations are:

A
AE
AB
BE
B
AC
ABC
BC
C
ACE
ABCE
BCE
CE
ACDE
ABCDE
BCDE
CDE
ADE
ABDE
BDE
DE
AD
ABD
BD
D

How do I get a count of how many times each combination appears? I tried using a nested 'IF' function and also the count if function. What can I do in this situation.

2
  • 1
    Sorry if i have understood your question incorrectly. when you say "unique combinations" as "A", "AE" - what do u mean? Is it a row that you want to see if it is unique? Can we convert each row to a hex value and do countif? Or am I altogether wrong?
    – Prasanna
    Jul 24, 2014 at 11:14
  • Hi Prasanna, When I say "A" or "AE", I am referring to the columns A and E and if I see a value of 1 in them, If the first row has a "1" in Column A and 0's in the other column it counts as "A". If columns A and E have a value of 1 in them and the others are zeros, it counts as "AE". I hope this clears your confusion. Jul 25, 2014 at 2:03

2 Answers 2

0

There are several ways you could answer this, I think an easy way to get the combinations is:

Make a backup of your current data just incase, since all your A,B,C,D,etc are always in the same column, you could basically convert all these numbers to the letters (if 0 then have ""). And then have a cell that basically adds all the strings of letters together, so you then have the combinations.

If can then use the COUNTIF function to get a total.

So to convert from numbers to letters, do this for every letter, use an IF function, example: =IF(A1=1,"A","")

When you have your columns in letters, you can use the =CONCATENATE to get the combinations, if your data of letters is is Column A, B, C, D: =CONCATENATE(A1,B1,C1,D1)

Finally, use the COUNTIF function to 'count' the unique combinations that exist in the range we have created with the CONCATENATE function.

0
0

I have worked out a way to do it. Please find the sample file in the file link here.
Step 1: What I have actually done is worked out the 32 possible combinations.
Step 2: Concatenate the binary into one string.
Step 3: Convert binary to Hex - just for reference. In case if you wanted to know that all the combinations that you made while entering those ones and zero's are unique.
Step 4: Name the each binary string - as you had described in the comments above in the column called " Notation".
Step 5: Implement pivot table to count the unique values as you wanted.

Quick look of the table

All you need to do, is start from "Step 3". For the notation do a VLOOKUPfrom my file and assign the notation as it is.
I hope this solves this problem.

1
  • Thanks for your answer but I was able to work this out using the trick provided by SP-15. I also figured out how to this stuff using a few simple "awk" commands. Aug 6, 2014 at 2:08

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .