Keeping Track of high game bowling scores

Trying to keep track of high games to post over the lanes,did a example layout but need help in how to sort out the data.

Would it be possible too have two names in a textbox if there was a tie on a lane?
Thanks Z

If you remove the text boxes and then use this formula in the first one and modify for the remaining you should be able to get the highest score

=MAX(IF($B$14:$B$63=1,$C$14:$C$63)) and make sure that you enter this by press Ctrl + Shift + Enter after typing this formula, instead of just pressing Enter.

This will place curly brackets {} around the formula and make it any array formula.

I am not sure if you can place more than one name without using a VBA solution and I am not sure of how to do that.

With those buttons you can have nothing but the problems.

Here, try this in A6:

Formula:

=IF(A5="","",INDEX($A$14:$A$100,MATCH((COLUMN()-1)/2+1&" "&A5,INDEX($B$14:$B$100&" "&$C$14:$C$100,,),0)))

I decided to jump in on this one.

The first thing I did was to change the data to Excel tables so there is a men’s table and a women’s table. Tables are so much easier to work with and reference in formulas.

Then I made a helper sheet (Sheet2). This sheet has a lot of array formulas.
Column A is the lane
Column B is the maximum score for that lane
Column C is the number of maximum scores for the lane (I rigged the data to have a tie on lane 3)
Column D is the row on which the first high score occurs
Columns E though I are the rows on where the first tie, second tie, etc. occur. Mostly these are #NUM errors because there aren’t a lot of ties.
Columns J though O look up the names associated with the high scores.
Column P is a concatenation of the names with the high scores.

I dropped the text boxes and replaced them with “regular” text boxes from the Insert Ribbon. I ran out of real estate, so I rearranged the layout.

I set the values of the text boxes to the values computed in Column P.

Finally, in a gesture of sexism, I colored the men’s data in blue and the women’s data in pink.

What's best is that there is no VB involved at all. This is all straight Excel.

And last, I’m glad I’m not bowling on this league. These scores are tough to beat.

P.S. I only did the men. You can set up a parallel system for the women's scores.

Hey zplugger,

I agree with Zbor that VBA is way to hard to do this problem. See my attached where I've done all the work in 3 possible Pivot Tables. Click on the filters to filter a single lane or use the lanes as columns heads. Learn a little about Pivots and you won't need VBA.

Bowling High game per lane Pivot.xlsm

http://www.excel-easy.com/examples/u...vot-table.html

Hello zplugger,

Without any bells or whistles, textboxes, labels,vba and so on, I have redone the first two Columns "Lanes 1 and 2" for you as an example. Please do let me know if you approve.

Kind Regards.

Hello zplugger,

Attached please find a sample Workbook of the Final results.

Regards.

Hello zplugger,

Thank you for the feedback.

Since you require a bit of automation, I had to include some VBA Coding.

As soon as you have amended the Players in the correct lanes, the results will adjust accordingly.

Please see the changes in the attached Workbook.

Regards.

Thank you for the feedback zplugger.

Is there a quick way to unhide the code columns?

Yes there is, but it should not be advocated since the formulas could be compromised.

Regards.

Old Man zplugger,

Might take this old guy some time but will give it a go.

You are no older than me "Old Man", and I still feel young!

Regards "Gramps".

Hi "Old Guy",

Speaking of which, maybe I should admit that same applies to me, since I erred in sending you the "Test" Workbook, instead of the "Final" Book.

Below is the correct Book. With the three tier rankings sorted out as well. If a Player appears on the List with a Lane Number and no Score yet, it displays the Player in his/her Lane with a zero Score.

I shall remove the "Test" Book from Post#17.

Here is a challenge for you!

Try to catch me out on the revised attached Workbook, or default me in any way!

Enjoy!

Thanks will have another look,one question for anyone if I may. Trying to password the workbook and having trouble with the removing duplicates when protected. Thinking its got something to do with the array? can I add code to remove password when the button is pushed the protect the book again after dup's are removed.I have all the ranges set to unprotected.
Another thing is when a new small name is added it does not go below another name in lane,go beside it when it should be on its own line. This thing is almost done and I hope other people can use the coding ideas from Winon.

Sub Doit()

    Sheet1.Range("$A$13:$C$1000").RemoveDuplicates Columns:=Array(1, 2, 3), _
        Header:=xlYes
    
       Sheet1.Range("$G$13:$i$1000").RemoveDuplicates Columns:=Array(1, 2, 3), _
        Header:=xlYes
 
    Range("C2").Select

End Sub

Sorry zplugger,

