Jul 08 2021 08:18 AM - edited Jul 09 2021 09:45 PM
Hi,
I coach softball and I am trying to learn how to create a play call sheet for my player's wristbands that they wear during the games. I will signal the play to the catcher and she will relay it back to the pitcher. I am trying to learn if there is a way I can create these play sheets for my players that are a random alpha/numeric combination. I will have letters for the type of pitch, and then combine that with one of the four pitching zones for the numeric portion (1-4). For example, a dropball down and away (D3) in my screenshot below would be found in 151 (row first 1, then column 15). The goal is that I combine the type of pitch (F=fastball, c=change up, d=dropball, and r=riseball, v=curve ball) with the zones (1-4) without having to enter the patterns manually in each and every cell. I would also like to easily change the combinations too. Is there a formula or way that I can do this for 5 more sections as you see below? Here is a screenshot of one section of what I am trying to accomplish. Thank you!
Jul 08 2021 11:02 AM - edited Jul 08 2021 11:06 AM
@BigWheels8 , There are formulas like RANDBETWEEN and CONCATENATE which can help. The RANDBETWEEN formula uses numbers, but using HLOOKUP formula, you can return an alphabet. See attached file for sample.
By pressing F9, the values will change to create a new combination.
=CONCATENATE(HLOOKUP(RANDBETWEEN(1,5),$C$4:$G$5,2,FALSE),RANDBETWEEN(1,4))
Jul 08 2021 11:02 AM
Jul 08 2021 12:04 PM
Solution@BigWheels8 maybe @amit_bhola solution is good enough, that would be great. But for the fun of it I worked out a sheet that might be useful. you can easily print it to both show the grid and the definitions of the numbers/letters and easily change and it guarantees that every possible combination is used at least once (assuming the grid is large enough). File attached.
Jul 09 2021 09:51 PM
Jul 09 2021 10:00 PM
Jul 09 2021 10:05 PM
May 19 2022 09:40 PM
May 20 2022 07:06 AM
@ingizmo In amit_bhola sheet I believe all the cells are randomly assigned so a reverse look up table as you request although possible (nearly everything is) but would take me a bit of thinking (I'll try to avoid, lol).
In the solution I posted above (and corrected in the attached) the randomized order is just repeated so cell I2 (row "0" and column "10") is repeated in cell K5 (row "3" and column "12") and then the sequence repeats until it fill the whole table. With added work it could add more randomization but this technique has a few advantages:
a) it guarantees all options used at least once,
b) it prevents the same call from having the same row/col (assuming the count of calls > columns and not an even multiple)
In the attached I:
- created a new column so after you have a everything set up you can copy and paste the VALUES ONLY to this new column and 'LOCK' the grid so it won't change all the time
- created a lookup column under the grid that creates that reverse lookup you requested. The column right now just uses col F but any order can be inserted there. The formula uses TEXTJOIN to show the multiple options with a separator and uses " ; " right now but that can be easily editted or the formula could be modified to separate the results into separate cells.
- corrected an ERROR in the original post above where I didn't offset the MOD result and hence repeated the first call and missed the last call in the grid
May 20 2022 07:09 AM
May 20 2022 12:18 PM
@ingizmo , looking up colors is difficult (It is possible by using macros, but a bit complicated so i am avoiding it).
For looking up values in a column, Mr. @mtarler has already given a solution.
There is a middle solution which i propose which looks up colors as well - if it suits your purpose.
(Well in reality, it actually masks the colors of "not found" cells to white, but the effect is same). However, the compromise is that this implementation doesn't arrange the results for one pitch in one column, but rather it arranges it in a rectangular grid same as original.
See attached File and press [F9] to generate new combinations.
May 20 2022 12:44 PM
May 20 2022 10:51 PM
@amit_bholaThank you! I'll dig more into the macro side. I've added a lookup concate add-in once in VBA and used to play, but it has been a few years. I'll review what you sent and see if I can't make it work! Thanks again!
May 20 2022 10:56 PM
May 21 2022 09:29 AM
@ingizmo Updated the file to
a) split lookup info into separate cells
b) use conditional formatting to color cells
Aug 31 2023 07:35 AM - edited Aug 31 2023 07:35 AM
In every tournament and game in the Champions League, referees are crucial. Similar to this,
How to Become a Tennis Sports Referee is familiar with all of the game's guidelines to help them make the best choice. The task of a referee is relatively difficult since they must determine the proper scoring. You're at the proper place if you're considering becoming a pickleball referee.
Jul 08 2021 12:04 PM
Solution@BigWheels8 maybe @amit_bhola solution is good enough, that would be great. But for the fun of it I worked out a sheet that might be useful. you can easily print it to both show the grid and the definitions of the numbers/letters and easily change and it guarantees that every possible combination is used at least once (assuming the grid is large enough). File attached.