johnph77's Blog

Mods to the second phase spreadsheet were completed yesterday - far more easily and quickly than I would have ever imagined. Short test indicates they work as intended. I did the mods in two stages; the second reduced the spreadsheet size by about 4% - and, still, the spreadsheet runs about 220Mb in size. Data entry goes smoothly, though. Error traps work. The database is ready to build.

I can go back and continue work on Phase I, which could turn into a monster should all permutations be explored. The database is flexible enough to handle it. All I have to do is write the control files.

All going well - for a change.

I read over my earlier entries where I said I had given up on OpenOffice spreadsheets and the last comment was 22 Jul 09. True. I then started construction of the new spreadsheet with old software I have on hand (Lotus 1-2-3 - does anyone else still use it?). The new file is complete. It's highly unlikely that the client will use it in its original form - I'm certainly not happy with the requirements as requested - and I'll be working on the mods that I believe that it will be necessary to please both him and I. There are over 7.6 million formatted cells in the spreadsheet - no mean accomplishment - and a single data entry format. Best of all, the file can be converted to MS Excel, both the 2003 and 2007 versions, with a minimum of effort.

All in all, though, it's still a portion of the second phase of the project, and, as such, won't be used until the first phase is complete. It is a template, though, and will be the most important part of that phase. If I can keep the Lotus software usable, it'll save a tremendous amount of time since the conversion of data to formatted text files was and is easier in Lotus than in any other software.

As far as the lotteries are concerned, my next project will be the Lucky Lines game in Oregon.

Home. Good to be back. Tough trip - spent most of today sleeping, recovering from trip. Almost 12 hours late getting in.....

Tournament finish so-so, but had fun. Good people.

Work went well - I'll have to fire up the laptop to finish it - haven't done so yet - but the spreadsheet looked good before I shut it down just before I left to return home.  I forgot to take a flash drive with me so purchased a couple while on the road. 4Gb flash drives are now smaller than the 1Gb drives I bought just over a year ago - technology advances.....

Leaving later today for Daytona Beach FL - playing in a pinochle tournament, visiting friends. Be away for ten days. Will be a busy traveling schedule for me through November.

Reconstruction of the lost spreadsheet goes smoothly - better and faster than I originally had thought possible. I'll take the laptop with me (of course) and work on it while away. I underestimated the number of formatted cells, though - instead of 4 million, the number is more like 7.6 million per spreadsheet - and there will be two of them. They run just about 250Mb per - and that's with no data at all. I won't send them out that way, though - I'm concerned that they may be just too buggy. Time will tell. They will contain every error trap I've built, though - hopefully they'll catch any incorrect formatting.

While updating the old spreadsheets to cover the new error traps in my current project, I happened to open the OpenOffice spreadsheet I had built for the next part of this project. Trash. Garbage. Landfill. Somehow, during the update I made from OO 3.0 to 3.1, the 3.0 spreadsheets became unusable. Cell addresses in other cells became #NAME rather than the cell address originally entered. And as soon as I typed in the correction, those cell addresses reverted back to #NAME. The spreadsheet has been deleted - totally unusable and uncorrectable. I'll now have to rebuild the entire file in Excel - and, while I find the new ribbon taskbar hard to use and navigate, I have to bite the bullet - and there are about 4 million cells to be formatted. I am not a happy camper.

The OpenOffice word processor is good enough for the ordinary user, inasmuch as neat, ordinary documents can be generated much like that of the processors one used to have to pay for. But specialized functions, such as typesetting (which is a necessity in some of my applications) is missing.

I had a lot of time invested in the creation of the deleted spreadsheet, though. So goes it - back to work. Into each life some rain must fall.

The math project will be restarted. I believe the final gap in ensuring the complete and total accuracy of the entered data has been incorporated. In addition, the presentation format has been modified to reduce the numbers of files necessary to comlete the presentations, and, therefore, the work needing done, to simplify the task and shorten the process. It means disposing of all the current files but the end should justify the means. Work is already underway on this, and the format looks good. Almost all the data entered so far can be used; it just has to undergo an additional error trap.

In addition, there will be a fundamental change in the addons. Hopefully this will better support the project aims.

There will be less work involved in the future, the new simplified templates are already built, and things are looking up. The client is more than pleased with all this, as it'll mean a shorter length of time will be necessary for completion.

One of the goals I had set for myself at the beginning of the year was to have pinochle tournament card setups of high-end plastic cards suitable for tournament use. I am now within reach of that goal.

Later this week I should have - ready for use - three 15-table setups of cards, ready for use. I'm also planning on a 45-table setup which would have covered the the tournament with the largest number of players I've ever attended.

