Lexicographic Index & Sums

[PAB]

Hi Alex,

So I guess now, to reduce the DN's down it's just a matter of Reverse Engineering the Module with more "For/Next" loops and "MyArray()"s, and of course, reducing some code also.

Exactly.

A thought occurred to me late last night, right at the edge of sleep!
In Column B "Excluding DN Numbers" 1-49 is Generated because of users input at E2 "Total Numbers Drawn From=".
Do you know of a formula/coding method that would allow 1, & or 2, Decades to be removed from the calculation pool?
I.E. No teens or say no 30's.

The module would still run the same as it does now, Just no Teens or 30's would be used to calculate out the "Combination Sum Total".

Have a go at adjusting the Formula with an If...Then statement(s) or setting up a VBA Function that you can apply an If...Then statement(s) to.
It will probably be best to get this sorted BEFORE Reverse Engineering the Module. This way it will make it a lot easier to create the other FOUR Modules for DN1, DN2, DN3, & DN4.

I will have a think about this and maybe do something on it later or tomorrow.

Keep up the good work .

BTW, the Excluding DN Numbers is an Array formula and therefore needs to be entered using Ctrl+Shift+Enter before it is copied down.
You also need to reset the calculation method to Automatic!
To get the colours the same, HIGHLIGHT the cells D6:E6 and drag down, then change the DN4 to DN5.

Regards,
PAB


-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-
12:45, restate my assumptions.
Mathematics is the language of nature.
Everything around us can be represented and understood through numbers.
If you graph the numbers of any system, patterns emerge. Therefore, there are patterns everywhere in nature.

 

[Alexafjb]

Good Morning PAB

Not starting out well! LOL
I've been bugged with a "Copy & Paste" issue that all of a sudden Excel 2007 is starting throw out at me! Its one of thous issues that has a multitude of possible causes. through my goggling of the net I've encountered way too many to list. So through attrition I will have to go! Usually, I just go to MS Tech support pages and noodle out the issues, but even there, it goes from a registry issue to a complete reinstall of Office, and I'm not going to go there! Just giving you a heads up. If you can think of anything, that might apply to this let me know, Please! Either here on the forum or threw mail, both will let me know threw my cell phone if I'm not near my computer.

It will probably be best to get this sorted BEFORE Reverse Engineering the Module. This way it will make it a lot easier to create the other FOUR Modules for DN1, DN2, DN3, & DN4.

I will have a think about this and maybe do something on it later or tomorrow.

I was reading yesterday on arrays, and came a cross a thought
on the "Decades Removal" the Formula might use " =COUNTIFS( , , , ) Not sure how just yet, but it was one of those things that jumped of the page at me, and got me thinking! Problem is it's only works for Excel 2007 and forward.

Anyways. will keep you up to speed on my progress.

Cheers.
Alex.

 

[PAB]

Afternoon Alex,

I've been bugged with a "Copy & Paste" issue that all of a sudden Excel 2007 is starting throw out at me! Its one of thous issues that has a multitude of possible causes. through my goggling of the net I've encountered way too many to list. So through attrition I will have to go! Usually, I just go to MS Tech support pages and noodle out the issues, but even there, it goes from a registry issue to a complete reinstall of Office, and I'm not going to go there! Just giving you a heads up. If you can think of anything, that might apply to this let me know, Please!

What are you trying to Copy & Paste, what is the issue, if you could explain in a bit more detail I might be able to help!

I was reading yesterday on arrays, and came a cross a thought on the "Decades Removal" the Formula might use " =COUNTIFS( , , , ) Not sure how just yet, but it was one of those things that jumped of the page at me, and got me thinking! Problem is it's only works for Excel 2007 and forward.

I don't think that the COUNTIFS will surfice for this particular case scenario.

Regards,
PAB


-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-
12:45, restate my assumptions.
Mathematics is the language of nature.
Everything around us can be represented and understood through numbers.
If you graph the numbers of any system, patterns emerge. Therefore, there are patterns everywhere in nature.

 

[Alexafjb]

Hi PAB

Happy days, shook out all the excel bugs on my end!
Once again THX for the help on the Array kurfluf-all.

