macro help needed please - match value against a table

[khanaran]

I have read a book on thepick3 and I am working on a small project to get some of the information onto a spreadsheet

I tried to upload my zipped spreadsheet unfortunately its a little large for the forum

http://www.mediafire.com/file/it70n97kg2muux8/match+and+color.xlsx


The macro needs o take the value in F2 ad mach-withn the table o2 to u17

if a match,t shade the cell contents in the table yellow

do the same for f2 to f62.

after that create a list of rows unshaded

thanx in anticipation

regards

raj

 

[khanaran]

Hi

I made an amendment to the attached file

Column F and Column M2:u16 are all now in the text format

Here is the link to the corrected file



Thank you and kind regards

raj

 

[Frank]

Im not quite sure why you think you need a macro for this. Conditional formatting would do this well.

I'm also wondering why cell F2 is so important to you. As a one off for this set of results then F2 presumably represents the most recent processed result. But what happens when you want to add a new result ? Where will you put it? You would need to either delete the whole table of results and paste in an entire new set with the new latest result on row 2, or insert a new row above to enable you to add a new result. If you do that then the latest processed result you wish to check will still be in F2, but the tested cell (which used to be F2) is now F3. So the system fails.

Also you do not appear to be checking all of your table N2:U16, you are checking O2 to u17 instead.

Also your statement "after that create a list of rows unshaded" is puzzling.
Why do you need this ? The yellow shading itself tells you all you need to know in situ on the sheet. A list of row numbers is actually less informative ? you still need to look at the sheet to cross reference with the actual cells ?

Please clarify these points please.

 

[khanaran]

Hi Frank

Thank you so much for pointing out my mistakes

I included my archive worksheet which updates the match worksheet so this may solve one of my mistake.

I would appreciate e if you can help with solving the values from i6 to i66 on the match worksheet and if this value appears on the table , it must shade the cell matched in yellow.

I thank you once again for your help.

I will appreciate any more comments and advice.

We now have the pick3 which is on each evening at 8.30pm



Regards

Raj

 

[jack]

