Pedro
asked on
Display from One table to another using abbreviations
Hi everyone,
Using the attached table data I run into the following issues:
1) I would like to extract data from the Standings tab into the Data tab using the abbreviaiton of names. I do not seem to be able to come up with a solution. The data to be extracted is the data located in the STRK (i.e. K column) into the corresponding team in the Data tab)
2) In the Standings tab, the "L10" label is supposed to read as ##-## but instead it either reads as date format like J6 or a five number sequence and I do not know where that number sequence comes from.
The original data for the "J" Column reads ##-## (i.e. "7-3").
Thanks,
P.S. I have not formatted the Standings tab as a table to give flexibility in problem solving. Format as you see fit, as long as it works as intended.
P.P.S. The original data is copied from the following web page,
http://mlb.mlb.com/mlb/standings/?tcid=mm_mlb_standings
If there is a way for Excel to update directly from this website in the same format it would be desired but not required.
EE-ExtractSTRK.xlsx
Using the attached table data I run into the following issues:
1) I would like to extract data from the Standings tab into the Data tab using the abbreviaiton of names. I do not seem to be able to come up with a solution. The data to be extracted is the data located in the STRK (i.e. K column) into the corresponding team in the Data tab)
2) In the Standings tab, the "L10" label is supposed to read as ##-## but instead it either reads as date format like J6 or a five number sequence and I do not know where that number sequence comes from.
The original data for the "J" Column reads ##-## (i.e. "7-3").
Thanks,
P.S. I have not formatted the Standings tab as a table to give flexibility in problem solving. Format as you see fit, as long as it works as intended.
P.P.S. The original data is copied from the following web page,
http://mlb.mlb.com/mlb/standings/?tcid=mm_mlb_standings
If there is a way for Excel to update directly from this website in the same format it would be desired but not required.
EE-ExtractSTRK.xlsx
What? No love for the AL West? :-)
Unfortunately, you won't be able to set up a data connection to the MLB standings page because there is no formal "table" displayed there that you can identify and then set up a connection. You have to see an arrow indicator (black arrow inside a yellow square) to note any tables to import/connect.
If you could identify a table, there is an option (top right of web import, "Options" button) to disable date recognition which would allow you to see the last 10 game record without it trying to convert to a date (ex., "7-3" instead of July 3).
-Glenn
Unfortunately, you won't be able to set up a data connection to the MLB standings page because there is no formal "table" displayed there that you can identify and then set up a connection. You have to see an arrow indicator (black arrow inside a yellow square) to note any tables to import/connect.
If you could identify a table, there is an option (top right of web import, "Options" button) to disable date recognition which would allow you to see the last 10 game record without it trying to convert to a date (ex., "7-3" instead of July 3).
-Glenn
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Glen,
Sorry about the AL West teams. It was an oversight on copy and paste. That is why I'd like to make automatic update. But...
That aside, It looks like you did an excellent job of getting the desired result. However, Will pasting data into this table change anything you've done? i.e. Will I get the same results when I update the "Standings" table?
Phillip,
tried the import from web, but as someone else pointed out there is no formal table displayed.
Sorry about the AL West teams. It was an oversight on copy and paste. That is why I'd like to make automatic update. But...
That aside, It looks like you did an excellent job of getting the desired result. However, Will pasting data into this table change anything you've done? i.e. Will I get the same results when I update the "Standings" table?
Phillip,
tried the import from web, but as someone else pointed out there is no formal table displayed.
So long as the table in the "Standings" sheet remains anchored in cell C5 (i.e., upper left corner), there's no reason why the formulas won't continue to work, even if the column order or content changes to the right.
-Glenn
-Glenn
Well, you know what they say. If you can't get it right first time, cheat!
Please find attached a file with the solution.
I have used http://www.cbssports.com/mlb/standings as the source data (so that's the cheating), and the vlookup command as I indicated earlier.
Hope the attached helps. If you enable Data connections, it will auto-update in the background when you open the file.
EE-ExtractSTRK.xlsx
Please find attached a file with the solution.
I have used http://www.cbssports.com/mlb/standings as the source data (so that's the cheating), and the vlookup command as I indicated earlier.
Hope the attached helps. If you enable Data connections, it will auto-update in the background when you open the file.
EE-ExtractSTRK.xlsx
ASKER
Glen,
Updating the Standings table seems to work fine, except the L10 column reverts back to date format. How can I get it to stop that? What did you do that I can do get it back to normal. (i.e. 7-3)
Phillip,
When I open the file I see the Standings Table data I want begins at 'C224', Is this the way it worsk? Does all that garbage from the web page need to be there?
Updating the Standings table seems to work fine, except the L10 column reverts back to date format. How can I get it to stop that? What did you do that I can do get it back to normal. (i.e. 7-3)
Phillip,
When I open the file I see the Standings Table data I want begins at 'C224', Is this the way it worsk? Does all that garbage from the web page need to be there?
I'm afraid so. It imports the entire webpage, so you just ignore it and use it in your front page - or use a lot of formulas to recreate the table - or find another html source which has it formatted as a table.
ASKER
Glen,
I looked at your solution above and do not see where or how it is implemented in the file. Please help!
Here is your solution:
PS To fix the L10 values (or any win-loss value) that converts to a date, you could insert a temporary helper column that converts it to text like so:
=TEXT(J6,"m-d")
This is for the L10 value. Then you copy and replace the original values with the results of this formula.
Please tell me where this formula goes and how I get it to apply to all L10 values.
I looked at your solution above and do not see where or how it is implemented in the file. Please help!
Here is your solution:
PS To fix the L10 values (or any win-loss value) that converts to a date, you could insert a temporary helper column that converts it to text like so:
=TEXT(J6,"m-d")
This is for the L10 value. Then you copy and replace the original values with the results of this formula.
Please tell me where this formula goes and how I get it to apply to all L10 values.
Actually, the fix for the win-loss issue (converting to dates) is to format all those columns that display wins-losses as "Text". So, when you paste new data in, they will remain in the original format and not convert to dates.
However, you've got another issue altogether that just became apparent last night when the Angels clinched the playoffs. The MLB website adds a tag in front of the team names like "w-" or "x-" or "y-" or "z-" when the teams clinch wildcard, playoffs. divisions, or best record. That will foul up any look up you have for the abbreviations to the team names and must be addressed. Frankly, I haven't figured out how to modify the indexing functions to account for this, especially since the tag will likely change (certainly for the Angels).
However, you've got another issue altogether that just became apparent last night when the Angels clinched the playoffs. The MLB website adds a tag in front of the team names like "w-" or "x-" or "y-" or "z-" when the teams clinch wildcard, playoffs. divisions, or best record. That will foul up any look up you have for the abbreviations to the team names and must be addressed. Frankly, I haven't figured out how to modify the indexing functions to account for this, especially since the tag will likely change (certainly for the Angels).
By the way, what browser are you using to capture the data and how are you pasting it? I get completely different results with IE9 and Google Chrome.
ASKER
Glen,
Tried the Convert to text by using right click - format cells - number - click text click OK and what I get is ...
L10
41883
41764
41705
41794
41735
I like the other way you suggested because it actually works the way I want it to. Those numbers are just garble to me and are unusable.
As for "The MLB website adds a tag in front of the team names like "w-" or "x-" or "y-" or "z-" when the teams clinch wildcard, playoffs. divisions, or best record" issue is solved by me going in manually and deleting the extra characters. That I am no concerned about since it only happens once a year and only at the end.
BTW, your division was the first to get that ranking. Go LAA!
I am using IE9 and I get supportable results.
Tried the Convert to text by using right click - format cells - number - click text click OK and what I get is ...
L10
41883
41764
41705
41794
41735
I like the other way you suggested because it actually works the way I want it to. Those numbers are just garble to me and are unusable.
As for "The MLB website adds a tag in front of the team names like "w-" or "x-" or "y-" or "z-" when the teams clinch wildcard, playoffs. divisions, or best record" issue is solved by me going in manually and deleting the extra characters. That I am no concerned about since it only happens once a year and only at the end.
BTW, your division was the first to get that ranking. Go LAA!
I am using IE9 and I get supportable results.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Glen,
Your ie9 Macro works as intended. Thanks for all your hard work.
Pedro
Your ie9 Macro works as intended. Thanks for all your hard work.
Pedro
ASKER
Thank you for all of your hard work!
For the STRK column, you need to include an extra column (maybe a hidden column) with the full version, and use VLOOKUP on the full version of the name.
For the update directly from the website, have a look at Data - Get External Data - from web, and copy the address from your web browser into the address bar.