Solved

Display from One table to another using abbreviations

Posted on 2014-09-15
16
149 Views
Last Modified: 2014-09-23
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
0
Comment
Question by:Pedrov664
  • 7
  • 6
  • 3
16 Comments
 
LVL 24

Expert Comment

by:Phillip Burton
Comment Utility
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
 
LVL 27

Expert Comment

by:Glenn Ray
Comment Utility
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
 
LVL 27

Accepted Solution

by:
Glenn Ray earned 500 total points
Comment Utility
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
 

Author Comment

by:Pedrov664
Comment Utility
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
 
LVL 27

Expert Comment

by:Glenn Ray
Comment Utility
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
 
LVL 24

Expert Comment

by:Phillip Burton
Comment Utility
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
 

Author Comment

by:Pedrov664
Comment Utility
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
 
LVL 24

Expert Comment

by:Phillip Burton
Comment Utility
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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 

Author Comment

by:Pedrov664
Comment Utility
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
 
LVL 27

Expert Comment

by:Glenn Ray
Comment Utility
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
 
LVL 27

Expert Comment

by:Glenn Ray
Comment Utility
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
 

Author Comment

by:Pedrov664
Comment Utility
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
 

Assisted Solution

by:Pedrov664
Pedrov664 earned 0 total points
Comment Utility
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
 
LVL 27

Assisted Solution

by:Glenn Ray
Glenn Ray earned 500 total points
Comment Utility
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
 

Author Comment

by:Pedrov664
Comment Utility
Glen,

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

Pedro
0
 

Author Closing Comment

by:Pedrov664
Comment Utility
Thank you for all of your hard work!
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now