Locate the 25 positions of the letters of the 4 blocks Each block has 25 positions Ex = 1st position we have 01 06 51 56 2nd position = 02 07 52 57
3rd place 03 08 53 58
https://www.excelforum.com/attachmen...1&d=1512586258
Locate the 25 positions of the letters of the 4 blocks Each block has 25 positions Ex = 1st position we have 01 06 51 56 2nd position = 02 07 52 57
3rd place 03 08 53 58
https://www.excelforum.com/attachmen...1&d=1512586258
I do not understand what you mean, your example is not very clear.
Where do you expect the results to go?
<----- If you were helped by my posts you can say "Thank you" by clicking the star symbol down to the left
If the problem is solved, finish of the thread by clicking SOLVED under Thread Tools
I don't wish to leave you with no answer, yet I sometimes miss posts. If you feel I forgot you, remind me with a PM or just bump the thread.
A few minutes extra thinking and the Array formula solution appeared. If this is what is requested that is....
Formula:Please Login or Register to view this content.
Hello Jacc. Yes, half of the work, almost there, now it is necessary to locate in each draw line Below the 25 positions Example
1ª 2ª 3ª ........ 25ª
3 0 1 ......
Ok JACC now you have to calculate in each of the 25 positions the number of numbers
* Ex = position 17% has 2 numbers in each draw
If you fill in the expected results in the first two rows it will be much easier for me to understand.
Hello JACC you can put a color in the value zero, the sum will always be 20 because the draws we have 20 numbers distributed in the 25 positions
It will have positions that have 2,3, is up to 4 numbers
* An example for a position can have 4 numbers
Ex position 25 = 45,50,95 00 having these 4 numbers in the draw line we then have 4 numbers in the position
hello jacc please!
https://www.excelforum.com/attachmen...1&d=1512746957
hello jacc please!
Try this workbook.
When there was conditional formatting it took 25 seconds to run, very slow indeed.
So, I now remove the conditional formatting in the beginning of the code and re-apply it in the end. With this method it takes only 2.5 seconds to run, ten times faster.
I'm still not sure this is correct but it looks like your sample results.
:Please Login or Register to view this content.
Please Login or Register to view this content.
Last edited by Jacc; 12-09-2017 at 05:17 PM.
oh, ok, perfect!JACC work of art, great work, congratulations, thank you
Glad to hear it works!
ok , thank you jacc
Jacc, opa! we have a problem, when I put new drawings is not doing automatic,
* Can you see this?
It's because the macro uses the named ranges. I will make the named ranges dynamic when I get back this evening.
If you need it to work right now just change the size of the named ranges DataRange (same as your resultado_lotomania) and ResultRange.
Okay, I had forgotten you can make zero value statistics for each vertical position always from the last draw up, example
* Quantities of intervals with zeros and with the presence of numbers in each position in sequences
Do you need to mark the thread as unsolved?
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
ex = lowest interval 2 greater interval 9
* it is an example,
* target view or greater ranges and if sequences
jacc, correct the macro when I post new sorters
I only had time to do the dynamic named ranges. If you add more data now it will be included. I will try and do the other stuff tomorrow.
ok!!!JACC, OK, OK
Hello jacc, I'm also seeing by horizontal draw line
* Sequence blocks with zero value and with (x) (quantity per position)
* Then I could see these sequences of blocks,
* Exe = 0 0 0 x xx xx xx x 0 0 0 ......
* See where it starts and ends each block sequence in both the zero value and the (x)
* In addition to the vertical statistics by position
jacc, this is important,
* always see from the last to the next to the number of repetitions of the positions
* example
1245 for 1246 repeated 9 positions
the value zero in this noa is taken into account,
* only x, xx, xxx, xxxx
another example 1560 to 1561 repeated 10 positions
hello jacc please
Sorry for my abscence. I have little time these days, hopefully someone else on the forum can help.
jorel since you've posted in the VBA sub-forum are you committed to only VBA solutions?
I have formula solutions, although I haven't built dynamic ranges yet. Will wait for your response.
Dave
Ok,FLAME what do you suggest? Change post?
Ok I can create another post from the same subject in which forum directory
Aim is to see blocks of sequences too
* In matrix
* 01 02 03 04 05
* 06 07 08 09 10
* 11 12 13 14 15
* 16 17 18 19 20
* 21 22 23 24 25
Besides the statistics by columns, you can add your ideas ok thanks
No, no, no! There is no need to double post or move. I was just checking to see if formula solution(s) were acceptable to you.
Are we to understand that you want to fill the four quadrants just as the example (hand typed) shows?Aim is to see blocks of sequences too
* In matrix
* 01 02 03 04 05
* 06 07 08 09 10
* 11 12 13 14 15
* 16 17 18 19 20
* 21 22 23 24 25
If you are not aware of it those four quadrants can be selected as a non-contiguous range.
That done copy and paste this formula in AX7. While holding down just Ctrl hit Enter. The whole selection will populate.Formula:Please Login or Register to view this content.
Hello,FLAMERETIRED can you put a command button on the worksheet?
Yes. See the attached macro version of above.
but where is the block sequence statistic? statistics per row and column of each of the 4 quadrant
Working on it. I first wanted to confirm the output on this last part is what you want.
Is it?
yes, yes! FLame
Okay, it was just emphasized that the pattern of position repetition per position repeats from the last draw to the next 8 to 10, but if it's complicated you can leave out this part,
Objective = is to take the sequence of blocks.
OK. I don't know enough to tell if it's complicated. I just don't understand what you mean by that.
I took a guess, though. After filling the counts in V:AT select AU4:BH4 and double click the fill handle. It counts the 0s blocks in each row.
I would need a hand typed upload with examples of what you are describing.
In the meantime here is what I have. There are 6 buttons. 2 for each part.
Let me know if there are any details I overlooked.
Going to bed now.
Last edited by FlameRetired; 12-19-2017 at 05:04 AM.
Ok perfect on the way
* Each draw line has to count the blocks of zero, from x xx xxx xxxx
Ex =
* Zero x xx xxx xxxx
10 1 3 3 1
* In each draw line,
* When I'm going to put new raffles have to do automatic
Try this.
Fill the counts V:AT then click 'Summarize'.
Is this what you wanted?
Ohh,FLAME perfect. Parabens very good work, conglaturations Masterpiece in excel, thank you
Good deal. Thank you for the feedback, rep and marking your thread Solved.
We have a problem, when I put new drawings, the color in zero value does not appear.
* I will always put new raffles
Please upload so I can see what you mean.
I, I add new draws there does not appear the yellow color in the value zero
Hello flame, is only correct this, when I will put new draws have to appear the color in the value zero
This is going to take me some time. Everything I've tried so far is not working and I don't know why.
ok,flameretired
OK.
Try this. Updating the format adds time to the execution.
Hello flame ok now perfect, congratulations,
* Flame, for future work always do to put new raffles
* Thanks for the hard work,
You're welcome. Glad it works. Thanks for the feedback and posting. I learned some new things.
ok thank you
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks