View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.programming
Bruno Campanini[_2_] Bruno Campanini[_2_] is offline
external usenet poster
 
Posts: 74
Default 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