Find value of every nonblank that's to the right of a blank, repeating along a range

sbawnh

New Member
Joined
Feb 25, 2019
Messages
33
ABCDEFGHIJK
16/10/20186/29/20188/2/201812/31/2018
2

<tbody>
</tbody>

Hi,

Is it possible to show the value of B1, D1, and I1 laid out maybe in columns L:X.

This is every cell to the right of one or many blanks.
 
Last edited:

Some videos you may like

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,313
Office Version
  1. 2010
Platform
  1. Windows
I am not sure why you want to put formulas out to Column X given that the maximum number of columns that will ever be needed is 9 (assuming Column I is the last cell that will be filled) which means you can stop at Column T. With that said, put this array-entered** formula in cell L1 and copy it across to cell T1...

=IFERROR(SMALL(IF(($A1:$H1="")*($B1:$I1<>""),$B1:$I1),COLUMNS($B:B)),"")

**Commit this formula using CTRL+SHIFT+ENTER and not just Enter by itself
 

Watch MrExcel Video

Forum statistics

Threads
1,109,033
Messages
5,526,373
Members
409,697
Latest member
christopherlewis1620

This Week's Hot Topics

Top