Index, Match then what?

[MVogel]

Something tells me this should be easy but I am struggling. I would like to use formulas for this. I have a spreadsheet with rows of data where there are many empty cells intermixed with cells with text. I don't want my users to have to scroll across hundreds of empty cells to see the few that have text in them. I need a formula that will find the first column with data in it and return the value, then in the next cell, find the next column with data in it, return the value, then in the next cell find the next column with data in it, return the value etc.

 

[p45cal]

In your sample file, select the cells B2:F2 and array-enter (ctrl+Shift+Enter, not just Enter) the following formula into them in one go:

=IFERROR(INDEX(2:2,0,SMALL(IF(LEN($H2:$S2)>0,COLUMN($H2:$S2)),COLUMN()-1)),"")

then copy them downwards as far as you need.

 

[MVogel]

In your sample file, select the cells B2:F2 and array-enter (ctrl+Shift+Enter, not just Enter) the following formula into them in one go:

=IFERROR(INDEX(2:2,0,SMALL(IF(LEN($H2:$S2)>0,COLUMN($H2:$S2)),COLUMN()-1)),"")

then copy them downwards as far as you need.

Perfect! Thanks, I never would have gotten that.