Removing the gaps in numbers using formulae

[Singemonkey]

I've got some formulae that extract numbers from rows of text. These need to act as index numbers for a bunch of other stuff. But only some rows have numbers in them the rest are blank. So my number column that extracts the text goes something like this:

1




2




2a

3

etc.

Now I want people to be able to paste the text into the appropriate column. Once the numbers are then extracted for them automatically, on a summary sheet, I need to see:

1
2
2a
3

like so. But I don't want my users to have to do any filtering. I want the numbers to appear by formulae. Now I've easily got it to do it with vb - a counter goes down, picks up a number, deposits it in the column on the other sheet which iterates its own counter, and so on until it runs out of numbers.

But I also don't want to have to have embedded macros in the sheet either, so that people feel more free about sharing and using the final book.

Is there any way that my summary sheet can have in that number column a formula that will retrieve each number in sequence, i.e.:

Row1: formula1 will retrieve: 1
Row2: formula2 will retrieve: 2
Row3: formula3 will retrieve: 2a
Row4: formula4 will retrieve: 3

The ultimate object is that the end user plugs the text in. My text formulae find and extract the numbers into an adjacent cell - if index numbers are found with that row.

On the summary sheet, the formulas I'm asking about retrieve the index numbers found by the text formulae and lay them down in a column, in sequence, ignoring blanks.

The end user doesn't have to do anything at all.

Any thoughts, or am I just being stupid?

[squiggler47]

try this

=IF(ISERROR(INDEX($B$2:$B$14,SMALL(ROW($B$2:$B$14)-1+(10000000*ISBLANK($B$2:$B$14)),ROW()-1),1)),"",INDEX($B$2:$B$14,SMALL(ROW($B$2:$B$14)-1+(10000000*ISBLANK($B$2:$B$14)),ROW()-1),1))

NOTE the 2 red 1's are the row number the first formula is in -1

so if you start in A2, 1 is correct, if you start in b10 this should be 9

confirm with CSE then copy down

[Singemonkey]

Thanks a bunch. I'll try this out and let you know how it goes. There's stuff in here that I had no idea about.

[DonkeyOte]

It might be worth posting a sample - a slightly different take:

Summary!A2:
=IF(ROWS(A$2:A2)>COUNTIF(Data!$B$2:$B$14,"?*"),"",INDEX(Data!$B$2:$B$14,SMALL(IF(Data!$B$2:$B$14<>"",ROW(Data!$B$2:$B$14)-ROW(Data!$B$2)+1),ROWS(A$2:A2))))
confirmed with CTRL + SHIFT + ENTER (enter alone will not suffice)
copied down

above assumes that Data!B2:B14 are text strings which is implied...

[martindwilson]

avoiding an array like that id go for a helper column this can be placed on a completely different sheet if you don't want to see it

[DonkeyOte]

avoiding an array like that id go for a helper column...

I love to see people championing helper cells so good call !

That said... ... if the number of non-blanks is limited then the approach I posted should not prove too expensive given the Array is only performed for as many times as there are values to retrieve.