Count the number of blank cells until next filled cell is found

[Bookender]

Okay, so. I have a sheet with data in all sorts of random cells. However, I only need the information from a few specific ones, so, when I import those columns, I get data that's all over the place.

1	Fish
2		Spoon
3
4		Drink
5
6	Tree
7	Shoe		Four
8			Eight
9
10		Night

<TBODY>
</TBODY>

I would like to get this data 'gathered' so that it takes up as little space as possible, while still keeping the different rows apart. And I want to do this with an INDEX command (no arrays, no VBA). I want to do this by having a helper column that does a simple check, and adds one to the number above itself if any of the cells in a row isn't blank; otherwise (and this is where I have trouble), I want it to add the number of blank cells until next non-blank is encountered. So the row with Fish would be 1, the row with Shoe would be 2, and then the row after that would say 4, as it counts the blank cell before Drink - and then the sequence would continue with 6, 7, 8, 10. In order. As demonstrated:

1

2

4

6

7

8

10

<TBODY>
</TBODY>

Anyone got any ideas for how to do that counting? (my apologies if this exact question has already been answered, but an intial search did not yield anything I could use)

 

[oldbrewer]

				helpercol1	helpercol2
1	Fish			1	1
2		Spoon		1	2
3				0
4		Drink		1	3
5				0
6	Tree			1	4
7	Shoe		Four	1	5
8			Eight	1	6
9				0
10		Night		1	7
1	Fish	0	0
2	0	Spoon	0
3	0	Drink	0
4	Tree	0	0
5	Shoe	0	Four
6	0	0	Eight
7	0	Night	0
					would this approach be acceptable

<colgroup><col width="64" span="6" style="width:48pt"> </colgroup><tbody>
</tbody>

 

[Bookender]

That would work just fine, oldbrewer. Though I admit that I am a bit at a loss as to how to get it to end up the way you demonstrate.

 

[oldbrewer]

the first helper column puts a 1 if there is anything in the row, the second helper column adds up the 1's sequentially.
The new table uses offset / match function - will post the formulas shortly.

 

[oldbrewer]

			helpercol1	helpercol2
1	Fish			1	1
2		Spoon		1	2
3				0	0
4		Drink		1	3
5				0	0
6	Tree			1	4
7	Shoe		Four	1	5
8			Eight	1	6
9				0	0
10		Night		1	7
	########
1	Fish
2		Spoon
3		Drink
4	Tree
5	Shoe		Four
6			Eight
7		Night
first cell under helpercol2 heading (which is E2)
=IF(AND(B2="",C2="",D2=""),0,1)
cell to the right of that (which is F2)
=IF(E2=0,"",1)
cell below that (which is F3)
=IF(E3=0,0,1+MAX($F$2:F2))
cell below ######## (which is B14)
=IF(OFFSET($F$1,MATCH($A14,$F$2:$F$11,0),-4)=0,"",OFFSET($F$1,MATCH($A14,$F$2:$F$11,0),-4))
this is copied across then change the two -4's to -3 and -2
then copy down

<colgroup><col span="10"></colgroup><tbody>
</tbody>

 

[Bookender]

Hm. Well, following the instructions you gave precisely did.. not work. At all. My guess is there is a typo somewhere. But, I'm afraid that I am sufficiently unfamiliar with OFFSET to understand its function - could you be kind and provide an explanation of how your code works?

 

[oldbrewer]

I copied and pasted the formulas from my test spreadsheet, so check your end for typos please.
Offset works by starting at a reference cell, in this case F1, then it goes down the required number of cells, then along the required number of cells.
So =offset(F1, 5,2) would return the value of cell H6.
=offset(F1, 5,-3) would return the value of cell C6

the match function is used here to define how many cells down, looking first for a 1 in column F before going 4 cells to the left to find FISH

 

[Bookender]

Really strange; I've copy-pasted from the thread, so that rules out any typos I could have done. The core data is in B2:D11, correct? With first helper column starting in E2, the second in F2, and then the Offsets going in B14:D25. If that is correct, then the formulas have been inserted as per your instructions...

But, B14:D25 remain blank (and yes, I did make sure that the sheet calculated the formula) - which is what puzzles me. Perhaps it is the fact that you reference $A14 and down, which is a blank column?

 

[oldbrewer]

A14 and down are 1 2 3 4 5 6 7

 

[Bookender]

Ah, problem found and solved. Thank you; the solution works now.