Re: Vlookup For 2 Column Criteria
just try the formula he put up there, it gives the result you're looking for
Re: Vlookup For 2 Column Criteria
just try the formula he put up there, it gives the result you're looking for
Re: Vlookup To Return Second Occurance Of String
nevermind, I figured it out, thanks
Re: Vlookup To Return Second Occurance Of String
anybody have any ideas?
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
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:
Re: automatic population from one table to another
ok, here a sample, I need a formula to put in A2 of Sheet2 and copy down so that just the property numbers from Sheet1 are shown
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
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
Re: array function alternative
here's the sample
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
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: 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?
Re: date formula for extracting unique dates
ok, that works, now I just need to figure out how to replace the other array formulas I have in this workbook
Re: Download data from this webpage in a table form
ok, maybe this is a dumb question, but how do I do that, I'm fairly new to vba
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
Re: date formula for extracting unique dates
thank you
Re: date formula for extracting unique dates
yes, there are more than one with a certain date
and I was hoping it could all be done with just one formula, but I'm starting to see I will need more, that's fine
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