Excel Formula: Skip blank columns in row (A2:D2) and transpose into a vertical list

[haruspication]

Hi all.

I'm looking for a formula to skip blank columns starting in row 2 (horizontal list) and transpose only those cells with values into a single column list. Then, I'd like to move down to row 3 and continue to add only cells with values to the bottom of the new vertical list, and so on.

Any suggestions?

[TABLE="class: grid, width: 600"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]I[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD][/TD]
[TD]Apr[/TD]
[TD]May[/TD]
[TD]June[/TD]
[TD][/TD]
[TD]Orders[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Item1[/TD]
[TD]25[/TD]
[TD]0[/TD]
[TD]30[/TD]
[TD][/TD]
[TD]25 (from row 2)[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Item2[/TD]
[TD]0[/TD]
[TD]19[/TD]
[TD]0[/TD]
[TD][/TD]
[TD]30 (from row 2)[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Item3[/TD]
[TD]35[/TD]
[TD]0[/TD]
[TD]30[/TD]
[TD][/TD]
[TD]19 (from row 3)[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]35 (from row 4)[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]30 (from row 4)[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


I have figured out a good formula for skipping blank rows, but not for skipping blank columns across an array of many rows.

 

[haruspication]

Any ideas on how to make the below formula skip cells that have a blank/zero value?

So far I have a formula in F2 that will transpose each row in range B2:D4 into a single column:
=INDEX($B$2:$D$4,1+INT((ROWS($B$2:B2)-1)/COLUMNS($B$2:$D$4)),1+MOD(ROWS($B$2:B2)-1,COLUMNS($B$2:$D$4)))

Each cell within the range B2:D4 contains a formula (even if they show "0").


[TABLE="class: grid, width: 600"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]I[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD]Apr[/TD]
[TD]May[/TD]
[TD]June[/TD]
[TD][/TD]
[TD]Orders[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Item1[/TD]
[TD]25[/TD]
[TD]0[/TD]
[TD]30[/TD]
[TD][/TD]
[TD]25 (formula starts here)[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Item2[/TD]
[TD]0[/TD]
[TD]19[/TD]
[TD]0[/TD]
[TD][/TD]
[TD]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Item3[/TD]
[TD]35[/TD]
[TD]0[/TD]
[TD]30[/TD]
[TD][/TD]
[TD]30[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]19[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]35[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]30[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Rijnsent]

Hi haruspication,

I'm not wizzkidding around to squeeze it all into 1 formula, but this does the trick for me:

G3: =ROW()
H3: =MOD(G3;3)
I3: =(G3-H3)/3
J3: =OFFSET($B$1;I3;H3)
K3: =COUNTIF($J$3:J3;">0")
L3: =OFFSET($J$1;MATCH(G3-2;$K:$K;0)-1;0)

And then drag down... It does the trick, but as said, you could probably squeeze it into way less formulae.

Ciao,

Koen

 

[haruspication]

Hi Koen (Rijnsent),

Thanks for your suggestion, however I noticed where you have a semi-colon in your formulas, there should be a comma.

I combined your G3, H3, I3, J3 formulas into one formula (J3) to limit helper columns. Any suggestions on how to combine them all into 1 single formula?


J3: =OFFSET($B$1,(ROW()-MOD(ROW(),3))/3,MOD(ROW(),3))
K3: =COUNTIF($J$3:J3,">0")
L3: =OFFSET($J$1,MATCH(ROW()-2,$K:$K,0)-1,0)

 

[Rijnsent]

Hi Haruspication,

welcome to international settings ... T is only a , separating parts of a formula if you have a . (dot) for a decimal symbol... And as my PC has European settings, I have a , (comma) for decimal separator and a ; in formulas . Having said that: combining the formulas into 1 column will require a rather complex array formula. Check out e.g. this manual: Extract a List of Values Filtered by Criteria with Sub-Arrays in Excel
I'm not that good at array formulas and I find them way too complex: I have a hard time reading the formula, let alone changing it or giving it to somebody less skilled in Excel to work with... Therefore I myself use quite often extra/helper columns: that makes the sheet at least understandable.

Cheers,

Koen