Lexicographic Index & Sums

[Alexafjb]

Hi PAB

Changed the Module, no errors, runs good!
Tested against other "Program" Just to see and of course there is a difference in Lex.

This is my inputs:
Total Numbers Drawn = 49
DN1 = 11
DN2 = 18
DN3 = 24
DN4 = 48
Combination Sum Total = 170

It = 13 Results
CSN-2
11, 18, 24, 48, 22, 47 Sum = 170 Prime = 2 Lex = 11040221

Of course there are 12 more combinations.

Running a test against the combination above my result was 11033372?
I inputed 11, 18, 24, 48, 22,47 also did it in sorted order. same result 11033372?
I also ran the test by inputing the lex 11033372 and came up with 11, 18, 22, 24, 47, 48 .

So I'm baffled, could be the method used to arrive at the CSN, I don't know? Not smart enough here yet! Anyways I will run tests starting at 123456, 123457 etc. and see. Could take a while!

Cheers.
Alex.

 

[PAB]

Hi Alex,

Tested against other "Program" Just to see and of course there is a difference in Lex.

This is my inputs:

Total Numbers Drawn = 49
DN1 = 11
DN2 = 18
DN3 = 24
DN4 = 48
Combination Sum Total = 170.

It = 13 Combinations,
CSN No2 = 11, 18, 24, 48, 22, 47,
Sum = 170,
Prime = 2,
Lex = 11040221.

Of course there are 12 more combinations.

Running a test against the combination above my result was 11033372?
I inputed 11, 18, 24, 48, 22,47 also did it in sorted order. same result 11033372?
I also ran the test by inputing the lex 11033372 and came up with 11, 18, 22, 24, 47, 48 .

So I'm baffled.

OK.
You need to add another SORT to the code to sort the combinations from Left to Right in Ascending Order before calculating the TRUE Lexicographic Index Number.
The combinations need to be in Ascending order for the TRUE Lexicographic Index Number to calculate correctly.
I will leave you to have a go at amending the code and see 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]

Hi PAB

Been at it most of the afternoon.
One would think it's not that hard?