Another thing is when a new small name is added it does not go below another name in lane,go beside it when it should be on its own line.

I have missed this, but here is the solution:

For the Men in Lane 1 Paste this Formula in the "Box" with the three names, and then Copy and Paste to all the other "Boxes".

Formula:

=IF($E$13<>"","Check Duplications",IF(BI4<>"",CONCATENATE(BI4&" "&BJ4&CHAR(10)&" "&BI5&" "&BJ5&CHAR(10)&" "&BI6&" "&BJ6),CONCATENATE(BI4&" "&BJ4)))

Do the same for Women, also starting with the first "Box" with Formula below;

Formula:

=IF($K$13<>"","Check Duplications",IF(BZ4<>"",CONCATENATE(BY4&" "&BZ4&CHAR(10)&" "&BY5&" "&BZ5&CHAR(10)&" "&BY6&" "&BZ6),CONCATENATE(BY4&" "&BZ4)))

I have all the ranges set to unprotected.

Maybe you should set it back to Protected(Locked), since we are only working in Range A14:C1000 and Range G14:i1000, and the above Code Range Locked = False takes Care of that.

Hope that solves your issue/s.

Kind Regards.

Do you mean to Password Protect the Workbook or just the Sheet?

If it is just the Sheet, you could use the Code below:

Sub Doit()

    Sheet1.UnProtect "Password"

    Sheet1.Range("A14:C1000,G14:I1000").Locked = False
 
    Sheet1.Range("$A$13:$C$1000").RemoveDuplicates Columns:=Array(1, 2, 3), _
        Header:=xlYes
    
       Sheet1.Range("$G$13:$i$1000").RemoveDuplicates Columns:=Array(1, 2, 3), _
        Header:=xlYes
 
    Range("C2").Select

   Sheet1.Protect "Password"

End Sub

Repeat this in every Sub of the Code.

Hi zplugger,

I have corrected the formulas for Men and Women in Post#23.

Sorry for the slip.

Hi zplugger,

You are welcome my friend. Thank you for your patience with me.

May you also enjoy a Great Weekend.

Kind Regards.

Hello zplugger,

On second thoughts, I have decided to try and help in making your Weekend Great!

Check out this freebie attached revised Workbook, with all the latest required adjustments. All done.

All you have to do is to first Click on Unprotect Sheet, go to the VBE and search for all Passwords in all of the Code, and enter your password between the quotes.

This will save you a good piece of other adjustments, so that you can enjoy what is left for the weekend.

Enjoy.

Ran into small problem that I can't solve,when removing duplicates it also removes any cell formatting.Would like to keep all formatting to the cells. It also changing Cell Protection too.
Thank You

Sheet1.Unprotect ""
Sheet1.Range("A14:C1000,G14:I1000").Locked = False

    Sheet1.Range("$A$13:$C$1000").RemoveDuplicates Columns:=Array(1, 2, 3), _
        Header:=xlYes
    
       Sheet1.Range("$G$13:$i$1000").RemoveDuplicates Columns:=Array(1, 2, 3), _
        Header:=xlYes
    Range("C2").Select
Sheet1.Protect ""

Hi zplugger,

removing duplicates also removes any cell formatting...

would that be on the last Workbook I have Posted, and what kind of Formatting would be involved? I Find it very peculiar and strange!

Kind Regards.

Thank you zplugger,

I have done the same as what you have said, and I cannot replicate your problem. Have you perhaps Protected the Workbook Content itself as well?

Maybe it would be a better idea if you could send me your Workbook again, with all the relevant Passwords, for me to have a look at.

I would love to see what you have messed up again. LOL

Thank you zplugger,

Would that be on the last Workbook I have Posted?

Sorry zplugger, but please don't make your problem mine!

Just jerking your chain, gimme a day or so, and I will have it Solved. In the meantime, when you do want to make such changes, please just make sure the Sheet is always UnProtected.

'Till later then.

I am testing same, please allow me a moment.

O.K. zplugger,

Together we can put this to bed, but first we have to agree on a few matters.

1.

If I take and Highlight all Men's names,lanes,scores and remove content it does it.Now if I format the bolders and make a duplicate it takes away the formatting.Got something to do with clearing all example names?

This is a double/triple "barrel" question/statement, which I need to clarify with you.

a. How do you "take and Highlight all Men's names,lanes,scores and remove content"? Do you use the "Delete" or the "Clear" Button?

b. "Now if I format the bolders and make a duplicate it takes away the formatting", this will only happen if you have Protected the Workbook. Do you have the Workbook Protected?

