LinkBack Thread Tools Search this Thread Display Modes
Prev Previous Post   Next Post Next
  #1   Report Post  
Posted to microsoft.public.excel.programming
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
 
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How can I find all possible combinations of words? Yvonne Excel Worksheet Functions 4 June 6th 08 01:19 AM
formula to find all possible combinations maijiuli Excel Worksheet Functions 2 July 12th 07 10:22 PM
how can i find all possible combinations nomi Excel Discussion (Misc queries) 3 February 13th 06 12:33 PM
find all combinations of cells that add up to certain number AD Excel Programming 14 November 18th 05 01:32 PM
Find combinations of numbers to equal a value jubu Excel Worksheet Functions 1 March 18th 05 12:46 PM


All times are GMT +1. The time now is 09:00 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"