Posts by elfudge35

  • Vlookup To Return Second Occurrence Of String

    I searched on the site but couldn't find an answer to my problem, I have multiple automatically updating data sets and I need to extract certain data to perform calculations on, this is the format of the data

    Code
    [FONT="Courier New"]
SPLIT AB  H 2B 3B
HOME  20  6  1  0
AWAY  20  7  2  0
SPLIT  R HR RBI
HOME   2  0  1
AWAY   3  1  4
[/FONT]


    basically, what I need is to extract the data for home and away, and put them each on one line, no problem if this is always the format, but there are other lines of data that I don't need thrown in there at times which shift the data up or down


    a simple vlookup will extract the first occurance of home, but I need another formula to extract the second occurance so I can have them all on one line and have the data as:


    Code
    [FONT="Courier New"]
SPLIT AB  H 2B 3B  R HR RBI
HOME  20  6  1  0  2  0  1
AWAY  20  7  2  0  3  1  4
[/FONT]

  • automatic population from one table to another

    I have a worksheet that has numerous amounts of data on claim and non-claim properties
    I need to have a proceeds worksheet that automatically populates the property numbers of only the claim properties and then I can perform vlookup to extract the necessary data I need for each of the property numbers


    any help would be great

  • array function alternative

    Re: array function alternative


    I replaced
    SUM(IF($A2=Data!$A$1:$A$37,IF(I$1=Data!$B$1:$B$37,Data!$C$1:$C$37)))
    with
    SUMPRODUCT(SUM(($A2=Data!$A$2:$A$37)*(I$1=Data!$B$2:$B$37)*(Data!$C$2:$C$37)))


    thanks


    but it is still taking forever to calculate, I don't see any more array functions, maybe it's just too big to be slow

  • array function alternative

    I have an array function that performs exactly what I want it to do
    the problem is that I need that same functionality in over 55,000 cells and it is making my workbook slow and big as it must calculate every time I do anything


    I'm enclosing a sample layout of what I need the function to do, I need it in the yellow cells, starting with I2


    basically I want the function to perform a SUMIF based on two criteria in Sheet2, pool number from column A and date from the row above (ie cell I1)


    this is what my formula would look like when applied to the constraints in my sample workbook, I know it's longer than it should be, but it gets the job done, I need to do the same thing without an array function


    {=IF(I$1="",0,IF(I1<40000,IF($B2<=I$1,SUM(IF($A2=Data!$A$1:$A$44,IF(I$1=Data!$B$1:$B$703,Data!$C$1:$C$703))),IF(ISTEXT($B2)=TRUE,I1-C2,I1)),IF(ISTEXT($B2)=TRUE,I1-C2,I1)))}


    any help would be greatly appreciated, thanks

  • Download data from this webpage in a table form

    Re: Download data from this webpage in a table form


    ok, I found a place I could get the data from, just 1 question, am I only able to get one web query per worksheet? I was hoping I could have the query for every player I need to look up on one sheet and then I could use vlookup on another sheet to extract the data I need

  • date formula for extracting unique dates

    Re: date formula for extracting unique dates


    here is what I want, but I need a formula for cell I2 that I can copy to all of the yellow cells that will do what is demonstrated


    I want the first cell of a pool to have the sum from the other sheet (Sheet2 in this example) and then every following total line will subtract the total that I generated in the green cells, once that sum is exhausted it should remain zero until the next pool that also contains that date, and then it does the dsum again


    can anybody help me with this?

  • Download data from this webpage in a table form

    Re: Download data from this webpage in a table form


    well, the espn site does change the address with the tabs, ie splits?playerId=5470 and gamelog?playerId=5470


    somehow I got it to work once, but every other time it just gives me that same error message, it's not subscription content, so I don't understand why they would block it, I usually just copy and paste, but it took my time up to over 3 hours a day and I just couldn't spend that much time on this

  • date formula for extracting unique dates

    Re: date formula for extracting unique dates


    OK, moving on, I need to take those dates and use them to calculate a sum from another table so at the beginning of every pool I have the sum for that date in the first column of the pool, then I need that sum subtracted from with another formula I already have in another column all the way down until it is zero, then when the next pool starts I need it to again take the sum for that pool


    I don't see how to get around this without an array formula, I already have the entire sheet filled up with columns and cannot add two criteria columns for each date for the dsum to work, any thoughts?

  • Download data from this webpage in a table form

    Re: Download data from this webpage in a table form


    well, I tried that, but it didn't work, said:
    This Web query returned no data. To modify the query, click OK, click the name of the external data range in the name box on the formula bar, and then click Edit Query on the External Data toolbar.


    then I tried another site: http://sports.espn.go.com/mlb/players/splits?playerId=5470
    but this said:
    Unable to open http://sports.espn.go.com/mlb/players/splits?playerId=5470


    any thoughts?

  • date formula for extracting unique dates

    Re: date formula for extracting unique dates


    ok, problem with that, there are some dates that repeat again, but not all the dates that are in the other pools are in the first pool


    like this:
    pool 1
    2/21/03
    2/21/03 total
    4/1/03
    4/1/03 total
    7/1/03
    7/1/03 total
    pool 2
    2/21/03
    2/21/03 total
    5/16/03
    5/16/03 total


    then the 5/16 would come after the 7/1/03

  • Download data from this webpage in a table form

    Re: Download data from this webpage in a table form


    I'm sorry that I can't provide an answer for the original question, but I have a question for Erik


    I would like to do a very similar thing that you perform in that I would like to extract data from a website about baseball players in order to perform statistical analysis on it


    each player has their own id that allows you to go right to their page
    ie http://www.sportsline.com/mlb/players/playerpage/7855 for Pedro Martinez


    would you know how to set something up to automatically extract, say the last 5 games from http://www.sportsline.com/mlb/…player/gamelogs/2005/7855


    I would like to have a worksheet set up for numerous players and I would then use vlookup to specify the players I need and perform my analysis


    any help would be great, thanks