c. "and make a duplicate it takes away the formatting". How do you go about making a duplicate and where do you do that, on a different Sheet, or Columns on the same Sheet?
NOTE: If you could show me how you want the Final "Bolders" and "Text", etc. I could provide for it in the Code to prevent it from not responding likewise.

Looking forward to your response.

How do you "take and Highlight all Men's names,lanes,scores and remove content"? Do you use the "Delete" or the "Clear" Button?

Are you using Clear?

I did not have VBA protected. What I asked is "Do you have the Workbook Protected?" This is different from Sheet protection.

Thank you for the feedback Bugger, oops I mean zplugger,LOL

Please NEVER use "Clear" for the Men's or Women's Sections, as "Clear" removes everything including Formats.

Please may I beg you to rather highlight the range/s and use the "Delete" Button.

Do try it and let me know if that solves your problem.

Hope it works for you now my Friend!

Hi my Friend,

I put a bolder around first 50 names in list and create a dup and remove the dup I lose all the formatting.

Where and how do you create the "Dup"

Hi my Friend,

Is there a form button I can add with a code to delete only the contents in the range?

That is possible yes, but you should use the "Delete" Button on the Keyboard only, and nothing in the Ribbon as such.

Please try it that way, and let me know if the problem persists.

Thank you.

Hello zplugger,

Revised sample Workbook attached.

Do what you like, use the "Clear" Button, Create a Copy(Sample on Sheet2), make your changes and Paste it as it appears(Without Formats), back to Sheet1, and see what happens.

Regards.

Hi Z,

Thank you for the feedback.

I added code to the Do It macro to format the bolder back,....

That is totally unnecessary, since the Delete Dups Code in the Do It Macro can NEVER remove Formats. It places an extra burden on that Macro, and I would suggest you remove it, and see for yourself.

Regards.

Does it do the same on my latest uploaded Workbook?

Hello zplugger,

I am glad to hear your problem seem to be Solved with the last Workbook. It is really a pleasure working with someone like yourself who knows what he wants, and is so keen to learn!

Thank you for your patience and for the "Rep", it is much appreciated.

Kind regards.

W

Hi zplugger,

Maybe you would like this revised attached Workbook better?

Kind Regards.

Hello zplugger,

Do you call it a challenge? Please

However, you are correct with your comment;

I Think for this to work we would have to use the names in BI to BW.

I "Hate" you for taking me to task. but love it!

It needed to change a few things, but as you will see in the revised attached Workbook, I hope it satisfies your latest requirement.

Best Regards.

Thank you for the kind words zplugger,

It is people like you, and others on this great Forum, making it worth my while to still hang around, 'till Higher Powers call on me for whatever awaits.

Greetings!

Thank you my Friend, it is long past my sleeping time, but don't worry, I shall do that for you, A.S.A.P,

Good Night, it is already almost 2 am Sunday her by us.

Hope you understand.

Hi zpligger,

I could nor fall asleep, since your latest request Kept on Bugging me.

So here we go, with the new revised attached Workbook.

Hope it Solves your latest request.

Bach=k to catch up on a lot of lost sleep(Rest)

You guys make me "Sick" LOL: just Joking:

HTh,

Best Regards.

Hello Mr. Z,

I believe we are not exactly on the same page!

Pick up your magnifying glass, and READ the Top Line of Sheet1, which states "Final Results". Nothing wrong with having a player in more than 1, 2 or 3 Lanes or more. It is a summary of the Final Outcome.

Now please Check Sheet2, which does not allow Players to be duplicated on more than one Lane. Sheet2 is named "Running Results", for a lack of a better description. You cannot ski with a boat fitted with an egg beater.

O.K. Z, enough revving you up, as you have said before, "you are an Oooold Man". LOL,

My Friend, please see if the revised attached Workbook suits you better.

Kind Regards.

Hello zplugger,

As I have said, the first Sheet is a summary of the final results.

...the bowling center will only put a name on one lane at a time

That is where they should use the dynamic, real time, second Sheet named "Running Results". No need for using a "x" on that Sheet.

W

Thank you for the clarification zplugger,

It requires at least two questions to be explained.

1. If there id a Tie in 1st or 2nd Place, what criteria to you use to "discriminate" against the other player when you use the "X"?

2. What is the duration of a typical Bowling Season, and how many games are played during that period?

Kind Regards.

Thank you for the feedback zplugger, I shall now resign from this project, unless you stumble upon something which requires attention, feel free to let me know.

Kind regards,

W

Hello zplugger,

In post #61 I can't fix the bug.

