Need Help Setting up Spreadsheet for Dynamic Web Query

[realhiphop]

I’m working on a baseball spreadsheet that uses webqueries to look up baseball information. It’s a Dynamic Web Query so the output changes rows depending on the player that is looked up.



I’m hoping someone can help me figure out a way to set up the columns on a spreadsheet so that the rows and columns match this website: http://www.fangraphs.com/statsplits.aspx?playerid=13431&position=P&season=2016



I’ve attached my spreadsheet so that you can see what I mean.

 

[realhiphop]

Anyone have any thoughts?

 

[realhiphop]

To elaborate. I think a version of Index/Match would work.

For more details, the process on this sheet is as follows:
1. Player ID is typing into the Input tab by me
2. Web Query in this file extracts data from website
3. Data would then need to be cleaned up to fit the Extract Sheet Column Headers, and Row Headers.

Item 3, is where I am having the issue. The problem is that the column headers are throughout the data that is extracted from the web query, and they might reside in different rows if a different player ID is input.

I've cleaned up the file and re-uploaded.

 

[Chihiro]

Hmm, looking at the data. Stats would change by position, no? As in Pitcher would have different data set than 3rd Base for example...

Or are you pulling Pitcher data only?

As things are currently laid out, there are multiple duplicate for Col1 & 2 on each table. So simple Index, Match won't work.

Will table order and info you wish to extract always same? (i.e. Standard then Advanced etc, and pulling info from Standard Handedness & Monthly as well as Advanced Handedness & Monthly).

 

[realhiphop]

The stats are only going to be specific to pitchers. The order of the data will always be the same. The only thing that might change would be what row specific data is in.

 

[Chihiro]

Formula method would require use of Volatile function (the ones I can think of at least). And would slow down analysis down stream.

If you are not opposed to VBA solution. Below will do what you are looking for. This code assumes table structure will remain consistent (just rows where Standard and Advanced Tables start changes).

See attached demo as well.

Sub test()
Dim eWs As Worksheet
Dim c As Range, cRange As Range
Dim lCol As Long

Set eWs = Worksheets("Extract Sheet")
eWs.Cells.ClearContents

With Worksheets("Raw P Splits")
    Set c = .Columns(1).Find("Standard")
    lCol = .Cells(c.Row + 1, Columns.Count).End(xlToLeft).Column
    Set cRange = c.Offset(1, 1).Resize(1, lCol - 1)
    cRange.Offset(0, 1).Copy eWs.Cells(2, 2)
    cRange.Offset(1, 0).Resize(2).Copy eWs.Cells(3, 1)
    cRange.Offset(16, 0).Resize(2).Copy eWs.Cells(5, 1)
  
    Set c = .Columns(1).Find("Advanced")
    lCol = .Cells(c.Row + 1, Columns.Count).End(xlToLeft).Column
    Set cRange = c.Offset(1, 1).Resize(1, lCol - 1)
    cRange.Offset(0, 1).Copy eWs.Cells(8, 2)
    cRange.Offset(1, 0).Resize(2).Copy eWs.Cells(9, 1)
    cRange.Offset(16, 0).Resize(2).Copy eWs.Cells(11, 1)
End With

End Sub