Formula/macro to count the number of continuous wins and losses for each subject/team

[smide]

Hello.


In columns A and B (A2:B400) I have a list of all games in one season, home team is in column A and away team in column B. Columns C and D shows game outcome Win or Loss (W/L).


I need a formula (or VBA) to count the number of current win/loss streaks after each game and to place those results in columns E and F. If team is in column A (home team) result (number of consecutive W/L) should be placed in column E, if team is in column B (away team) result should be placed in column F.
* Consecutive number of losses is represented with negative number.
* Two possible game outcomes: Win or Loss (W or L).


example. (only for Team1 here)

	A	B	C	D	E	F
1	Home Team	Away Team	Game outcome home	Game outcome away	Consecutive home W/L	Consecutive away W/L
2	Team1		W	L	1
3		Team1	L	W		2
4
5	Team1		L	W	-1
6
7
8	Team1		L	W	-2
9
10		Team1	W	L		-3

<tbody>
</tbody>

explanation. Team1 won first two games so result in cell F3 after second game is 2, then they lost next three games and result in cell F10 is -3.