Row indexing formula to only display non-blank cell values

[Platinum3x]

In range B4:M4 some cells contain values and some are blank. I am trying to write a formula that will omit the blank values and just give me the nonblank values. The code below returns the FIRST nonblank value, but I can't seem to figure out how to drag it across a row to get the others. I want to ensure that the numbers stay in the same order (so no largest/smallest formulas), but the blank cell values need to be removed.

This would then need to be repeated for (B5:M5, B6:M6, ETC).

Please Login or Register  to view this content.

Any help in modifying my formula above would be much appreciated!

[ChemistB]

Try the following Arrayed formula*

=INDEX($B4:$M4,SMALL(IF($B4:$M4<>"", COLUMN($B4:$M4)-1),COLUMNS($A$1:A$1)))

This will not change your order. I am assuming your new list will also be in a single row across columns

*...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.

[Platinum3x]

Thanks for the quick reply, your formula worked perfectly! I kept messing with the COLUMN(S) formula(s), but I just couldn't make it happen.