Transferring info from 1column on sheet2 to sheet1

rogcar75 · #1 08-19-2014, 08:25 PM

Testsheet.xlsx

I am trying to get the info in column B in sheet2 to find the correct name in sheet1 and transfer that info in column I in sheet1.
Any help would be Greatly Appreciated.
Thanks

excelledsoftware · #2 08-19-2014, 10:52 PM

Your data has ascii characters instead of spaces in some of the cells which makes look ups kind of weird. The formula below worked for every cell except for Dee Gordon who for some example has a differently formatted name. If you can change this it will work no problem. Also it appeared that certain names included OF rather than RF so the IF statement accounts for that.

Place the following code in Sheet1 cell I4 and autofill down.

Code:

=VLOOKUP(IF(RIGHT(E4,1)="F",B4&" "&A4&", "&F4&"*OF",B4&" "&A4&", "&F4&"*"&E4),Sheet2!A:B,2,0)

Let me know if that works out.

Thanks

rogcar75 · #3 08-20-2014, 03:44 AM

Test20Aug2014.xlsx

Ok thanks i tried that formula and it worked on most names, i think my problem might be related to the way i have to input the names in column A.
Ok here is a better way of letting you know what i have to do.

In column A and column B in sheet 2, i copy and paste these names from another site, and this is why the names have OF, RF, LF, 1B, 2B, 3B, SS, etc.
That is the problem i think but i thought the formulas in sheet 2 in columns C, D, E, F, would take of that, but it isn't i guess.
Here is a different sheet that is what it looks like after i paste the names in columns A, B, C.
Also in columns K, L, in sheet 2 is where i paste the info, and then repaste them in columns A, B, C, in sheet 2
I hope this didn't throw everything crazy.
Thanks for your help.

excelledsoftware · #4 08-20-2014, 11:31 PM

Ok here is my suggestion for what you are trying to do. Normally I would write a VBA script to do this but it is very possible to do with formulas as well.

1: I would change where you create the search name into column A on Sheet2. This makes it alot easier to use it for vlookups. You can still easily copy and paste new data in the sheet just start on Column B.
2: I would change your search name formula to the following

Code:

=SUBSTITUTE(LEFT(C2,FIND(",",C2)-1)," ","")

This removes the spaces which have been problematic the only thing to watch out for is duplicate first and last names.
3: Use the following Vlookup formula in Column I for Sheet1

Code:

=VLOOKUP(B4&A4,Sheet2!A:D,4,0)

With these changes this should work except for the names that have the dreaded * in them. This will need to be removed and a Find and Replace can be problematic with removing * because it is considered a wildcard.

If this still isnt quite what you are looking for maybe we should look into writing you a VBA script that will do this task automatically.

Let me know

Thanks

rogcar75 · #5 08-21-2014, 07:57 AM

Ok i tried that also it didn't seem to work either, so i really don't know what to try next, if you want to try a VBA Script, that's fine if you have the time, i really no nothing at all about VBA Scripts.
Thanks for your help.
Rog

Pecoflyer · #6 08-21-2014, 08:30 AM

Quote:

Originally Posted by rogcar75

Ok i tried that also it didn't seem to work either, so i really don't know what to try next, if you want to try a VBA Script, that's fine if you have the time, i really no nothing at all about VBA Scripts.
Thanks for your help.
Rog

Could you kindly tell us what goes wrong?

rogcar75 · #7 08-21-2014, 08:37 AM

With the last post that excelled software left for me to try.
I did what he asked and it just left a #NA in sheet 1, and in sheet 2 it left a #value.
I also thought that i placed the formula in the wrong column, so tried in all columns in sheet 2 with no prevail, nothing really at all shows up, in sheet 1.
Thanks for your interest.
Rog

excelledsoftware · #8 08-22-2014, 09:12 PM

Ok provide me with the link where you copy and paste the information from or the HTML file. Then tell me exactly what you are trying to do with that data. I will look at it and see if it is best to do with formulas or a script.
The html file or webpage you are copying it from is very important since it appears to format spaces differently.

Thanks

rogcar75 · #9 08-23-2014, 07:33 AM

Here is the Link to the web page i copy the top 800 players names by their player rater numbers, then i paste them on a spreadsheet and delete the inside columns, the only columns i want to keep is the Rank Column, Player name Column, and the 2014 column.
After i delete the other columns then i copy the 3 columns and paste in the first 3 columns on the original sheet 2, then the formulas are supposed to find those names on sheet 1 and transfer the player rater numbers in Column I,(Player value column).

Link - http://games.espn.go.com/flb/playerrater

Thanks again for your time and help.
Rog

excelledsoftware · #10 08-23-2014, 02:36 PM

