Need to count blank cells in a column, with a twist

Hello, and thank you in advance for any help you can offer. I have a spreadsheet that has 36 columns and 6000+ rows. Each column has a mix of blanks and content, which is an X. See example of the pattern below, column A. I want to run the COUNTBLANK function through the entire column A so that it assigns a value in column B, next to the content it relates to. Line B4, B9, and B11 would have results, in the example below. I want to be able to apply a formula the entire worksheet, all 36 content columns, and over 6000 rows, so that I can obtain the results quickly by filling down, across, etc. I suspect a loop might be in order, but I have no clue. Currently, I have to run the COUNTBLANK between one X and another, repeating that tediously over and over again. I would be forever grateful for help with this. -- Mike

---A----------------------------B
1 X
2 blank
3 blank
4 X--------------------Result is the number of blanks between this X and the X above it (which would be 2)
5 Blank
6 Blank
7 Blank
8 Blank
9 X--------------------Result is the number of blanks between this X and the X above it (which would be 4)
10 Blank
11 X-------------------Result is the number of blanks between this X and the X above it (which would be 1)
12 Blank

Thanks, Rschwar23. I tried out that formula but I received error messages. Sorry for anything that was confusing. To make it simple, I attached a screen capture image of the worksheet. Basically, I want an easy way to quickly count the blanks between the X's and show the results.

fill in the blanks.jpg

Attached now is a sample. The desired results are highlighted in yellow. The number is the amount of blank spaces between the X and the X above tthat X, as you go down the column.

Using the file you supplied, put this formula into Cell B2

Please Login or Register  to view this content.

you can then copy it down all rows, and also copy it across to the other columns you need

but with the amount of data you have from previous draws, it might be worth looking at the method you use to populate this sheet from the historical data you have of all Fantasy 5 draws. So instead of it placing the X it could put the number you wanted instead (as in number of draws the number was last seen).

Try this:

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.