Please help with MS Excel

Blakespops

victor2 wrote: »

Where do you get that number from? It's way too small unless you're already applying some restrictions.



Surely there are more than 2 consecutive numbers in that, which is one of your rules?

Anyway, still fun to try.
I'd just let your friend go on winning. Meanwhile, I'll just buy lottery tickets with 1,2,3,4,5,6 on them. It's never won, so must be due to come up soon according to the laws of statistics!:D

As stated it is one set of numbers 0-36, not six sets. so any six from 37 gives 2324784 before filters. I am thinking you are giving each of the six people their own set of 37 not sharing the filtered numbers between the six.

now it is not about what he does it is about working out how to apply the filters. my wife says I am like a dog with a bone once I am challanged with something. may be quicker to just have it put all 2324784 combinations and then do filters manually.

Also you are correct I did make a mistake on what would be first set of numbers.

victor2

Blakespops wrote: »

so any six from 37 gives 2324784 before filters.

I agree now - I wasn't allowing for the same set of numbers in a different sequence to be considered the same.:o

edit:
Played around with my filtering routine and it has just found the 246,478th valid set, which is 1 10 21 24 29 34, so a few more to go through yet since it started with the left digit at 0!

Blakespops

victor2 wrote: »

I agree now - I wasn't allowing for the same set of numbers in a different sequence to be considered the same.:o

edit:
Played around with my filtering routine and it has just found the 246,478th valid set, which is 1 10 21 24 29 34, so a few more to go through yet since it started with the left digit at 0!

I am of the opinion that anyone who comes up with a program that can be run with excel that allows you to do any number of filters at the same time plus even give the potion of not only getting the results of the filtering but swapping between the filtered out items so you can see which items were removed due to each filter. using this problem as example maybe say starting in A1 - A37 you put the numbers then B gives your results, C shows items removed by no more than 2 consecutive numbers , D shows no more than 3 from any 10 etc.

victor2

I get what you're saying, but in reality you're dealing with huge numbers of combinations, which is how these sorts of gambling survive.
I'm still playing with my routines since it is a fun challenge. The logic I have used probably could be applied in VBA, but listing filtered results is going to be beyond the capacity of excel. If I don't restrict the range of combinations it tries, I can go and have dinner while it is processing!

Blakespops

victor2 wrote: »

I get what you're saying, but in reality you're dealing with huge numbers of combinations, which is how these sorts of gambling survive.
I'm still playing with my routines since it is a fun challenge. The logic I have used probably could be applied in VBA, but listing filtered results is going to be beyond the capacity of excel. If I don't restrict the range of combinations it tries, I can go and have dinner while it is processing!

I would have given up long before now had it not been a fun thing to try. Part of me is thinking my friend is just full of BS but hey who am I to pass judgement on how he chooses to live or spend his money.

I am looking to see if doing something in visual basic 6 would work better.

victor2

My routine just fell over, having found 1,124,103 combinations that should meet the criteria. The last was 22 24 25 33 35 36, so it hasn't finished yet, although the further on you get, the fewer there are. Think I could improve it and not use a database if I'm only counting how many combinations there are.
The logic just to generate unique combinations for each pass was quite a challenge on its own.
Think I'll just keep buying lottery tickets.:)

Edit:
Ran it to completion this time and got the same result as above, except it said there were 1,124,104 combinations, so maybe it crashed before upon completion. Thought there would be more combinations after the one reported, but I guess not. Takes a little over an hour for my humble desktop to figure them out - it's going for a lie down now!

Blakespops

Got it sorted by using =SUMPRODUCT(--(MOD($A2:$F2,2)=1))>3 for odd, =SUMPRODUCT(--(MOD($A2:$F2,2)=0))>3 for even,=SUMPRODUCT(--(B2:E2-A2:D2=1),--(C2:F2-B2:E2=1))>0 for 2 consecutive. now just need to work one out that will sort out the amount within any set of 10. all I did is filter out the true items leaving me with the ones that are not in the ranges above.

victor2

How are you going to list all the combinations in Excel? Even the filtered list is more than Excel can handle.
Neat formulae though!