Hi Yvon, hope everything is fine.
Ok, let me try to rephrase your question to make sure that I understand it correctly.
There are about 940+ names in NHL Salaries, 650+ names in fwd-def, and 60+ names in goalies.
You want a macro with a button in say fwd-defs that would do two separate comparisons:
1- one between NHL Salaries names and fwd-defs names, which will highlight in red any names in column A of fwd-defs that have a match in NHL Salaries.
2- the second comparison between NHL Salaries names and goalies names, which will highlight in red any names in column A of goalies that have a match in NHL Salaries.
What about the names that were already highlighted in red from a previous comparison but no longer have matching names in NHL Salaries, do you also want the highlight of these names to be removed?
Good morning,
All is fine down East and was offered a position within the org on the west coast. We are looking at it as our three kids and grandchildren are located in British Columbia and Alberta respective. As they say in the business decision and more decision...
As far your queries goes: Query 1 - Yes/no, as I have room to add up to 999. The red cells would only identify those names in the "fwds-defs" who are not listed in the "NHL Salaries" sheet.
As far query 2 goes, same as # 1. As far your last paragraph goes, yes if feasible.
2 similar macros with the same purpose on 2 different sheets...
Thanks
Yvon
The new move must be very exciting. Well good luck.
Go ahead and do your things, I don't think I will need any more clarification. Will post the solution as soon as I have finished..
Thanks - Here in Canada moving to let's say an Eatern Province, then the Real Estates is much cheaper than moving to the West Coast. At least with this added feature, it will further enhance and facilitate my work as everything else's is done through my web site.
On another topic, "perhaps" you recalled that I wasn't to sure how to handle the data for two identical player surnames? Unfortunately, I have that issue now where "Alexandre Picard" is there twice one being a forward and the other being a defencemen. That being said, I think the only way I can make sure the stats are both downloaded is to seperate the "fwds-defs" sheet into one for fwds and a new one for def. The reason, I have contacted Yahoo, and you were right there's an hidden {serial number} code that differs players with identical names.
I will look at it another time as it isn't a major issue now...
Later
Yvon,
Here is a draft version that is meant to display the status of the macro operation so that you can see what's going on for testing purposes. Please check the correctness of the highlighted non-matching names as some names although they appear to be matches but they may actually be spotted as non matches because of the presence of leading space in some names.
http://wikisend.com/download/961748/DASH_Regular_Season_Activities_08-09a.xls
When we finish with the testing I will add the other part for the Goalies, and hide the macro status viewing.
John,
Excellent work - It is the desire answer to what this question was all about. I rather check those cells in red vice every single ones everyday.
So have you completed the testing and found that it is working correctly, or do you need some more time to do so, before I go on with the other part?
Oops, sorry John, I thought I did.... yes, it woks just fine and following verification, I can confirm it is and in few cases, I didn't typed the name properly in the NHL Salaries. In addition, I noticed that Yahoo changed the spelling on some others from last year data. This added feature will erase any possibility of errors.
Quick query - Will the macro go down column A of both sheets as long there are names as possibilities exists throughout the year that new names will be downloaded from Yahoo.
Ok, here we go:
http://wikisend.com/download/916418/DASH_Regular_Season_Activities_08-09b.xls
You can have as many names as you want in all 3 sheets. The macro will look for all the names in the lists no matter how long each list is.
Let me know if it needs anything else.
BS Degree in Engineering.
25 years experience in computer programming and troubleshooting. Planning & Construction Management
Just one word John,
Outstanding! I like the way you did it by one macro that does both the "fwds-defs" and "goalies" sheets.
Once again, I truly appreciate the work and solutions... With the time zone, I am sure you want to go for supper already...
Thank you and with the days ahead I will be in a better position to provide a more accurate feedbacks...
Thank you very much Yvon. A pleasure to work with you.
Have a great rest of the weekend.
With this added feature, it took me just a few minutes to scoop 9 additional names worthy of retention where in the old way took quite longer to do. Including within the nine were four players with typo errors, thus displaying negative stats.
Glad to hear that.
Yvon, If you need any minor adjustments or fine tuning just let me know.
Hi John,
Can you give me a hint here as I want to change the look of the last button to look the same as all others. I have activated the 3D toolbar and the options appears to be all disable?
I will peruse the help option.... That latest work is just great !!!
Hi Yvon,
Go to the sheet that has the button you like, right click on it ans choose Copy. Then go to the fwds-def sheet and press Ctrl + V to paste it there. Move the button to the desired location, then right click on it and choose Assign Macro, and choose Find_NonMatch and click OK. Right click again on the button and choose Edit Text to rename it.
Finally right click on the original button and click the Delete key.
That should do it. Let me know if you need ,me to do it for you.
It worked on the backup file and will try again with live data Tuesday morning as there are no games tonight. It sure wasn't easy to find it under the "help" option.
Perhaps, a future writer in the making here as your step by step instructions were easy and clear.
Good, and make sure you keep these instructions for future use.
Have a good night.
Good evening John,
Can I seek a small/minor clarification with the latest macro? If so, I will forward the file?
The minor issue is when I run the macro it automatically opens the file I received back from you. Therefore, on the bottom tool bar, it displays both files.
Sorry for the trouble, but I think I found and solved the problem and it laid with the file name within the macro file. I will run the macro Thursday with fresh overnight data and go from there.
The macro in question does not have any reference to any file names, so I am not sure I understand how or where you found a file name within the macro.
Anyhow, if the problem has been solved then I guess we should not worry about it. Let me know if you need anything else.
If not, all the best.
Good day John,
Just so you are aware, I've just posted a work related Excel question something to do with Pie Charts?