There are 1000 straight pick 3 numbers of those:
720 Are Singles and they are 6 ways numbers, that is, they have 6 permutations: 123, 132, 231, 213, 312 and 321.
Each of those permutations is made out of 120 (Box) pick 3 numbers, such as:
123 = Lowest, Middle and High digits:
012, 013, 014, 015, 016, 017, 018, 019, 023, 024, 025, 026, 027, 028, 029, 034, 035, 036, 037, 038, 039, 045, 046, 047, 048, 049, 056, 057, 058, 059, 067, 068, 069, 078, 079, 089, 123, 124, 125, 126, 127, 128, 129, 134, 135, 136, 137, 138, 139, 145, 146, 147, 148, 149, 156, 157, 158, 159, 167, 168, 169, 178, 179, 189, 234, 235, 236, 237, 238, 239, 245, 246, 247, 248, 249, 256, 257, 258, 259, 267, 268, 269, 278, 279, 289, 345, 346, 347, 348, 349, 356, 357, 358, 359, 367, 368, 369, 378, 379, 389, 456, 457, 458, 459, 467, 468, 469, 478, 479, 489, 567, 568, 569, 578, 579, 589, 678, 679, 689, 789
132 = Lowest, Highest and Middle digits:
021, 031, 041, 051, 061, 071, 081, 091, 032, 042, 052, 062, 072, 082, 092, 043, 053, 063, 073, 083, 093, 054, 064, 074, 084, 094, 065, 075, 085, 095, 076, 086, 096, 087, 097, 098, 132, 142, 152, 162, 172, 182, 192, 143, 153, 163, 173, 183, 193, 154, 164, 174, 184, 194, 165, 175, 185, 195, 176, 186, 196, 187, 197, 198, 243, 253, 263, 273, 283, 293, 254, 264, 274, 284, 294, 265, 275, 285, 295, 276, 286, 296, 287, 297, 298, 354, 364, 374, 384, 394, 365, 375, 385, 395, 376, 386, 396, 387, 397, 398, 465, 475, 485, 495, 476, 486, 496, 487, 497, 498, 576, 586, 596, 587, 597, 598, 687, 697, 698, 798
Etc.
270 Are Doubles and they are 3 ways numbers, that is they have 3 permutations: 113, 131 and 311:
Each of those permutations is made out of 90 (Box) pick 3 numbers, such as:
113 = Lowest and Highest:
001, 002, 003, 004, 005, 006, 007, 008, 009, 011, 112, 113, 114, 115, 116, 117, 118, 119, 022, 122, 223, 224, 225, 226, 227, 228, 229, 033, 133, 233, 334, 335, 336, 337, 338, 339, 044, 144, 244, 344, 445, 446, 447, 448, 449, 055, 155, 255, 355, 455, 556, 557, 558, 559, 066, 166, 266, 366, 466, 566, 667, 668, 669, 077, 177, 277, 377, 477, 577, 677, 778, 779, 088, 188, 288, 388, 488, 588, 688, 788, 889, 099, 199, 299, 399, 499, 599, 699, 799, 899
10 are Triples and they have only 1 permutation:
000, 111, 222, 333, 444, 555, 666, 777, 888, 999
The Triples are always Straight numbers not Boxed.
-------------
---------------
Back to the Singles:
As I said they have 6 ways or permutations:
------------
123, 132 = Lowest digit on the Lth or 1st position.
231, 213 = Middle digit on the Lth position.
312, 321= Highest digit on the Lth position.
------------
213, 312 = Lowest digit on the Middle or 2nd position.
123, 321 = Middle digit on the Middle position.
132, 231 = Highest digit on the Middle position.
----------
231, 321 = Lowest digit on the Rth or 3rd position.
132, 312 = Middle digit on the Rth position.
123, 213 = Highest digit on the Rth position.
----------
So there is 1 chance out of 3 (1/3) that one of the Lowest, Middle and Highest will be in any one of the 1st, 2nd and 3rd positions.
-----------
---------------
Lowest digit:
123, 132 = Lowest digit on the Lth or 1st position.
213, 312 = Lowest digit on the Middle or 2nd position.
231, 321 = Lowest digit on the Rth or 3rd position.
-------------------
Middle digit:
231, 213 = Middle digit on the Lth position.
123, 321 = Middle digit on the Middle position.
132, 312 = Middle digit on the Rth position.
-----------------------
Highest digit:
312, 321= Highest digit on the Lth position.
132, 231 = Highest digit on the Middle position.
123, 213 = Highest digit on the Rth position.
------------------------
As seen the chances of the Highest digit being in any one of the 3 positions is 1/3 or 1 in 3.
Same for the Middle and also for the Lowest digit.

 

[jack]

Beware of mistakes.



Highest digit:

---------------------------------------

312, 321= Highest digit on the Lth position: 240 pick 3 numbers:

201, 301, 401, 501, 601, 701, 801, 901, 302, 402, 502, 602, 702, 802, 902, 403, 503, 603, 703, 803, 903, 504, 604, 704, 804, 904, 605, 705, 805, 905, 706, 806, 906, 807, 907, 908, 312, 412, 512, 612, 712, 812, 912, 413, 513, 613, 713, 813, 913, 514, 614, 714, 814, 914, 615, 715, 815, 915, 716, 816, 916, 817, 917, 918, 423, 523, 623, 723, 823, 923, 524, 624, 724, 824, 924, 625, 725, 825, 925, 726, 826, 926, 827, 927, 928, 534, 634, 734, 834, 934, 635, 735, 835, 935, 736, 836, 936, 837, 937, 938, 645, 745, 845, 945, 746, 846, 946, 847, 947, 948, 756, 856, 956, 857, 957, 958, 867, 967, 968, 978

210, 310, 410, 510, 610, 710, 810, 910, 320, 420, 520, 620, 720, 820, 920, 430, 530, 630, 730, 830, 930, 540, 640, 740, 840, 940, 650, 750, 850, 950, 760, 860, 960, 870, 970, 980, 321, 421, 521, 621, 721, 821, 921, 431, 531, 631, 731, 831, 931, 541, 641, 741, 841, 941, 651, 751, 851, 951, 761, 861, 961, 871, 971, 981, 432, 532, 632, 732, 832, 932, 542, 642, 742, 842, 942, 652, 752, 852, 952, 762, 862, 962, 872, 972, 982, 543, 643, 743, 843, 943, 653, 753, 853, 953, 763, 863, 963, 873, 973, 983, 654, 754, 854, 954, 764, 864, 964, 874, 974, 984, 765, 865, 965, 875, 975, 985, 876, 976, 986, 987

