I just did some further tests before closing it down, and found there is a problem with the original formula I posted
It wasn't coping with balls for the first draw in your data, i.e. on row 2

This formula now does a check for the first row of data

=IF(AND(ROW()=2,A2="x"),0,IF(A2="","",ROW()-1-COUNTIF(A$2:A2,"x")-SUM(B1:B$2)))
Again, as long as you enter this formula into cell B2 initially, you will be able to copy it down and across without having to make any adjustments to it


Here is a sample of about the first 50 draws for balls 1 to 10

1 1B 2 2B 3 3B 4 4B 5 5B 6 6B 7 7B 8 8B 9 9B 10 10B
X 0
X 2 X 2
X 3 X 3 X 3
X 4
X 1 X 2
X 0 X 5
X 4 X 0
X 8 X 0 X 3
X 3
X 2 X 3 X 0 X 2
X 12
X 4
X 10
X 1 X 9
X 12 X 4 X 4
X 0
X 5 X 2 X 18
X 7
X 4 X 3
X 2
X 10 X 0
X 0
X 10 X 4 X 8
X 6 X 7 X 0
X 1
X 7 X 0 X 1
X 5 X 2
X 4
X 2
X 10
X 5
X 3 X 6
X 6
X 19 X 3
X 5
X 11 X 13 X 2
X 0 X 12 X 4
X 3
X 1
X 2 X 4
X 4 X 28
X 1
X 11 X 0