Range ("I4:N" Range ("I:N" & Rows.Count) .Sort _
Key1: = Range("I4:N4"), Order1:= xlLeftToRight, _
Key2: = Range("I4:N"), Order2: = xlAscending, Header:=XLNo

Code inserted next line down from "Next A"

Tried many Variants, But no Joy today!
I'm missing something, not getting my head wrapped around the code !

Anyways enough for today, will get at it again in the morning with fresh eyes.

Cheers.
Alex.

 

[Alexafjb]

Hi PAB

And another Year Begins!
Hope your not too dragged out today! LOL 99.0% of the world is Hung over.

Anyways ignore my last post. This is the sort I'm working on now, I'm not getting the "Range" to work? Not grasping it proper.

If "SumTotal" is "Dim As Range" Then what am I missing here?

End If
Next B
Next A
'Alex's Combination Sort LtoR, Ascending Ln 61 and Ln 62
Range("I4:O" & Range("I4" & Rows.Count).End(xlDown).Row).Sort _
Key1:=Range("I4"), Order1:=xlAscending, Header:=xlNo, Orientation:=xlLeftToRight
Range("Q4:Q" & Range("G" & Rows.Count).End(xlUp).Row).Formula = _
"=SUM(COUNTIF(I4:N4,{2,3,5,7,11,13,17,19,23,29,31,37,41,43,47}))"

I've copy and pasted a block to show position in the Module, I'm working with the 2 lines under the remake or comment if you will.

ERROR is a Run-time '1004'
Method 'Range of Object'_Global failed

Obviously I'm asking it to do a sort on something it can't find is this because it has not printed to the screen yet?
Will keep at it !

Cheers.
Alex.

 

[PAB]

Hi Alex,

Firstly, a Happy, Prosperous & Safe New Year to you.

This is the sort I'm working on now, I'm not getting the "Range" to work? Not grasping it proper.

If "SumTotal" is "Dim As Range" Then what am I missing here?

End If
Next B
Next A
'Alex's Combination Sort LtoR, Ascending Ln 61 and Ln 62
Range("I4:O" & Range("I4" & Rows.Count).End(xlDown).Row).Sort _
Key1:=Range("I4"), Order1:=xlAscending, Header:=xlNo, Orientation:=xlLeftToRight
Range("Q4:Q" & Range("G" & Rows.Count).End(xlUp).Row).Formula = _
"=SUM(COUNTIF(I4:N4,{2,3,5,7,11,13,17,19,23,29,31,37,41,43,47}))"

I've copy and pasted a block to show position in the Module, I'm working with the 2 lines under the remake or comment if you will.

ERROR is a Run-time '1004'
Method 'Range of Object'_Global failed.

Obviously I'm asking it to do a sort on something it can't find is this because it has not printed to the screen yet?
Will keep at it!

OK.
Your thinking is along the right sort of lines Alex.

(1) You need to SORT the combinations individually in Ascending Order from Left To Right, BEFORE the TRUE Lexicographic Index Number is calculated.
Hint: For..Next Loop

(2) You need to CLEAR the .SortFields BEFORE the integration of the SORT for Prime and then Lexicographic Index Number.

Have a go.
When you get fed up I will email you the amended code.

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

Ouch, My Brain Hurts!
Am I, even close?

'Dim for sort Var
Dim j As Long


Next B
Next A

'Sort loop Ascending & LtoR
With Range("I4:N4")
For j = 1 To .Rows.Count _
.Rows(j).Sort Key1:= Rows(j)Range("I4"), _
Order1:=xlAscending, Orientation:=xlLeftToRight
Next
End With

Keep getting Error at Key1:
As for clearing the Sortfields haven't Got that far yet!

Will Get back to it later.

Cheers.
Alex.

 

[Alexafjb]

Good Morning PAB

I've been thinking and reading & reading.
I'm not quite up to speed. Thought that, if I start back at coding again that things would start to fall back into place.

So on that note...

Please send off your Amended code.
I might be able to glean, why I can't figure out, either what the right values coming out of the "DrawnFrom" For/Next Loop are!
Or, what coding glitch I'm introducing into the "Sort Loop" I'm working on.

I think, I've bitten off, a little more then my mind can handle at the moment! That's not to say that I've given up, it's just I'm not comfortable with "Methods, Functions & Properties in VBA as of yet.
Let alone the proper use of VBE. But, I'm reading and picking up a little more each day.

After getting totally Knackered with it yesterday, I think it's time for me to start taking baby steps from the beginning again.

THX

Cheers.
Alex.

 

[PAB]

Hi Alex,

Please send off your Amended code.

email with attachement sent!

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

THX

Cheers.
Alex.

 

[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]

Hi PAB

How are you getting on?

Slow... But making head way!
I put about, 6 hours a day into in to it at the moment.
3-4 hours in the morning (4am to 8am) in your time that would be I guess UK time (9am to 1Pm) and in the afternoon (3Pm to 5Pm) - (8Pm to 10Pm).

Making stupid mistakes.
I started with adding DN5 to the mix, Dim'ed it, did the obvious code point insertion.
Module, Complied clean, but no output? So, I be in trace mode at the moment to see what I'm missing!
I think I might have buggered up at the "Event Handler"!

I hope you weren't expecting me to have it finished this morning!
If you compare this to life, I'm at the "Learning to tie my shoes stage".

Anyways, will slug on with it.

Cheers.
Alex.

 

[PAB]

Hi Alex,

Making stupid mistakes.
I started with adding DN5 to the mix, Dim'ed it, did the obvious code point insertion.
Module, Complied clean, but no output? So, I be in trace mode at the moment to see what I'm missing!
I think I might have buggered up at the "Event Handler"!

Sounds good to me!

Don't forget that adding the extra DN5 number you need to:-

(1) Dim DN5 As Integer - Already done .
(2) Take out the For...Next loop for B and adjust A.
(3) Add the DN5 = Range("E7").Value.
(4) Adjust the Sum criteria.
(5) Adjust the ActiveCell.Offset(s).

Important!: Don't forget to change the Formula in Excluding DN Numbers to allow for the extra range addition for the DN5 number.

Good luck, let me know if you have any problems!

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

THX
Pretty much there, but at the moment as I attempt to change the Cell Colour for "D7" & "E7" & include the proper text, Excel becomes Non responsive". Been recovering the file. So next time I will load my backup and recode, my guess is somehow I'm stuck in debug. It's either that or my computers memory is starting to Swiss Cheese! Which I guess is not a bad thing cause I want to up this laptop to 4 Gig of Ram, and this will give me the excuse.

Oh Happy Days.

Cheers.
Alex.

 

[PAB]

Hi Alex,

Pretty much there, but at the moment as I attempt to change the Cell Colour for "D7" & "E7" & include the proper text, Excel becomes Non responsive".

Perhaps you could explain this in a bit more detail please?
Are you trying to change it in the code or the WorkSheet?
You could just highlight cells D6:E6 and drag down one row and change 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]

Hi PAB

No Problem!
My Machine has been giving me grief for a while now, not to bad thou

Any who!

It's complete and should be sitting in you inbox!
Silly as this sounds, most of todays grief was Excel not taking the colour change, I could do it, but as soon as I hit enter to apply, Excel would Freeze? So in the end what I did was recode and save after every step and left the colour change to last, and for some reason it worked? Could be a memory issue, Who Knows.

Let me know what you think.

Cheers.
Alex.

 

[PAB]

Hi Alex,

VERY good work!

Just two changes to make:-

(1) Take out the - 1 from the For A...Next loop.
(2) Adjust the Formula in the WorkSheet to accomodate the DN5 range value.

Keep up the good work !

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

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.
Of course, reducing some code also.

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".

Just a thought.

Cheers.
Alex.

 

[Alexafjb]

Hi PAB

OK, I was, I see it now!

THX

Cheers.
Alex.

 

[Alexafjb]

Hi PAB
You've Got Mail!
Cheers.
Alex.

 

[PAB]

Excellent work Alex .

Just one thing, leave the DrawnFrom = DrawnFrom - 1 as it was under the Loop.

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.