Done. I was feeling really nice. This script will do the following:
It will automatically open up the webpage and put that data into a worksheet. This worksheet will be formatted in a way that will allow you to use a vlookup on your first sheet by just putting the 2 names together. If you have more than 1 URL to do let me know and I will make a small change to the code that will allow you to do as many URLs as you like without ever having to copy and paste the data.

Code:

Option Explicit
Sub PlayerRaterParser()

  'Extracts the data from a set of webpages and exports the data out

  Dim ie As Object, NameText As Object, NameDesText As Object
  Dim TableText As Object, v As Variant, LastRow As Long
  Dim rw As Long, Col As Long, LastCol As Long, DsExists As Boolean
  Dim DuplicateCheck As Long, DuplicateIssue As Boolean, DupString As String
  Dim CheckString As String, NewString As String
  Dim wb As Workbook, ws As Worksheet, rws As Worksheet, uws As Worksheet
  
    Set wb = ThisWorkbook

    'Make sure the scrape sheet exists
    Application.DisplayAlerts = False
      For Each v In wb.Worksheets
        If v.Name = "Data Scrape" Then
          v.Range("A:Z").ClearContents
          v.Range("1:1").Font.Bold = True
          DsExists = True
        End If
      Next v
    Application.DisplayAlerts = True
    
      If DsExists = False Then
        Sheets.Add
        ActiveSheet.Name = "Data Scrape"
      End If
      
    Set ws = wb.Worksheets("Data Scrape")
    ws.Activate
    'Create the IE object to use
    Set ie = CreateObject("InternetExplorer.application")
    ie.Visible = True
    
    ie.Navigate "http://games.espn.go.com/flb/playerrater"
      Do While ie.ReadyState <> 4 'Make sure page loads
        DoEvents
      Loop
    
    'grab the needed HTML
    Set NameText = ie.Document.GetElementsByClassName("flexpop")
    Set NameDesText = ie.Document.GetElementsByClassName("playertablePlayerName")
    Set TableText = ie.Document.GetElementsByClassName("playertableData")
    
    rw = 2
      For Each v In NameText
        ws.Range("A" & rw).Value = v.innertext
        If v.innertext <> "" Then
          rw = rw + 1
       End If
      Next v
    rw = 2
      For Each v In NameDesText
        If v.innertext <> "" Then
          ws.Range("B" & rw).Value = Replace(v.innertext, ws.Range("A" & rw).Value & ", ", "")
          rw = rw + 1
        End If
      Next v
    'Fill in the table data
    LastCol = 15
    
    Col = 4
    rw = 1
    
      For Each v In TableText
        ws.Cells(rw, Col).Value = v.innertext
        If Col = LastCol Then
          Col = 4
          rw = rw + 1
        Else
          Col = Col + 1
        End If
      Next v
            
    ws.Range("A1").Value = "Player Name"
    ws.Range("B1").Value = "Team POS"
            
      'Clean up
      ie.Quit
      Set ie = Nothing
      
    'Create a LookupString
      LastRow = ws.Range("A50000").End(xlUp).Row
      
      ws.Range("C:C").Font.ColorIndex = 5
      ws.Range("C1").Value = "LookupString"
      
    'Remove the space
      For rw = 2 To LastRow
        CheckString = ws.Range("A" & rw).Value
        NewString = Mid(CheckString, InStr(1, CheckString, " ") + 1)
        NewString = NewString & Mid(CheckString, 1, InStr(1, CheckString, " ") - 1)
        ws.Range("C" & rw).Value = NewString
      Next rw
      
      'Check for any duplicates
      
      For rw = 2 To LastRow
        DuplicateCheck = WorksheetFunction.CountIf(ws.Range("C:C"), ws.Range("C" & rw).Value)
        If DuplicateCheck > 1 Then
          DupString = DupString & "Row " & rw
        End If
      Next rw
      
      If DupString <> "" Then
        MsgBox "You have duplicates on the following row(s): " & DupString & vbLf & _
        "Your Vlookup will not work unless you change these values"
      End If

End Sub

Thanks

excelledsoftware · #11 08-23-2014, 03:48 PM

I Read this thread all the way through and it was mentioned that you copy and paste the top 800 players. I have modified the code to do this. Now the Vlookup will NOT work for everyone of them because there are some duplicates. However I know that with the amount of time I have saved you with copying and pasting these that you can go and fix 4 duplicates out of the entire list each time.

Run this code and either watch the fun or walk away it takes some time to process but you will not have to copy and paste the data again, you can just run this code each time you want to update your worksheet. If you dont want the inputbox to come up at the end change the GiveFormula variable to false. (this is the 14th line of code near the top.)

Enjoy!

Code:

