Formula to populate a list without blanks.

Please see the attached array formula (Entered with Ctrl Shift Enter)

Example 1.2.xlsx

In F5
=IF(ROWS(A$1:A1)>COUNT($C$5:$C$27),"",INDEX($B$5:$B$27,SMALL(INDEX(($C$5:$C$27<>"")*(ROW($C$5:$C$27)-ROW($C$5)+1),),COUNTBLANK($C$5:$C$27)+ROWS(A$1:A1))))

In G5
=IF(F5="","",SUMIF($B$5:$B$27,F5,$C$5:$C$27))

Copy down

Enter this array formula in F5 and pull to G5 and then down

=IFERROR(INDEX(B:B,SMALL(IF($C$5:$C$27<>"",ROW($C$5:$C$27)),ROWS($F$5:F5))),"")

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

I've been thinking a lot recently about this type of solution, and after several OPs have come back to me saying that their workbooks have practically crashed when calculating a large number of these array formulas, I've now realised that the method used by Ace_XL here is much, much preferable to the standard IFERROR(... construction.

Obviously in this case the number of rows is so small as to make the difference in performance insignificant, but in general it's certainly worth doing: dragged down to several thousand rows' worth, the version with IFERROR will still calculate the array part, whether it's beyond the last "necessary" row or not.

Using the IF clause nullifies these unnecessary calculations for further rows, and so gives a considerable improvement is performance. In fact, better still is to have the row limit calculation in a separate cell somewhere, and reference this in the formula, i.e.:

=IF(ROWS($1:1)>$X$1,...

etc.

The reason I mention it is just in case people are interested and happen to be reading this thread. The more so because the vast majority of solutions proposed along these lines (including until recently mine!) still opt for the (inefficient) IFERROR(... option.

Regards