So now on to working out how to get 1 or 2 decades removed from the "Excluding DN Numbers" Array"
I'm almost intimidated by this task, after my last problem with this array!
Can an Array be Named?
I some how keep thinking that it might be better manipulated that way.
Will see what I can Learn about "IF/Then" statement.

Cheers.
Alex.

 

[PAB]

Hi Alex,

Happy days, shook out all the excel bugs on my end!
Once again THX for the help on the Array kurfluf-all.

EXCELLENT Work!

So now on to working out how to get 1 or 2 decades removed from the "Excluding DN Numbers" Array".
I'm almost intimidated by this task, after my last problem with this array!
Can an Array be Named?
I some how keep thinking that it might be better manipulated that way.

That ARRAY formula used in column B already has a Defined Named Range reference incorporated in it!

Regards,
PAB


-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-
12:45, restate my assumptions.
Mathematics is the language of nature.
Everything around us can be represented and understood through numbers.
If you graph the numbers of any system, patterns emerge. Therefore, there are patterns everywhere in nature.

 

[Alexafjb]

Good Morning PAB

I've been thinking about how I screwed up the program by saving it.
I've noted that any Input by the user is saved also. How would you go about making a "Reset Button"?

I Understand this:

With Application
.ScreenUpdating = False: .Calculation = xlCalculationManual: .DisplayAlerts = False
End With
Range("G4:R" & Range("G" & Rows.Count).End(xlUp).Row).ClearContents

With Application
.DisplayAlerts = True: .Calculation = xlCalculationAutomatic: .ScreenUpdating = True
End With

I understand that:

I also understand that all the variables have to be cleared also, and the screen cleared.
I guess what I'm asking is, it put as the last statement before "End Sub"?
Is it just an "If/Then" Loop?

Quote:
Originally Posted by Alexafjb
So now on to working out how to get 1 or 2 decades removed from the "Excluding DN Numbers" Array".
I'm almost intimidated by this task, after my last problem with this array!
Can an Array be Named?
I some how keep thinking that it might be better manipulated that way.

That ARRAY formula used in column B already has a Defined Named Range reference incorporated in it!

Apologies: It's named "MyArray" Just could not see the forest for the tree's! the other day.

Cheers.
Alex.

 

[PAB]

Morning Alex,

I've been thinking about how I screwed up the program by saving it.
I've noted that any Input by the user is saved also. How would you go about making a "Reset Button"?

Yes, ANY changes either within the Excel WorkSheet OR the VBA code will be saved. Perhaps, when you save the file you could add at the end of the file name something like v1.00, then next time as v1.10 etc. That way if you screw anything up you can go back to the previous working version and start again!

I also understand that all the variables have to be cleared also, and the screen cleared.
I guess what I'm asking is, it put as the last statement before "End Sub"?

If I understand you correctly, yes.

Is it just an "If/Then" Loop?

Sorry, don't understand this question!

Apologies: It's named "MyArray" Just could not see the forest for the tree's! the other day.

Sorry, don't understand this bit!

Can you please clarify that the numbers below are what numbers you are regarding as the DECADES?

Decade 0 = 01-09
Decade 1 = 10-19
Decade 2 = 20-29
Decade 3 = 30-39
Decade 4 = 40-49

Regards,
PAB


-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-
12:45, restate my assumptions.
Mathematics is the language of nature.
Everything around us can be represented and understood through numbers.
If you graph the numbers of any system, patterns emerge. Therefore, there are patterns everywhere in nature.

 

[Alexafjb]

Good Morning PAB

Quote:
Originally Posted by Alexafjb
Is it just an "If/Then" Loop?

Sorry, don't understand this question!

Sorry about that, I was referring to What the Reset Button make up would be to clear the program of Variables that the user input.
i.e if user hits rest button it clears E4 to E8 and the screen.

Quote:
Originally Posted by Alexafjb
Apologies: It's named "MyArray" Just could not see the forest for the tree's! the other day.

Sorry, don't understand this bit!

That was referring to my question the other day about naming Array's. When I was looking at the code I did not see that the Array was named. and I never should have asked that question.

Can you please clarify that the numbers below are what numbers you are regarding as the DECADES?

Decade 0 = 01-09
Decade 1 = 10-19
Decade 2 = 20-29
Decade 3 = 30-39
Decade 4 = 40-49

