Hi,
I am looking to create a macro in VBA that add two new columns to a table of data and fills each cell with a count created from data in another column. So it essentially counts a sequence.
For example in the below i have added the Counter Column which first increments a counter on the occurence of the player in the winner column. It then resets when the Score value = 0-0 and starts again.
The Sequence column would then pick out the highest number in each sequence.
The reason i want to do this is so that i can then run a COUNTIFS on the table to see how many times a player has won points in a row - such as 3pts in a row = 2, 2pts in a row = 3, 1pt in a row = 7.
I hope that makes sense. I'm struggling to figure out the best way to do it, but it needs to be in VBA. I can run it on either the raw table of data or on an actual excel table version as i have access to both.
Thanks,
Simon
I am looking to create a macro in VBA that add two new columns to a table of data and fills each cell with a count created from data in another column. So it essentially counts a sequence.
For example in the below i have added the Counter Column which first increments a counter on the occurence of the player in the winner column. It then resets when the Score value = 0-0 and starts again.
The Sequence column would then pick out the highest number in each sequence.
The reason i want to do this is so that i can then run a COUNTIFS on the table to see how many times a player has won points in a row - such as 3pts in a row = 2, 2pts in a row = 3, 1pt in a row = 7.
I hope that makes sense. I'm struggling to figure out the best way to do it, but it needs to be in VBA. I can run it on either the raw table of data or on an actual excel table version as i have access to both.
Thanks,
Simon
Score | Winner | Counter | Sequences |
0-0 | Player 1 | 1 | |
15-0 | Player 1 | 2 | |
30-0 | Player 1 | 3 | |
40-0 | Player 1 | 4 | 4 |
0-0 | Player 2 | 1 | |
0-15 | Player 2 | 2 | 2 |
0-30 | Player 1 | 1 | |
15-30 | Player 1 | 2 | |
30-30 | Player 1 | 3 | |
40-30 | Player 1 | 4 | 4 |
0-0 | Player 1 | 1 | |
15-0 | Player 1 | 2 | 2 |
15-15 | Player 2 | 1 |