Option Explicit
Sub PlayerRaterParser()

  'Extracts the data from a set of webpages and exports the data out

  Dim ie As Object, NameText As Object, NameDesText As Object
  Dim TableText As Object, v As Variant, LastRow As Long, x As Integer
  Dim rw As Long, Col As Long, LastCol As Long, DsExists As Boolean
  Dim DuplicateCheck As Long, DuplicateIssue As Boolean, DupString As String
  Dim CheckString As String, NewString As String, UrlArray(0 To 15) As String
  Dim StartRow As Long, Complete As String, GiveFormula As Boolean
  Dim wb As Workbook, ws As Worksheet, rws As Worksheet, uws As Worksheet
  
  GiveFormula = True
  
    Set wb = ThisWorkbook

    'Make sure the scrape sheet exists
    Application.DisplayAlerts = False
      For Each v In wb.Worksheets
        If v.Name = "Data Scrape" Then
          v.Range("A1:O1000").ClearContents
          v.Range("A1:O1000").ClearFomats
          v.Range("1:2").Font.Bold = True
          DsExists = True
        End If
      Next v
    
      If DsExists = False Then
        Sheets.Add
        ActiveSheet.Name = "Data Scrape"
      End If
      
    Set ws = wb.Worksheets("Data Scrape")
    ws.Range("1:2").Font.Bold = True
    ws.Activate

    
    UrlArray(0) = "http://games.espn.go.com/flb/playerrater"
    UrlArray(1) = "http://games.espn.go.com/flb/playerrater?startIndex=50"
    UrlArray(2) = "http://games.espn.go.com/flb/playerrater?startIndex=100"
    UrlArray(3) = "http://games.espn.go.com/flb/playerrater?startIndex=150"
    UrlArray(4) = "http://games.espn.go.com/flb/playerrater?startIndex=200"
    UrlArray(5) = "http://games.espn.go.com/flb/playerrater?startIndex=250"
    UrlArray(6) = "http://games.espn.go.com/flb/playerrater?startIndex=300"
    UrlArray(7) = "http://games.espn.go.com/flb/playerrater?startIndex=350"
    UrlArray(8) = "http://games.espn.go.com/flb/playerrater?startIndex=400"
    UrlArray(9) = "http://games.espn.go.com/flb/playerrater?startIndex=450"
    UrlArray(10) = "http://games.espn.go.com/flb/playerrater?startIndex=500"
    UrlArray(11) = "http://games.espn.go.com/flb/playerrater?startIndex=550"
    UrlArray(12) = "http://games.espn.go.com/flb/playerrater?startIndex=600"
    UrlArray(13) = "http://games.espn.go.com/flb/playerrater?startIndex=650"
    UrlArray(14) = "http://games.espn.go.com/flb/playerrater?startIndex=700"
    UrlArray(15) = "http://games.espn.go.com/flb/playerrater?startIndex=750"

      

    'Create the IE object to use
    Set ie = CreateObject("InternetExplorer.application")
    ie.Visible = True
    
    For x = 0 To 15
      StartRow = ws.Range("A50000").End(xlUp).Row + 1
      ie.Navigate UrlArray(x)
        Do While ie.ReadyState <> 4 'Make sure page loads
          DoEvents
        Loop
      
      'grab the needed HTML
      Set NameText = ie.Document.GetElementsByClassName("flexpop")
      Set NameDesText = ie.Document.GetElementsByClassName("playertablePlayerName")
      Set TableText = ie.Document.GetElementsByClassName("playertableData")
      
      rw = StartRow + 1
      ws.Range("A" & StartRow).Select
        For Each v In NameText
          ws.Range("A" & rw).Value = v.innertext
          If v.innertext <> "" Then
            rw = rw + 1
         End If
        Next v
      rw = StartRow + 1
        For Each v In NameDesText
          If v.innertext <> "" Then
            ws.Range("B" & rw).Value = Replace(v.innertext, ws.Range("A" & rw).Value & ", ", "")
            rw = rw + 1
          End If
        Next v
      'Fill in the table data
      LastCol = 15
      
      Col = 4
      rw = StartRow
      
        For Each v In TableText
            ws.Cells(rw, Col).Value = v.innertext
            If Col = LastCol Then
              Col = 4
              rw = rw + 1
            Else
              Col = Col + 1
            End If
        Next v
      Next x
      
      'All data scraped
            

            
      'Clean up
      ie.Quit
      Set ie = Nothing
      
    'Create a LookupString
      LastRow = ws.Range("A50000").End(xlUp).Row
      
      ws.Range("C:C").Font.ColorIndex = 5
      
      
      'Clear out the extra headers
      ws.Range("A1").EntireRow.Delete
      For rw = 2 To LastRow
        ws.Range("C" & rw).Select
        If ws.Range("D" & rw).Value = "RNK" Then
          ws.Range("D" & rw).EntireRow.Delete
          rw = rw - 1
        End If
      Next rw
      ws.Range("A1").Value = "Player Name"
      ws.Range("B1").Value = "Team POS"
      ws.Range("C1").Value = "LookupString"
      LastRow = ws.Range("A50000").End(xlUp).Row
    'Remove the space
      For rw = 2 To LastRow
        CheckString = ws.Range("A" & rw).Value
        NewString = Mid(CheckString, InStr(1, CheckString, " ") + 1)
        NewString = NewString & Mid(CheckString, 1, InStr(1, CheckString, " ") - 1)
        ws.Range("C" & rw).Value = NewString
      Next rw
      
      'Check for any duplicates
      
      For rw = 2 To LastRow
        DuplicateCheck = WorksheetFunction.CountIf(ws.Range("C:C"), ws.Range("C" & rw).Value)
        If DuplicateCheck > 1 Then
          DupString = DupString & "Row " & rw & ", "
          ws.Range("A" & rw & ":C" & rw).Interior.Color = vbRed
        End If
      Next rw
      
      'Create new names
      For rw = 2 To LastRow
        If ws.Range("C" & rw).Interior.Color = vbRed Then
          ws.Range("C" & rw).Value = ws.Range("C" & rw).Value & "_" & rw
        End If
      Next rw
      
      ws.range("A1").Select
      
      If DupString <> "" Then
        MsgBox "You have duplicates on the following row(s): " & DupString & vbLf & _
        "These cells have been highlighted red and the name has been changed." & vbLf & _
        "These will not lookup and will need to be entered manually."
      End If
      
      If GiveFormula = True Then
        Complete = InputBox("Please copy and paste this formula into your worksheet now.", _
        Default:="=VLOOKUP(A3&B3,'Data Scrape'!C:O,13,0)")
      End If
      