Yes that is correct, I seen it somewhere else in on the board, and thought it was a good way to describe it.

Cheers.
Alex.

 

[PAB]

Hi Alex,

Sorry about that, I was referring to what the Reset Button make up would be to clear the program of Variables that the user input.
i.e. If the user hits the rest button it clears E4 to E8 and the screen.

OK.
Put this code in Another Module:-

Option Explicit
Option Base 1

Sub Clear_Criteria_PAB()
With Application
.ScreenUpdating = False: .Calculation = xlCalculationManual: .DisplayAlerts = False
End With
Range("E2:E8").ClearContents
Range("E2").Select
With Application
.DisplayAlerts = True: .Calculation = xlCalculationAutomatic: .ScreenUpdating = True
End With
End Sub

That was referring to my question the other day about naming Array's. When I was looking at the code I did not see that the Array was named. and I never should have asked that question.

I wasn't talking about the code, I was talking about the Defined Named Range within the Array Formula in cells B4:B52.

Yes that is correct, I seen it somewhere else in on the board, and thought it was a good way to describe it.

Great, I will put something together for you.
What sort of Decade criteria will you be using?
For example, One Decade, Two Decades etc, and will they vary from Decade One, Decade One & Three etc?

Regards,
PAB


-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-
12:45, restate my assumptions.
Mathematics is the language of nature.
Everything around us can be represented and understood through numbers.
If you graph the numbers of any system, patterns emerge. Therefore, there are patterns everywhere in nature.

 

[Alexafjb]

Hi PAB

Decade 0 = 01-09
Decade 1 = 10-19
Decade 2 = 20-29
Decade 3 = 30-39
Decade 4 = 40-49

Would Work Fabulously!

Cheers.
Alex.

 

[PAB]

Hi Alex,

To save confusion it will probably be best for you if we created Named Formulas that can be used in the Array Formulas in column B.

Therefore please do the following:-

(1) DELETE the existing Named Formula Numbers.

(2a) Create a Named Formula called DN with refers to as =ROW(INDIRECT("1:"&'DN Combination Generator'!$E$2)).
(2b) Create a Named Formula called Decade1 with refers to as =ROW(INDIRECT("01:09")).
(2c) Create a Named Formula called Decade2 with refers to as =ROW(INDIRECT("10:19")).
(2d) Create a Named Formula called Decade3 with refers to as =ROW(INDIRECT("20:29")).
(2e) Create a Named Formula called Decade4 with refers to as =ROW(INDIRECT("30:39")).
(2f) Create a Named Formula called Decade5 with refers to as =ROW(INDIRECT("40:49")).

(3) For the Array Formula purely for Excluding DN Numbers ONLY use this Array Formula in cell B4 and copy down:-

=IFERROR(1/(1/SMALL(IF(ISNA(MATCH(DN,E$3:E$7,0)),DN),ROWS(B$4:B4))),"")

(4) For the Array Formula for Excluding DN Numbers AND Decade2 AND Decade4 use this Array Formula in cell B4 and copy down:-

=IFERROR(1/(1/SMALL(IF(ISNA(MATCH(DN,E$3:E$7,0)),IF(ISNA(MATCH(DN,Decade2,0)),IF(ISNA(MATCH(DN,Decade4,0)),DN))),ROWS(B$4:B4))),"")

The Array Formula above EXCLUDES the TWO Decades you originally mentioned.
It should be quite simple for you now to adapt that formula for just ANY ONE DECADE or for ANY TWO DECADES etc.

I hope this helps!
Please let me know how you get on.

EDIT: DELETE ALL EXISTING FORMULAS IN COLUMN "B" FIRST.

Regards,
PAB


-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-
12:45, restate my assumptions.
Mathematics is the language of nature.
Everything around us can be represented and understood through numbers.
If you graph the numbers of any system, patterns emerge. Therefore, there are patterns everywhere in nature.

 

[Alexafjb]

Hi PAB
Thank You Muchly!!!!
Will Get, back to you as soon as I can.

Cheers.
Alex

 

[Alexafjb]

Good Evening PAB

So, I noodled through it!

