Find Combinations
I have a list of integers (myrange)
ROW Numbers
1 1,2,3,8,10,14
2 21,26,34,45,60,66
3 8,18,19,23,45,46
4 21,36,38,41,48,49
n .................
Each row is ordered LeftToRight, ASC; n is in the order of 7000.
I have 6 numbers (ordered the same way)
21,26,34,45,48,60 (myrow)
I write all the combinations I can get from
myrow, they are 2^6 = 64 but excluding C6,0 they are 63:
6,1 = 6
6,2 = 15
6,3 = 20
6,4 = 15
6,5 = 6
6,6 = 1
Now I want to see how many times EACH combination out of the 63'
appears in myrange, and in what row.
The solution for the example is:
C6,1 = 8
C6,2 = 11
C6,3 = 10
C6,4 = 5
C6,5 = 1
C6,6 = 0
Somebody tried scanning any row of myrange 63 times but
it is a very much time consuming way (more than 1 hr).
I tried using queries and got a good time of some 3 minutes.
Any good idea using VBA without any support of queries?
(using queries my Excel 2013/64 bit is unstable and
crashes often).
Bruno
|