-------------------------------------------------------------------------

132, 231 = Highest digit on the Middle position: 240 pick 3 numbers:


021, 031, 041, 051, 061, 071, 081, 091, 032, 042, 052, 062, 072, 082, 092, 043, 053, 063, 073, 083, 093, 054, 064, 074, 084, 094, 065, 075, 085, 095, 076, 086, 096, 087, 097, 098, 132, 142, 152, 162, 172, 182, 192, 143, 153, 163, 173, 183, 193, 154, 164, 174, 184, 194, 165, 175, 185, 195, 176, 186, 196, 187, 197, 198, 243, 253, 263, 273, 283, 293, 254, 264, 274, 284, 294, 265, 275, 285, 295, 276, 286, 296, 287, 297, 298, 354, 364, 374, 384, 394, 365, 375, 385, 395, 376, 386, 396, 387, 397, 398, 465, 475, 485, 495, 476, 486, 496, 487, 497, 498, 576, 586, 596, 587, 597, 598, 687, 697, 698, 798

120, 130, 140, 150, 160, 170, 180, 190, 230, 240, 250, 260, 270, 280, 290, 340, 350, 360, 370, 380, 390, 450, 460, 470, 480, 490, 560, 570, 580, 590, 670, 680, 690, 780, 790, 890, 231, 241, 251, 261, 271, 281, 291, 341, 351, 361, 371, 381, 391, 451, 461, 471, 481, 491, 561, 571, 581, 591, 671, 681, 691, 781, 791, 891, 342, 352, 362, 372, 382, 392, 452, 462, 472, 482, 492, 562, 572, 582, 592, 672, 682, 692, 782, 792, 892, 453, 463, 473, 483, 493, 563, 573, 583, 593, 673, 683, 693, 783, 793, 893, 564, 574, 584, 594, 674, 684, 694, 784, 794, 894, 675, 685, 695, 785, 795, 895, 786, 796, 896, 897

-------------------------------------------------------------

123, 213 = Highest digit on the Rth position: 240 pick 3 numbers:


012, 013, 014, 015, 016, 017, 018, 019, 023, 024, 025, 026, 027, 028, 029, 034, 035, 036, 037, 038, 039, 045, 046, 047, 048, 049, 056, 057, 058, 059, 067, 068, 069, 078, 079, 089, 123, 124, 125, 126, 127, 128, 129, 134, 135, 136, 137, 138, 139, 145, 146, 147, 148, 149, 156, 157, 158, 159, 167, 168, 169, 178, 179, 189, 234, 235, 236, 237, 238, 239, 245, 246, 247, 248, 249, 256, 257, 258, 259, 267, 268, 269, 278, 279, 289, 345, 346, 347, 348, 349, 356, 357, 358, 359, 367, 368, 369, 378, 379, 389, 456, 457, 458, 459, 467, 468, 469, 478, 479, 489, 567, 568, 569, 578, 579, 589, 678, 679, 689, 789

102, 103, 104, 105, 106, 107, 108, 109, 203, 204, 205, 206, 207, 208, 209, 304, 305, 306, 307, 308, 309, 405, 406, 407, 408, 409, 506, 507, 508, 509, 607, 608, 609, 708, 709, 809, 213, 214, 215, 216, 217, 218, 219, 314, 315, 316, 317, 318, 319, 415, 416, 417, 418, 419, 516, 517, 518, 519, 617, 618, 619, 718, 719, 819, 324, 325, 326, 327, 328, 329, 425, 426, 427, 428, 429, 526, 527, 528, 529, 627, 628, 629, 728, 729, 829, 435, 436, 437, 438, 439, 536, 537, 538, 539, 637, 638, 639, 738, 739, 839, 546, 547, 548, 549, 647, 648, 649, 748, 749, 849, 657, 658, 659, 758, 759, 859, 768, 769, 869, 879

