creating unique combinations

The enclosed file contains a script that finds matching groups of numbers, however, I would like to edit this program to do the opposite.

The end result would be unique groups of numbers, in other words, number sets that appear in only one sheet would be separated in a their own sheet. The script is enclosed because I believe it may just need editing to do what is intended here.
Copy-of-EXX-130918-Match-Combina.xlsb
Pedrov664Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

FaustulusCommented:
By its nature, your existing script would produce about 388 sheets of unique combinations. Since the total number of combinations exceeds 1,048,000 they can't be all combined into one sheet unless you would accept several sets of combinations in one row. So, the question is how you would like the result to be presented.
0
Pedrov664Author Commented:
The result should be presented in its own separate sheet which the script makes. Actually the existing script does not have to be used and if you run the script you will find that it ends up with creating a separate single sheet for the results. However in the mean time it creates 'other' sheets in which to hold data so as to not alter the originals.

But like I said this script does not have to be used, if there is a better way then I am open to it.
0
FaustulusCommented:
There is no way you can have 1.6 million rows in one sheet.
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Pedrov664Author Commented:
And I believe you right. The idea is to find number groups that do not appear in any other sheets not to combine sheets. In other words there should less not more groups of numbers
0
FaustulusCommented:
We aren't on the same page. Take a look at the attached solution. Run the procedure 'ListUniqueCombinations'. It will produce a result within a little over 2 minutes on my laptop, that is a little slower than the previous extraction of duplicates. I think the difference results mostly from the list being longer.
In fact, the list is a lot shorter than I had anticipated. I had thought that with some 400,000 duplicates out of 1.6 million combinations more than a million should remain which I wouldn't be able to put into a single list. Actually, there are only 427,000 unique values, leaving you with some 800,000 duplicate duplicates.
Anyway, the 427,000 unique values do fit on a single tab. However, if you run other numbers and the result ever exceeds 1,048,576 rows neither Excel nor my program will be able to handle it.
EXX-130922-Unique-Combinations.xlsb
0
Pedrov664Author Commented:
Faustulus,

At first glance the script seems to do the opposite of what I would like in this case. Therefore, let me explain. The script would compare the first set in numbers_1 with numbers_2 looking for unique combinations, that is combinations that appear in numbers_1 but not in any other sheet.

Thus it would do as follows:

compare the first set of numbers (2,6,9,11,13) with those in sheet 2 and it finds that numbers_2 also contains the same set of numbers therefore that set is NOT put into the 'report' sheet.

It then goes to the next set in numbers_1 (2,6,9,11,14) with those in sheet 2 and it finds that numbers_2 also contains the same set of numbers therefore that set is NOT put into the 'report' sheet.

and on and on unti it finds a set of numbers in numbers_1 that does NOT appear in any other sheet. For instance, (2,6,9,11,42) does not appear in numbers_2 or numbers_3 but it appears in numbers_4 and thus it also would not be included in the report sheet.

Therefore, the unique combinations in the report sheet would be found in one sheet and one sheet only. If any other sheet contains that same set of five numbers it is not included in the report sheet.

I do hope that helps to clarify what it is I am looking to accomplish.
0
FaustulusCommented:
It's not the opposite. In database termology "unique" means nett of duplicates. Now I understand that you want a list of combinations that have no duplicates. That is even easier based on he original script. I'll do it by the end of the day.
0
FaustulusCommented:
Here we go. You are already familiar with the program. The name of the procedure to run is now 'ListUnmatched'. Just let me remind you that no searching is done. The result is achieved entirely by sorting and comparing.
EXX-130924-Unmatched-Combination.xlsb
0
Pedrov664Author Commented:
Q. Because of all the private subroutines, should I not add scripts to this file or is it possible to run other scripts from the same page?
0
FaustulusCommented:
The private declarations act to limit the availability of the procedures to other procedures within the same module. They can't be called from another module or the keyboard. They don't interfere with any other code you might put in the same module. However, this isn't my recommendation.
In an ideal world you would add functionality to a workbook by adding a code module and remove the functionality by removing the module. This is exactly what you have. Why should you wilfully destroy a perfect setup?
I recommend to put other code in other modules, preferably each subject in its own module - appropriately named - and don't worry about having too many modules. There is no such thing.
BTW, I could help you combine code for Matching and Unmatched into one module. The code is 90% identical.
0
Pedrov664Author Commented:
Excellent Idea. I will create another request, since I believe you have earned your points here. Please look for the other request so you can earn those points as well.

P.S. I will accept this one as soon as I have time to process the script for bugs. It seems my pc runs slower than yours. So i have to dedicate all of it while I run these scripts.
0
FaustulusCommented:
Best way to do continuations is to post a link to the new thread in the preceding one.
0
Pedrov664Author Commented:
0
[ fanpages ]IT Services ConsultantCommented:
Errr... yeah, I know! :)
0
Pedrov664Author Commented:
Faustulus,

The enclosed file was used to test the script and it took over 20 minutes and did not finish. I know my computer is slow but it only took 5 minutes with the original file. Please advise.
CreateCominations-1-debug.xlsb
0
FaustulusCommented:
I have seen this kind of behaviour when an array fails to write to a worksheet. In fact the program gets hung. The failure may be caused by an array that is too large or too small. I found that the command fails if the array holds a single cell only. In general, the code makes special provisions for such a case, but I've been stingy with the required test because it has to be repeated so many times.
This error, if it is what I described above, wouldn't occur with the data I had because I would have found it. I take it that the data n your test Wb are different.
These days I'm rather squeezed for time. I'll look into the problem ASAP.
0
Pedrov664Author Commented:
Faustulus,

You're right it is a different set of data. I used it to test under different conditions.

I completely understand your time constraints, since I also have little time for R & R myself. As they say, all work no play. At least for now.

Best Regards,
Pedro
0
FaustulusCommented:
I found an error affecting sheets with a single combination. I also found a referencing error which, in effect, made it impossible to change the value of NwsFirstData. Both errors were resolved.
Your new set of data has nearly twice as many groups as the previous. Sorting all these sheets seems to have overwhelmed Excel. I have re-written the sorting code which has the additional bonus of being much faster.
Regarding speed it has been my experience that the program slows down the more often you use it. Clean out the workbook the way I described earlier. It would be a good idea fo structure your work flow in such a way as to be able to work from a template so that you have a fresh workbook for each job.
The revised code is posted at this link since it also combines the Matched and Unmatched versions of itself.
[ http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/Q_28249495.html ]
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Pedrov664Author Commented:
Thank you for all your hard work.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VB Script

From novice to tech pro — start learning today.