Sorry Z, but that is not a "Bug" for the Code was written to Compare Name, Lane and Score. If all of that was pasted at the Bottom of the List or anywhere else, it will definitely show as a "Dup".

I had to use those three criteria, Name, Lane and Score, otherwise the program would be cluttered with "Dups", which would be False!

Trusting that you get the concept, I am looking forward to any other clarifications.

Kind Regards.

Hello zplugger,

For a fail safe can we add a message box telling us there is a empty score on a lane,we could add this to the Lane and score buttons

No, personally I find repetitive MsgBoxes irritable, and would rather have the Code silently replace the Blank Scores with a Zero in the background!

Please see if the revised attached sample Workbook suits you better. Again you will have to change the Password/s to your own choice.

Regards.

Thank You Winon, May I ask a couple questions ?
In many hours of testing this is what I don't understand,in the last workbook go to sort lanes and you will see Brock Darville is is first name in lane1. Now change his zero score to 300,then sort men.You will see the new entry on top but if you sort lanes again he is gone? Like when you sort lanes it does not pickup top name,if you look at lane 6 it makes changes too.


Just noticed that if you delete mens data then womens data and reset format its all out of wack. Thanks (It the end of today,so rest my friend because tomorrow is new day.)
Z

No problem Z,

Yes, I made a few mistakes, but hopefully have it sorted out in the Workbook below.

Hi Z,

Give me a hint where the sort problem was, sure it had to do with row be added?

Yes, you are correct, it involves quite complicated changes to formulas and Code, and it is easy to miss something!

Restore Formats works fine in all aspects.

Regards.

Hello zplugger,

I have made the current Workbook work even better. To save you you the trouble of fixing your Password accordingly. please find a means of providing me with your Password, so that I can include it for you in the revised Workbook.

Thank you.

Hello zplugger,

You have a good eye, and I appreciate your positive feedback.

Sorry to see you say all my hard work is only 99.9% good enough for you.

In that case please find someone else that can do better.

Gotcha!, just joking my friend.

Please do let me know if you have any problems with the new attached Workbook.

Kind Regards.

Excellent work my friend,not sure but on my end when I delete both mens & womens data, form shrinks to almost nothing. Can't read anything?

Here you go, try the version below!

Hi Z,

What do you want to read if there is nothing?

The whole layout is like 3 inches wide
Capture.JPG

Whatever tickles your tummy, I am glad you approve so far.

W

Your going to hate me,some of the First place dups not working. For sure lanes 3,4,5,6,7,8 for women. Not sure about men yet. Same for men,kinda like finding whats wrong,not sure possible for me.LOL

I saw that before you did, took me a while to find the problem, and eventually nailed it. The culprit, again to to with the added lines!

there is another problem which is Excel related without a possible fix it seems, and that is when you sort the Scores for either Men or Women for the first time, you have to to Click on the "Sort Scores" for men and women twice, before it lists all the zeros if any at the top of the respective lists. I am sure you can live with that!

Please let me know if it works for you now?

Good afternoon zplugger,

The problem started when I had you add the zero's,my bad idea. Lets drop that part in code

That will also be a bad idea, since if you happen to have a blank Score, it will reflect in the corresponding lane as a Zero, if there are not already Three Players listed with scores greater than Zero. I am sure you don't want that either. Instead of filling out the blank Scores with a Zero, I have replaced it with an "X". You will see the benefit of that in the summary Boxes under the respective lanes.

You now also have two extra Buttons, one to "jump" to the Bottom of the Men's List, and the other takes you back to the top of the same list, instead of having to scroll all the way up or down.

To add an "X" to a Score you wish to hide, simply double click on that Score, and to remove the "X" from a Score, also just double Click on that Score again. Simple.

...your going to miss me aren't you

Nah I don't think so, since I bet you a Coke and a Bun, that you will come knocking on my door soon enough!

Stay in touch "Old Man".

Kind Regards.

Hi Z,

Thank you for the feedback my friend.

There is a problem in the last Workbook I have Posted.

Did you forget to clean your reading glasses? It is a typical "Old Man" syndrome!

If there is only one Name in a "Box" on any "Lane" it shows that there is a tie for 2nd Place, what nonsense is that???

I shall remove the Workbook in Post#92 and replace it, with mistakes taken care off. I expect you to scrutinize that Workbook, until you find a problem, otherwise I shall lose my temper and come and beat your Chickens to death. If you don't have Chickens, then you will just have to buy a dozen or so!!!

Regards.

Here you go zplugger,

I think the double click is a good idea But I think it should be restricted to the score column only

Agreed. Here is your last update.

Regards.