-------------------------------

 

[jack]

On this boxed numbers: 123 permutation (Singles):

012, 013, 014, 015, 016, 017, 018, 019, 023, 024, 025, 026, 027, 028, 029, 034, 035, 036, 037, 038, 039, 045, 046, 047, 048, 049, 056, 057, 058, 059, 067, 068, 069, 078, 079, 089, 123, 124, 125, 126, 127, 128, 129, 134, 135, 136, 137, 138, 139, 145, 146, 147, 148, 149, 156, 157, 158, 159, 167, 168, 169, 178, 179, 189, 234, 235, 236, 237, 238, 239, 245, 246, 247, 248, 249, 256, 257, 258, 259, 267, 268, 269, 278, 279, 289, 345, 346, 347, 348, 349, 356, 357, 358, 359, 367, 368, 369, 378, 379, 389, 456, 457, 458, 459, 467, 468, 469, 478, 479, 489, 567, 568, 569, 578, 579, 589, 678, 679, 689, 789

On the Lth or 1st position:

The digit that comes out the most is the 0 followed by the 1 then the 2 then the 3 then the 4, Etc.

On the Rth or 3rd position the digit that should come out the most is the 9 followed by the 8 then the 7 then the 6 then the 5, Etc

On the Middle positon I don't quite recall, but things are not quite the same the chances of digits being in the Middle position are a little more even that is their probable distribution:

Digits 4 and 5 might have a little more chance of being in the Middle postion, then follow digits 3 and 6 then follow 2 and 7 then 1 and 8, Etc.

 

[jack]

There are 3 pairs:
Lowest to Middle digits = LM
Middle to Highest digits = MH
And Lowest to Highest digits = LH
There are 3 positions for pairs so those pairs can be on any of the 3 positions.
I wil say that this are the 3 positions:
Lth, Rth and Sides (S), so:
L, R and S.
So there are 3 factors and then another 3 factors that makes for 9 combinations such as:
L-LM, L-MH, L-LH
R-LM, R-MH, R-LH
S-LM, S-MH, S-LH
-------------------
Mon, Feb 10, 2014 5-2-6 L-LM, R-LH, S-MH
Sun, Feb 09, 2014 1-8-3 L-LH, R-MH, S-LM
Sat, Feb 08, 2014 7-9-3 L-MH, R-LH, S-LM
Fri, Feb 07, 2014 4-9-8 L-LH, R-HM, S-LM
Thu, Feb 06, 2014 7-5-1 L-MH, R-LM, S-LH
Wed, Feb 05, 2014 9-3-2
Tue, Feb 04, 2014 5-4-6
Mon, Feb 03, 2014 0-5-7
Sun, Feb 02, 2014 9-0-4

 

[jack]

wanted to make sure



9,8,7= high



0,1,2= low



4-5/3-6= middle





For the Highest Digit the 9, 8 and then 7 have a higher chance to come out.

For the Lowest Digit the 0, 1 and then 2 have more of a chance.

For the Middle Digit the 4-5 first and then 3-6 have a little more of a chance.

4-5 = Equal chance, 3-6 = Equal chance, that is how it works for the Middle Digit.



Do you look at first position or 3rd position to determine what keys to use?
You would Box the straight pick 3 number and then either try to guess the digit on the 1st or on the 3rd position, you would need to keep track of the digits on those positions, I guess.
You are right. The 340 was a miscalc, and it should be 240 (for non repeating digits) . Thanks for the correction. Also there is no implication in my post that this filter should be a be all and an end all to making selctions. It was suggested to possibly combine it with other filters.

 

[Frank]

OK Khanaran,

To get back onto the topic you posted, your spreadsheet, I have downloaded your latest version and can see how you operate it. I see that you have a macro to scroll down the existing results and make a space for the new result which then gets pasted in. In the latest version the cell of interest ( for comparison) is I6.

There were a few issues with formatting which caused problems. Your concatenated sorted figure in column I was actually text and formatted as text. I have converted the evaluated result to a number ..=VALUE(text in cell I6 etc.) and formatted as custom 000, which displays the final ordered number in three digit format. The whole of column I is formatted the same.

