Look Up with qualifications

[JoshEngleman]

I hope I explain myself correctly. I am trying to figure out a way to select a certain amount of data from a group, based on it's relationship to today's date. My data looks as follows, starting with column headers:


Date Home Away Side Total
1/1/2011 Atlanta Boston -7 180
1/2/2011 Charlotte Dallas +4 199
1/3/2011 Boston New York +1 204
...

It would continue like that. What I am looking to do is only pull data for each listed team for the 5 most recent dates. So if I was on another sheet, and I had Boston in cell A1, I could pull up the information of their 5 most recent games. Ultimately, I would like to weigh the 5 most recent games in Solver in order to create some power rankings, but just being able to seperate out the most recent games would be a huge step.

 

[obiron]

I would suggest that the first thing to do would be to restucture the data

Game# Date Team H/A Side Total
1 1/1/2011 Atlanta H -7 180
1 1/1/2011 Boston A -7 180
2 1/2/2011 Charlotte H +4 199
2 1/2/2011 Dallas A +4 199
3 1/3/2011 Boston H +1 204
3 1/3/2011 New York A +1 204

better still would be one table for games, side and total and another table the game,team and H/A - link the tables using the game #

Game# Date Side Total
1 1/1/2011 -7 180
2 1/2/2011 +4 199
3 1/3/2011 +1 204


Game# Team H/A
1 Atlanta H
1 Boston A
2 Charlotte H
2 Dallas A
3 Boston H
3 New York A

This will make it much easier to analyse the data by team

 

[JoshEngleman]

Ok, if I were to have the two individual tables like you suggested, is there a way to piece that together so I can pull individual teams. For example, if I had a 3rd sheet where I selected a specific team from a drop down list, it would populate some recent games. I was thinking of some combo of Vlookup and Large functions. Is it possible to lookup a certain value, ie a team name, and return the largest game ID number that is next to that team name?

 

[obiron]

looking at the data you are working with I am guessing you are building a spread betting manager..

best way to analyse the data will be with a pivot table. To do this you need to generate a master spreadsheet.

Assuming your teams are in sheet1 and the games are in sheet2 and the game number is in column A in both. In sheet, cell D2 put the following formula next to each team.

=VLOOKUP(A2,sheet2!A:D,2,FALSE)

This will put the game date into sheet1. The VLOOKUP breaks down like this: Lookup the value in A2 (game #) in sheet2 columns A:D and return the value from the 2nd column selected; if you don't find a match then return '#N/A'

You can do the same for the line and the points. Depending on what you are tyring to do, you may want to reverse the line value against the away team.

Copy the formulas down the sheet so that they appear against each row.

You can now create a pivot table (search the board or Google for how to do this). The pivot table will allow you to do the following things:

Select a team and see all their games (or most recent 5)
Select a date and see all the games for the date and the home and away teams

With some clever codes you should also be able to rate and rank teams based on recent performances and give more weight to recent games or wins over teams with a higher ranking; but get the basics working first...

Obiron