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?

	A	B	C	D	E	F	I
1		Apr	May	June		Orders
2	Item1	25	0	30		25 (from row 2)
3	Item2	0	19	0		30 (from row 2)
4	Item3	35	0	30		19 (from row 3)
5						35 (from row 4)
						30 (from row 4)

<tbody>
</tbody>

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").

	A	B	C	D	E	F	I
1		Apr	May	June		Orders
2	Item1	25	0	30		25 (formula starts here)
3	Item2	0	19	0		0
4	Item3	35	0	30		30
5						0
						19
						0
						35
						0
						30

<tbody>
</tbody>

 

[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