Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Sorting letters in a column when they occur once or more times

0

Hi,

I am glad it was you who picked up this because I don't think I thanked you properly for your brilliant solution to my question. I took your advice and got rid of the merged rows. That took me almost back to the beginning and gave me chance improve my sheet. Sadly, where your code changed to allow the merged portion, I tried to break it apart, but nothing worked. I also got confused as adding columns meant I couldn't work out what went where, hence going back to my amatuer coding.

To be honest, your original advice made me realise I can calculate the letters in my tray, I used to just add them into the board at the end to see what was left. Now I can see the information in the red/yellow column perhaps I should leave it there. I have wasted enough of my time and I don't want to waste any more of yours. If you want to tell me to use the sheet as it is I will accept your decision. After all is said and done, I don't intend offering or selling the workbook to anyone else, it is purely for the satisfaction of knowing I (we) have achieved almost perfection.

If you want to play around with my file it works best (and worse) if protection is set. I am sure it is unprotected, but simply 1234 if not. The passwords I use elsewhere are a lot stronger.

Thank you for reading this. I am sorry for the confusion I have caused. For the most part I think it frustrates me to think I cannot solve this problem myself. From my early days teaching myself BASIC (Sinclair QL) I thought it was a case of looking in C2, if a letter is found place it in D2. Loop down C until the next letter is found and put it in D3 and continue until D8 is filled or C101 is reached. Fireworks and thunderclaps would ensue and I would be content knowing we had succeeded. 

Thanks for everything once again. Have a good day.

excelmacros-vbaformulas-functions
MS Office Version: Office365
posted by: MrEMann (rep: 20) on Sun Aug 11, 2019 at 9:58 am
Answer
Discuss

Discussion

Please provide a row where you indicate the values in cell A1 the Values In cell B1 and what you expect in cell C1
k1w1sm (rep: 197) Aug 12, '19 at 5:59 pm
Thanks for your answer. I was going to send the file, but now I cannot see that option. The sheet is a version of Scrabble, but differs for copyright reasons.

