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)

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]Home Team[/TD]
[TD="align: center"]Away Team[/TD]
[TD="align: center"]Game outcome home[/TD]
[TD="align: center"]Game outcome away[/TD]
[TD="align: center"]Consecutive home W/L[/TD]
[TD="align: center"]Consecutive away W/L[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD]Team1[/TD]
[TD][/TD]
[TD="align: center"]W[/TD]
[TD="align: center"]L[/TD]
[TD="align: center"]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD][/TD]
[TD]Team1[/TD]
[TD="align: center"]L[/TD]
[TD="align: center"]W[/TD]
[TD][/TD]
[TD="align: center"]2[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD]Team1[/TD]
[TD][/TD]
[TD="align: center"]L[/TD]
[TD="align: center"]W[/TD]
[TD="align: center"]-1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD]Team1[/TD]
[TD][/TD]
[TD="align: center"]L[/TD]
[TD="align: center"]W[/TD]
[TD="align: center"]-2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD][/TD]
[TD]Team1[/TD]
[TD="align: center"]W[/TD]
[TD="align: center"]L[/TD]
[TD][/TD]
[TD="align: center"]-3[/TD]
[/TR]
</tbody>[/TABLE]


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.