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
Pedrov664Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
You need to copy in column J having previously defined the column as Text. Excel thinks it is a date, which is also converted as a 5-digit number around the 40,000 mark.

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.
0
Glenn RayExcel VBA DeveloperCommented:
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
0
Glenn RayExcel VBA DeveloperCommented:
To get back to your original question of setting up a dynamic lookup against the regular season standings table:

1) Create a lookup table that equates the team abbreviations (ex., "WSH" = "Washington") to the team names shown in the standings.  For example, I'd set up a table on a separate sheet and and call it "ABBList"

2) Insert the following formula in cell D6 of your "Data" sheet and copy down:
=OFFSET(Standings!$C$5,MATCH(VLOOKUP($C6,ABBList,2,FALSE),Standings!$C$5:$C$42,0)-1,MATCH(D$5,Standings!$C$5:$AD$5,0)-1)

It performs a VLOOKUP on the abbrevation, returning the full name.  Then it uses the MATCH function to determine how many rows down to find that team.  Then it uses another MATCH function to locate the category in row 5 ("STRK").  All of this is embedded in an OFFSET function which uses these two results to locate the value.

See the attached file - with my AL West teams added for good measure! :-)

Regards,
-Glenn

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.
EE-ExtractSTRK-mod.xlsx
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Pedrov664Author Commented:
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.
0
Glenn RayExcel VBA DeveloperCommented:
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
0
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
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
0
Pedrov664Author Commented:
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?
0
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
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.
0
Pedrov664Author Commented:
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.
0
Glenn RayExcel VBA DeveloperCommented:
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).
0
Glenn RayExcel VBA DeveloperCommented:
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.
0
Pedrov664Author Commented:
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.
0
Pedrov664Author Commented:
Glen,

Forgot to mention, I am currently using the =TEXT(J6,"m-d") formula and manually doing this for every cell. If there's a way for me to do this automatically after I paste the new tables in that would be great!

Right now I use A5:A41 to hold the pasted data then put the formula in J5:41 and I get the results I want in all 'J' cells. If there's a better way let me know.
0
Glenn RayExcel VBA DeveloperCommented:
Text Issue/Browser:  I found that when copying/pasting with IE9, I can't just paste the tabular data; it pastes as HTML only (including formatting and hyperlinks).  This also wipes out any cell formatting that could force the win-loss columns to text instead of being reset as "General" (and therefore, dates).  But doing this gets ALL the table columns, including those not visible on the first screen.

When I copy/paste using Google Chrome, I can paste using Unicode Text and get unformatted columns (preserving text-defined columns), BUT I only get the first screen of the table (nothing past the "Next game" column).

"Clinching tags":  If you don't mind manually removing them every time you load a page, then I guess that's okay.  You could run a macro to strip them out automatically:
Sub Remove_Tags()
    Dim rng As Range
    Dim cl As Object
    
    Sheets("Standings").Select
    Set rng = Range("C6:C42")
    For Each cl In rng
        If InStr(1, cl.Value, "-", vbTextCompare) > 1 Then
            cl.Value = Mid(cl.Value, InStr(1, cl.Value, "-", vbTextCompare) + 1, 30)
        End If
    Next cl
End Sub

Open in new window


And speaking of macros, if you're going to use IE9 - which allows the full table extract - then I'd use another macro to convert all the dates to text:
Sub Convert_Win_Loss()
    Dim rng As Range
    Dim cl As Object
    
    Sheets("Standings").Select
    Set rng = Range("J6:J42,L6:M42,P6:W42,Z6:Z42")
    For Each cl In rng
        If cl.Text <> cl.Value Then 'change format
            cl.NumberFormat = "@"
            cl.Value = Format(cl.Value, "m-d")
        End If
    Next cl
End Sub

Open in new window


I've attached an example file with the macros and sample data sheet.

Regards,
-Glenn
EE-ExtractSTRK-mod.xlsm
0
Pedrov664Author Commented:
Glen,

Your ie9 Macro works as intended. Thanks for all your hard work.

Pedro
0
Pedrov664Author Commented:
Thank you for all of your hard work!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.