Deleted "All Formulas in Column B"
Deleted "Numbers" from the "NAME MANAGER"
I created "Named Formula"
DN
Decade1
Decade2
Decade3
Decade4
Decade5
and
Copied and Pasted
=IFERROR(1/(1/SMALL(IF(ISNA(MATCH(DN,E$3:E$7,0)),DN),ROWS(B$4:B4 ))),"")
Down Column B
At this point the Light bulb goes "ON"

Am I right in assuming That I will be create a new program for each of the Decades I wish to use and their corresponding configurations?

Thanks For the Great Work PAB !!!
I'd be Years.. and Years at this!
Now I just have to put it all together, in a little box!

Cheers.
Alex.

 

[PAB]

Hi Alex,

Am I right in assuming that I will need to create a new program for each of the Decades I wish to use and their corresponding configurations?

I don't follow you!
A new program?
Are you referring to the below?

(4) For the Array Formula for Excluding DN Numbers AND Decade2 AND Decade4 use this Array Formula in cell B4 and copy down:-

=IFERROR(1/(1/SMALL(IF(ISNA(MATCH(DN,E$3:E$7,0)),IF(ISNA(MATCH(D N,Decade2,0)),IF(ISNA(MATCH(DN,Decade4,0)),DN))),R OWS(B$4:B4))),"")

The Array Formula above EXCLUDES the TWO Decades you originally mentioned.
It should be quite simple for you now to adapt that formula for just ANY ONE DECADE or for ANY TWO DECADES etc.

If so, there are 30 combinations for FOUR Decades, because using all of them will not produce any results obviously.
The 30 combinations are as follows:-

Decade1
Decade2
Decade3
Decade4
Decade5
Decade1 + Decade2
Decade1 + Decade3
Decade1 + Decade4
Decade1 + Decade5
Decade2 + Decade3
Decade2 + Decade4
Decade2 + Decade5
Decade3 + Decade4
Decade3 + Decade5
Decade4 + Decade5
Decade1 + Decade2 + Decade3
Decade1 + Decade2 + Decade4
Decade1 + Decade2 + Decade5
Decade1 + Decade3 + Decade4
Decade1 + Decade3 + Decade5
Decade1 + Decade4 + Decade5
Decade2 + Decade3 + Decade4
Decade2 + Decade3 + Decade5
Decade2 + Decade4 + Decade5
Decade3 + Decade4 + Decade5
Decade1 + Decade2 + Decade3 + Decade4
Decade1 + Decade2 + Decade3 + Decade5
Decade1 + Decade2 + Decade4 + Decade5
Decade1 + Decade3 + Decade4 + Decade5
Decade2 + Decade3 + Decade4 + Decade5

You could setup 30 Array Formulas, one for each of the above and then just Copy & Paste the relevant formula into column B and then run the code.

I will just say that I have tried to setup the easiest and simplest way using the Array Formula method for you.
Adapting the Array Formula for ANY ONE DECADE is quite easy to do, and to adapt the Array Formula for ANY TWO DECADES just means changing the particular Decade number in the formula I have posted.

I hope this helps!
Please let me know how you get on.

Regards,
PAB


-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-
12:45, restate my assumptions.
Mathematics is the language of nature.
Everything around us can be represented and understood through numbers.
If you graph the numbers of any system, patterns emerge. Therefore, there are patterns everywhere in nature.

 

[Alexafjb]

Good Morning PAB

I know you are trying to keep it simple. I've no problem with what you have come up with.
As you are well aware, I need to practice my skills and develop new technices, this is the perfect Module to do it with!
You are an amazing programmer and if you don't teach this subject, you should!
I've researched the internet enough to know that on most boards, when someone asks a question, the responses are usually a long laundry list of "why", and "have you tried this" and the B.S. & stupid comments from other board members. This has not been the case here! and I appreciate it, very much.
Cuto's to "LT" if he is the owner!!

I will keep you up to date with my progress and come to you when I have questions. With luck the questions won't be many!

Once again Thank You so very much for all your hard work and efforts!

Cheers.
Alex.

 

[PAB]

Hi Alex,

You're welcome .
Thank you for your kind words.

Cuto's to "LT" if he is the owner!

Yes, LT is the Forum owner and does an EXCELLENT job.