We used the 20 sets I currently own out of the planned 45-table set in Detroit recently and the feedback was overwhelmingly positive. The players loved the ease of shuffling and handling which goes along with the use of plastic cards. The sets were returned with only one card missing, which can and will be replaced.

In addition, there were tournament directors from other tournaments who expressed an interest in using those cards for their tournaments - and that's what they're there for.

As an offshoot, I now have a goodly number of setups for my personal use.

The reason for all this is that I enjoy playing with good cards - they enhance the pleasure of playing the game - and, in the last couple of years, I've become somewhat of a playing card collector, obtaining rare and hard-to-get cards. Amongst the sets I currently have are Copag Centennials in both bridge and poker widths, which, IMHO, have the most beautiful, if somewhat impractical, faces of any deck I've ever seen.

More on this later as progress is made.

Put together an Excel spreadsheet as a result of a thread in LP requesting same. To my knowledge it works fine.

Basically, two groups of five numbers can be entered in the spreadsheet in the appropriate cells, and the spreadsheet will return all 1,000 3-digit combinations - the 125 combinations possible only by using the 5 numbers in the first group, the 375 combinations possible by using two numbers in the first group and one from the second, the 375 combinations possible by using one number from the first group and two numbers from the second, and the 125 combinations possible by using only the 5 numbers in the second group. The cell backgrounds are color-coded to differentiate the four groups. The output cell contents are in text format, allowing those combinations with a leading zero to be viewed. This sort of thing works well with high/low, odd/even, or any five numbers one wants to highlight - but all 10 numbers have to be entered for the chart to work properly.

There's a request for the same type of chart for Pick 4, but I'm far too occupied right now to have the time to format 10,000 cells - sorry.

Working on the math project again. Currently in Phase IV, but it appears that Phase III will have to be redone from the start - too much detritus and errata in the files that have been sent. Not good.

The entire project may or may not be shortened - there's been some indication that they want to go in a different direction with future files. If so, it won't affect the work done so far, but would change the original plan considerably. Time will tell.

Took some time off to build an Excel Pick 3 chart featuring 3 of 5 for another LP member - works well - I think.

Got home yesterday morning. Did not finish well in either tournament but had fun.

Lots of work accomplished, still more to go. There's always a lot of catching up to do when I get back. I'm satisfied with what was accomplished while I was away.

Back to the mines.

Back on the road Monday for two tournaments. Staying again with friends in Columbus, riding to Charlotte with them, then soloing to Detroit the following weekend. Good trip planned. visiting old friends as well.

I'm picking up the pieces of the first project - very labor-intensive. Refining existing spreadsheets and databases, experimenting with other software. Making significant progress with completing the next section but nothing published as yet. When I do, though, it'll be fast and furious. Building additional modules for the next phase as well.

I'll take a lot of work with me on the road, enough so that I don't think I'll be able to finish it.

I'll be in and out of here irregurlarly until I return.

gl

j

The formula for conversion of numeric information to text in my Pick 4 entry is incorrect - the correct formula is:

=MID(A1,1,1)&MID(A1,2,1)&MID(A1,3,1)&MID(A1,4,1)

I apologize for any inconvenience.

Permutation formulas for Pick 3. See previous entry re Pick 4 for comments and instructions.

=(MID(A1,1,1)*100)+(MID(A1,2,1)*10)+(MID(A1,3,1)*1)
=(MID(A1,1,1)*100)+(MID(A1,3,1)*10)+(MID(A1,2,1)*1)
=(MID(A1,2,1)*100)+(MID(A1,1,1)*10)+(MID(A1,3,1)*1)
=(MID(A1,2,1)*100)+(MID(A1,3,1)*10)+(MID(A1,1,1)*1)
=(MID(A1,3,1)*100)+(MID(A1,1,1)*10)+(MID(A1,2,1)*1)
=(MID(A1,3,1)*100)+(MID(A1,2,1)*10)+(MID(A1,1,1)*1)

gl

Stumbled across a question in the Pick 4 Forum the other day about inputting a 4-digit number in Excel and having the spreadsheet return all possible 24 permutations. I answered it but it was a PITA to formulate and edit my response. I thought about it for a while and came up with an easier way to construct the formulas. The Excel output is still the same no matter whether the formulas in my response in the Pick 4 Forum are used or copied from this entry. Here goes:

