Need programming help for last 6 matches in excel

[Pepen]

Hi guys. I've been following this forum for quite some time now, and due to an assignment at my university, I'm given the task of trying to make a rating system for 1x2. Sorry if this question is too offtopic, but I'll need the macro to start my system (: I've decided to use available data from the PL at http://football-data.co.uk/englandm.php just to make a template of the final "system".. The only problem so far is, that I'm not exactly a genius when it comes to programming in excel (only done some basic C++, and very little VBA). Is there anybody who can help me with a macro that finds the last 6 matches for both of the involved teams? For example a round 10 match: Man Utd vs Everton Here I'd like a macro that identifies each team and find the last 6 matches they've been involved in, so I can use e.g. the accumulated point/goals or any such thing. Thanks in advance for any help :)

[gloryhunters]

Re: Need programming help for last 6 matches in excel Well I think you need to create several arrays in VBA. Say hometeam, homegoals etc. and a counter Then go to your data sheet and go down the home team column and step down the each column asking if the team is homegoals. if yes then increase your counter by 1 and all your variables you need homegoals(counter) is what is in that row. Do this as you go down the list until you hit a blank cell. Your last 6 matches will be homegoals(counter), homegoals(counter-1), etc. I have been doing this for my spreadbets and so just look at one match at a time and do this within Excel without VBA. Basically you make 2 columns to the left of the data set and have a formula like: =IF(E22=Goals!$C$2,ROUND(A21+1,0),A21+0.00001) this is for line 22. The first bit asks if the team is as you ask for in a different sheet. If yes then add one and round otherwise add a very small bit. On another sheet you can have a row full of numbers (say 1 to 40) and next to each one say: =IF(A6