As I said in my previous post, there are a lot of scenarios with regard to the Decades. If you are thinking that you will "Mix & Match" them, then it would be advisable to setup a seperate Sub covering ALL the FIVE Decades. This will just involve ONE coding line per Decade, that way you could just ADD or Delete a Decade(s) as and when needed.

Thinking about the way the Decades are currently managed, although I know that this was the way you wanted to go, it is a bit limiting and cumbersome.

Setting up a new Sub would be a nice little excercise for you to develop new skills and techniques. I am not going to leave you cold on this, so here are some hints and suggestions.

The code should include:-
(1) Turn OFF Screen updating etc (you have this technique in your current codes).
(2) Set a Range for the Array Formula to start in.
(3) Don't forget that this is NOT a standard formula, it is an Array Formula, there is a distinct difference. You can use the current Array Formula to put into the code.
(4) Double Quotes """" will play a part within the Array Formula.
(5) Copy the Array Formula down as far as needed.
(6) Turn ON Screen updating etc (you have this technique in your current codes).


Have a go, if you get any problems please let me know!

Regards,
PAB


-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-
12:45, restate my assumptions.
Mathematics is the language of nature.
Everything around us can be represented and understood through numbers.
If you graph the numbers of any system, patterns emerge. Therefore, there are patterns everywhere in nature.

 

[PAB]

Morning Alex,

How are you getting on?

Regards,
PAB


-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-
12:45, restate my assumptions.
Mathematics is the language of nature.
Everything around us can be represented and understood through numbers.
If you graph the numbers of any system, patterns emerge. Therefore, there are patterns everywhere in nature.

 

[Alexafjb]

Good Morning PAB

I've a hell of allot to ingest! LOL
I'm a bit slow right now, what I've done so far is not much!
Of course I did the Naming / or show I say renaming, for the formulas and set it up with the 5DN's that we just finished.
Took my time, and I never introduce any bugs or syntax errors, thank you very much!
While I'm doing this, I keep thinking of ways to improving, i.e. must learn about "Calling other Modules", "Making of Buttons", and stuff like that.
I've printed out most of the correspondence that we have had over the last little while, I review that, cause, I have a mild case of A.D.D. Bright shine thing attract my attention, then I'm off on a Tangent!

If I'm reading between the lines of your previous postings correctly, you believe that I'm going about this the wrong way? or that there is a more efficient way of arriving at the same out come?

I believe that what you have helped with thus far is plenty at the moment, but if you feel there is a different more efficient way of writing this, by all means, please let me know.

Cheers.
Alex.

 

[PAB]

Hi Alex,

While I'm doing this, I keep thinking of ways to improving, i.e. must learn about "Calling other Modules", "Making of Buttons", and stuff like that.

I will just comment on these two points for now.

To Call a Sub all you need to do is insert something like Call Sub NAME in the correct position within the code.

To insert a Button, while in Developer click on the Insert Icon, click the top left button in Form Controls, click somewhere on the WorkSheet and then click the Macro you want to run from the Button.

Regards,
PAB


-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-
12:45, restate my assumptions.
Mathematics is the language of nature.
Everything around us can be represented and understood through numbers.
If you graph the numbers of any system, patterns emerge. Therefore, there are patterns everywhere in nature.

 

[Alexafjb]

Sort

Good Morning PAB

Thanks again for your insight with all the issues that I present!

I've another, in a long list of questions!!!

When you run the program and you get "Results" / ( I call it "Output") and if a module is created to sort out a specific criteria, that "removes" a calculated Result/Output.
Then is that criteria lost?
(Till the program is rerun with the same variables.)

An example would be: If a module was built to act on the combination "Output" to remove combinations from that "Output"...
Lets say I want to remove all combinations that have 3 or more Prime Numbers, or I want to remove all combinations that have 3 or 4 specific numbers in them, Or I want only combinations that have 2 number in sequential order.

I'm guessing that once it's acted on, then it's removed from memory.
Highlighting the criteria would work, but some of the lists are very long.
Could the Out be sent/printed to another worksheet or file for retrieval if need be?

My goal here is to reduce some of the "unneeded output" that does "not fit" to the observed lotto patterns.

Let me know what your thoughts are, when you have the time.

Cheers.
Alex.