End Sub

rogcar75 · #12 08-25-2014, 05:07 AM

Thank You very much for your time and Help, i will get this going very soon, just trying to find time to install the script.
Question 1 - where do i paste the code?, as i know nothing about VBA, so this will be a challenge.
Question 2 - So each time i want to update the sheet i just run this code, and its final, except for the duplicates, which by the way, i have no problem changing a few names, lol.
Thanks again for your help, i will do some research on VBA Scripts.
Rog

excelledsoftware · #13 08-25-2014, 06:54 AM

No problem rogcar.

To get this running in your worksheet do the following.

Until you are comfortable using the VBA editor you should only have the 1 workbook open.

1. Open your workbook
2. Press ALT + F11 to open the visual basic editor
3. Go to Insert, Module
4. In that Module paste the code in there.
5. Close the VBA editor
6. Go to your worksheet and make any autoshape.
7. Right click on the auto shape and click on assign macro
8. Select the macro and press OK.
9. SAVE YOUR WORKBOOK and make a backup before you click on this shape now.
10. Click the shape and let the code run a new worksheet will automatically be created which you can go and freeze the top row after the code runs so you can search out your duplicates (There were only 2 the last time I ran it).
11. Post back here with your results and any questions.

Thanks

rogcar75 · #14 08-25-2014, 07:27 AM

Ok Thanks.
I got that all done, and the results are 4 duplicates, and the results on the sheet 1 is that the numbers are showing on players names 1 row below where they actually go with the correct name.
I pasted the formula it gave me in Column I, Row 4, where the first row names are.
Also how do I change the duplicate names, just manually in the Scrape sheet or Sheet 1, just don't want to make a mistake, lol.

Edit - I did change the formula to 1 row lower and the names are in the correct row, will the #NA, stay in the rows of names that don't have updates, not a problem, just asking, Thanks.
I have to say this amazing, yeah excited!

excelledsoftware · #15 08-25-2014, 09:21 AM

No problem. Good job fixing the formula. The duplicates need to be fixed
On sheet 1 or manually entered in. You should not have to
Run the formulas again just the script and everything should
Update. later today I will fix the script to include the teams
For each duplicate name so you only need to change duplicate
Formulas once.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Newbie to excel for starters, needing to transfer info from sheet2 to universe sheet.	rogcar75	Excel	0	08-12-2014 07:21 AM
How to populate cells in Sheet2 with Data Source query using cell data from Sheet1	bobznkazoo	Excel	2	03-27-2014 11:14 AM
split text files to worksheets (sheet1,sheet2,sheet3 soon sheet 25)	gsrikanth	Excel Programming	1	03-22-2012 11:04 PM
arrows remain between task bars, but predecessor info disappears from task info	Antares	Project	1	12-14-2011 09:19 AM
How to merge two cells from sheet1 to one cell in sheet2 in next sheet	KIM SOLIS	Excel	6	10-30-2011 11:14 PM