Sort column data separated by blanks into other column

Originally Posted by Glenn Kennedy

If you do not need backward compatability with old Excel versions...

In B2, copied down as far as the last row:

=IF(ISNUMBER(A2),1+B1,0)

In D2, use this array formula, copied across and down:

=IFERROR(INDEX($A:$A,SMALL(IF(B1:$B$317=ROWS($D$1:$D1),ROW(B1:$B$317)),COLUMNS($A:A))),"")

Array Formulae are a little different from ordinary formulae in that they MUST be confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER.

You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly brackets yourself - it won't work...

The issue I am facing now is, if I don't have 7 entries in the first group (ex by deleting 986 in A32 ); the index array formula shifts the last row towards the left (filled in red). Is there any way to record them as a group they belong.