The table on the right had a mixture of text versions of numbers e.g '028 and actual numbers which did not help.

I have made all the numbers N2:U16 into real numbers and formatted custom 000 to give all 3 digits.

Finally I used conditional formatting> highlight cell rules> equal to>$I$6 for all the table cells and I7 downwards. I used the provided yellow fill in that wizard.

I think its now doing what you specified. http://www.mediafire.com/file/sdmrmjsdk7wuu1d/match.xlsm

Give it a try and see, and Good luck.

 

[khanaran]

Hi Frank

Thank you so much for fixing my spreadsheet.

I note that the i6 matched in the table and it was shaded.

How do I get from i6 to i 66 to show shaded yellow if the values match in the table

Thank you in anticipation for your help and comment

Regards raj

 

[khanaran]

Hi Frank

The main purpose of the match sheet is to check how many of the values from I6 to I66 match on the table.

Once we determined how matched and the cells got shaded with yellow, we get rows that may contain cells without any yellow shading.

we then use the unshaped rows to find the new next pick3.

once and unshaped rows gets a matching value from the results sheet as they get updated, the table gets cleared and we start again matching the values from i6 to i66.


Trust this may give you a better idea what I hope to achieve.
Regards

Raj

 

[jack]

hello ,ICE, please in excel 2016 post

 

[Frank]

Khanaran,

If I understand you correctly, you have changed your requirements from the original description.

You originally asked for numbers in the table to turn yellow when matched with the top of column I, which I have done. You also said "do the same for f2 to f62.", which in your latest sheet would be I7 downwards. I took this to mean that you wanted cells in column I to go yellow ALSO if any matched with I6. I conditionally formatted this column to do this.

Now it appears you want matching cells in the WHOLE TABLE on the right to go yellow if ANY VALUE in ALL OF column I matches a value in the table, (not just I6). If this is what you wanted why didn't you say so in the first place ?

Please clarify this.

 

[Frank]

Khanaran,

Based on what I now think you require I have modified the spreadsheet, (same link) to highlight the table cells in yellow if they match anything in column I. You will notice that the unmatched rows of the table are highighted in pink. I created another table to the right containing the MATCH function to make it easier to deal with the multiple comparisons you now require. The conditional formatting refers to this other table to find empty unmatched rows. If you do not wish to see this table you can hide columns W to AD.

I hope this is more like what you were looking for.

 

[khanaran]

Hi Frank

magic this is what I was looking forward.

so now i am going to paper trail the pink row for the next four to 5 draws.

I have also added a few more bits of info that I have read and will appreciate your comments and advice.

 

[khanaran]

Khanaran,

Based on what I now think you require I have modified the spreadsheet, (same link) to highlight the table cells in yellow if they match anything in column I. You will notice that the unmatched rows of the table are highighted in pink. I created another table to the right containing the MATCH function to make it easier to deal with the multiple comparisons you now require. The conditional formatting refers to this other table to find empty unmatched rows. If you do not wish to see this table you can hide columns W to AD.

I hope this is more like what you were looking for.

Hi Frank

Thank you

this is working perfectly

I like the way you spread the sheet out and the explanation

many thanks

Raj

 

[Frank]

You are welcome, I hope it helps bring you some success.

 

[khanaran]

Hi Frank

the sheet is work nicely

I am uploading a working copy of the match spreadsheet

http://www.mediafire.com/file/s245u5t8915rb4p/match%282%29.xlsm

I need your help with the following:-

In the match sheet, when a number that was previously shaded is automatically deleted from the updating , it leaves they ellow shade can this be set so that it shades only if the cell contains value

On the archive , I recorded a macro called macro6.. The macro copies the data from table 4 and creates a new table and the purpose was to create and sort from Top to bottom to gives a HOT to Cold numbers for each column

Thank you in anticipation

Regards

raj

 

[khanaran]

Hi Frank

Sorry

There was a typo in my macro so I correct and uploaded correction

https://www.mediafire.com/?t0o86tnljyuvayk

I further question if I may trouble you further..

Will it be possible to scan results and if either a 3,6 or 9 ball has been drawn, anticipate the a double digit draw inthe next or subsequent draw


Once again thank you for your help and guidance

Raj