Posts by elfudge35

    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

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

    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:

    [FONT="Courier New"]
    HOME  20  6  1  0  2  0  1
    AWAY  20  7  2  0  3  1  4

    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

    Re: array function alternative

    I replaced


    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

    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


    any help would be greatly appreciated, thanks

    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

    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?

    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

    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?

    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:
    but this said:
    Unable to open

    any thoughts?

    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 total
    4/1/03 total
    7/1/03 total
    pool 2
    2/21/03 total
    5/16/03 total

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

    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 for Pedro Martinez

    would you know how to set something up to automatically extract, say the last 5 games from…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