A2:A28 counts the letters as they are played, reducing.
B2:B28 is A to Z + the "?".
C2:C101 is all the possible characters that can be used.
=IF(A2>6,"A","0" is the formula I used to replace the letters with "0", although I did find "ZZ" was more accurate than "0".
I hope that answers your questions, but if you can suggest a way I can send the file you would have a better understanding of my goals. 

Could I just add that a running list as the letters are removed would be ideal, but I did foresee either a macro I could activate manually once only 7 letters remain. Or a macro that self activates once only 7 or less letters remain.

I hope that explains my problem in better detail. Thanks again for any help you can offer. May I also assure you my sheet does not allow me to cheat, it just gives me an edge! I only play for fun and as a stroke survivor, for brain excersise. Thanks again.
MrEMann (rep: 20) Aug 12, '19 at 8:45 pm
A problem like this can't be solved based on hypothesis because in order to create true hypothetical similarity you would need to know the solution which you don't. I have sen your workbook before but even what it was like then isn't enough to work out a solution now. Please post your workbook. You must Edit your question in order to access the capability to append files to it.
I think you enter letters on the scrabble board. Then you probably create a list of the entered letters somewhere (where?). Then you want a unique list of letters used (where?). It seems that you tell us to extract the unique list from the complete list. Why not from the scrabble board? By suggesting how you want it done you might disqualify the best solution. By not disclosing the data you tie our hands. By not telling us where you want the result you pretty much disable our capabilities. For me personally, I need to see which parts of my earlier recommendations where incorporated in your project. I need to see the project's current state. I think that is true for other willing helpers, too.
Variatus (rep: 4889) Aug 13, '19 at 1:30 am
I guess you want to build a tool to display the unplayed tiles in a game of scrabble. You know there are 100 tiles and what their distribution is.   
When you select you seven tiles you can see there are 93 yet to play and what they are. You do not know what tiles the other players have so you have no way to know which are still in the bag.
So as the game progresses tiles are played. These played tiles plus your seven will tell you what the tiles yet to be played consists of. 
Personally, I would do al this with macros. A reset button to clear the game.
I pick button to show the letters I have on my stand. A play button to enter the letters being played A display to show the unplayed letters - possibly a couple of columns with the letter and number.
Is this what you want?
k1w1sm (rep: 197) Aug 13, '19 at 4:42 pm
Thank you for joining in this discussion. I'm afraid I am new to forums and not sure what etiquette I need to follow, if any. I assume you have access to the file I uploaded (called forum+ext)? I think a reset button would be dangerous as I could click it by mistake. I have a master file which I use to start all new games from. 

I (anyone in fact) could achieve my goal simply by printing out all 100 tiles and crossing them off as the game progresses. Using Excell is my way of visually keeping track of games and working out possible moves in between playing games. Some games have a 28 hour move rate, all the way up to 30 days. So reminding myself what I thought of playing is a handy tool to have.

If you have seen, or can see my sheet you will see that I have answered my own question in the main board. I think I may have to accept that is the nearest I can get towards the ultimate pretty solution that works everything out automatically for me.

This is where etiquette comes into play. I feel it would be wrong of me to ask several experts to work towards the same goal. Since the other expert is eminently qualiified to provide a solution to my problem, I really don't want to use up your valuable time working on a project that will shortly reach a satisfactory conclusion. Thank you for your interest and please feel free to follow this post if you so wish. Thank you.
MrEMann (rep: 20) Aug 13, '19 at 8:59 pm
I'm on the verge of going for a holiday, perhaps for as long as a week. I shall look into your question when I return. Meanwhile, perhaps k1 can provide the answer. I'll see when I return.
Variatus (rep: 4889) Aug 14, '19 at 7:28 am
Thank you for letting me know. I hope you have a nice holiday.
MrEMann (rep: 20) Aug 14, '19 at 12:30 pm
Add to Discussion

Answers

0
Selected Answer

Here you go 

I have added code on to the Sheet4(data) code sheet.

This will run every time you activate this sheet.

It clears all the values in col D. loops through Col C and copies the non 0 values to Col D.

Hope this is of some value even if only as an example for what you can do.

posted by: k1w1sm (rep: 197) on Wed Aug 14, 2019 at 7:13 pm
Discuss

Discussion

Thank you very much for the work you did on my file. I have tested it and works just as I expected.

Unfortunately (or foolisly) I only sent the sheets named White and Data to keep the file size down. When I added your vba to my working file, which inludes a sheet named 'Black' I only had a complete copy of Data C column as column D. Also, I can view your vba code using Alt+F11 but it doesn't show in View+Macros list. Although I won't need to Run, Edit or Step into the macro, I am just wondering if there's a step or formula I have to add to make it run automatically?

Other than that I am highly delighted to see my project reach fruition. So many thanks to you both for getting me over the final hurdle.
MrEMann (rep: 20) Aug 15, '19 at 6:44 am
I am not what you have done but in the xl I sent you the code runs automatically every time you open the sheet. 
The code is not in the macro list as it is not in a module. I believe it is an activex control. 
If you have copied it to a module then it will not run. It needs to be on a worksheet code page and will then run every time you select got to (activate) that sheet.
You could make this into a module going to a module entering "sub LettersUsed" (or any name you like) and pressing enter. This will auto-fill the "end sub" bit. Next copy the code between "Private Sub Worksheet_Activate()" and "End Sub" into this module.
So having typed all that out I was trying to think of why it would not run and the only thing that springs to mind is that "events" may have been disabled. (Application.EnableEvents = False )

   
k1w1sm (rep: 197) Aug 15, '19 at 4:45 pm
I am so sorry I cannot seem to make myself clear. Firstly, I am delighted with the work you did on the files I sent to you. That is working perfectly. Now I will try to explain what I have done since.

I added a sheet called 'Black'.
As your code didn't appear to specify the 'Data' sheet by name I assumed it would work with anything, whatever it is called.
So I renamed 'Data' to 'Wdata' and checked it still woked ok with 'White' sheet.
I then added a sheet called 'Bdata' and this is where the problem lies.
I copied Wdata columns A,B,C and D into Bdata, but only columns A,B and C function correctly. Column D does not update at all.

I think the easiest thing from your point of view is if you can see my whole workbook so that you can make the changes needed to the 'Bdata' sheet. The file size is 118kb. So unless you say that is too large I will attach the full version, which will be called 'Test'.

Failing that, perhaps  it will be simpler if you can send the vba code updated to work on both 'Wdata' and 'Bdata' simultaneously?

I hope that makes things clearer and I aplogise for all I have done to make this more confusing than it needed to be. Thank you once again.
MrEMann (rep: 20) Aug 16, '19 at 12:42 pm
Great, that was perfectly clear. It is I that have failed to make myself clear. I have tried to introduce some new techniques for you with very little explanation. I am sorry. However, looking at what you have managed so far, I am sure it is well within your grasp
First off you did well to find the code with the Alt f11. What didn't notice is that it is on the worksheet code sheet rather than in the modules sheet.
Usually, the macro editor displays the sheet names on the left. If you double click on any of these you will see the code associated with that sheet. Because the control I used only runs when the sheet is activated I did not need to reverence the sheet name. This explains why it still worked after you change the sheets name. Looks like we both fluked that. So to get your new sheet to build your column D you simply need to copy all the code and past it onto the code sheet for your Bdata sheet. That should do the trick. I may have not use the correct names for thees objects as I an at home, being Saturday, and I do not have access to excel. Don or Variatus  will be able to give the correct technical names I am sure. 
 As an aside, this is not best practice to have the same code in 2 locations as it would mean that should it need to be maintained at some later date it would need to be done twice. That would be an issue for anther question should you need it.
If you are still having problems then, certainly, send the workbook and I will fix it. 
K1w1
k1w1sm (rep: 197) Aug 16, '19 at 8:01 pm
Thank you once again. I have now realised I had to insert your vba code into both data sheets (white and black) for the vba to work. It is now functioning brilliantly. Thanks to both of you for all that you have done.
MrEMann (rep: 20) Aug 17, '19 at 1:45 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login