=(MID(A1,1,1)*1000)+(MID(A1,2,1)*100)+(MID(A1,3,1)*10)+(MID(A1,4,1)*1)
=(MID(A1,1,1)*1000)+(MID(A1,2,1)*100)+(MID(A1,4,1)*10)+(MID(A1,3,1)*1)
=(MID(A1,1,1)*1000)+(MID(A1,3,1)*100)+(MID(A1,2,1)*10)+(MID(A1,4,1)*1)
=(MID(A1,1,1)*1000)+(MID(A1,3,1)*100)+(MID(A1,4,1)*10)+(MID(A1,2,1)*1)
=(MID(A1,1,1)*1000)+(MID(A1,4,1)*100)+(MID(A1,2,1)*10)+(MID(A1,3,1)*1)
=(MID(A1,1,1)*1000)+(MID(A1,4,1)*100)+(MID(A1,3,1)*10)+(MID(A1,2,1)*1)
=(MID(A1,2,1)*1000)+(MID(A1,1,1)*100)+(MID(A1,3,1)*10)+(MID(A1,4,1)*1)
=(MID(A1,2,1)*1000)+(MID(A1,1,1)*100)+(MID(A1,4,1)*10)+(MID(A1,3,1)*1)
=(MID(A1,2,1)*1000)+(MID(A1,3,1)*100)+(MID(A1,1,1)*10)+(MID(A1,4,1)*1)
=(MID(A1,2,1)*1000)+(MID(A1,3,1)*100)+(MID(A1,4,1)*10)+(MID(A1,1,1)*1)
=(MID(A1,2,1)*1000)+(MID(A1,4,1)*100)+(MID(A1,1,1)*10)+(MID(A1,3,1)*1)
=(MID(A1,2,1)*1000)+(MID(A1,4,1)*100)+(MID(A1,3,1)*10)+(MID(A1,1,1)*1)
=(MID(A1,3,1)*1000)+(MID(A1,1,1)*100)+(MID(A1,2,1)*10)+(MID(A1,4,1)*1)
=(MID(A1,3,1)*1000)+(MID(A1,1,1)*100)+(MID(A1,4,1)*10)+(MID(A1,2,1)*1)
=(MID(A1,3,1)*1000)+(MID(A1,2,1)*100)+(MID(A1,1,1)*10)+(MID(A1,4,1)*1)
=(MID(A1,3,1)*1000)+(MID(A1,2,1)*100)+(MID(A1,4,1)*10)+(MID(A1,1,1)*1)
=(MID(A1,3,1)*1000)+(MID(A1,4,1)*100)+(MID(A1,1,1)*10)+(MID(A1,2,1)*1)
=(MID(A1,3,1)*1000)+(MID(A1,4,1)*100)+(MID(A1,2,1)*10)+(MID(A1,1,1)*1)
=(MID(A1,4,1)*1000)+(MID(A1,1,1)*100)+(MID(A1,2,1)*10)+(MID(A1,3,1)*1)
=(MID(A1,4,1)*1000)+(MID(A1,1,1)*100)+(MID(A1,3,1)*10)+(MID(A1,2,1)*1)
=(MID(A1,4,1)*1000)+(MID(A1,2,1)*100)+(MID(A1,1,1)*10)+(MID(A1,3,1)*1)
=(MID(A1,4,1)*1000)+(MID(A1,2,1)*100)+(MID(A1,3,1)*10)+(MID(A1,1,1)*1)
=(MID(A1,4,1)*1000)+(MID(A1,3,1)*100)+(MID(A1,1,1)*10)+(MID(A1,2,1)*1)
=(MID(A1,4,1)*1000)+(MID(A1,3,1)*100)+(MID(A1,2,1)*10)+(MID(A1,1,1)*1)

Enter each formula into individual spreadsheet cells where you would like to view each permutation.

The response in the Forum took about an hour to edit properly, the above was done in about 10 minutes in a text editor.

The formulas assume your initial 4-digit entry will be in Cell A1; if in any other place, the formula where "A1" appears will need to be edited to your preferred entry location. The results of these formulas will be numeric - if text results are desired one may eliminate the "*1000", "*100", "*10" and "*1" portions of the formulas along with the outermost set of parentheses in each part of the formula - example:

=MID(A1,1,1)+MID(A1,2,1)+MID(A1,3,1)+MID(A1,4,1)

gl

Back home. Terrible finish at Cleveland, but won Harrisburg - with an outstanding partner. We may conceivably form a steady partnership for East Coast tournaments after I fulfill all my current commitments.

Friends who so generously allowed me to share their home between tournaments increased my work productivity so much that I accomplished far more than the goals I had set before I left. All I have to do now is transfer and upload some files to get me back on track, then the fun begins. I've got a full three weeks of work to do, so it'll be nose to the grindstone, shoulder to the wheel and ear to the ground - then I have to find a way to work in that position.

Calculated and posted data on the AZ Cash 4 game - I picked up a ticket while traveling through there. I really want to revamp and update that whole file but that may be some time into the future before I get around to that.