Formula to pull non 0 cells to a list

[bearcub]

I have a file where I have to copy the numbers from a row format to a column format. Is there a formula to retrieve non zero amounts from a row to column format. Is there a way to filter out the zero amounts from appearing in the list without having to delete them from the file.

In the table below I have customers that have purchased some of services (e.g. New Business, renewals, services, New MultiYear and Renewal MultiYear orders).

I have like the New New to show in Row 1, Renewal - 192,868 in the Row 2, Renewal in 38,215 in Row 3, New 223,892 in Row 4, etc. In other words, the I want the zero values to be ignored and not copied down into the transposed list.

Thank you for your help.

I would have used the Xl2bb tool but it is disabled from my computer. Seems my computer is automatically disabling macros and I don't know now to fix this yet. Just happened this last week.

 

[Peter_SSs]

Is this the sort of thing you are trying to do?

bearcub.xlsm
	A	B	C	D	E	F	G	H	I
1
2		3	1	0	0	2	0		3
3		0	0	2	1	2	3		1
4		0	2	1	0	1	2		2
5		0	0	0	0	0	0		2
6		0	0	3	1	1	2		1
7									2
8									3
9									2
10									1
11									1
12									2
13									3
14									1
15									1
16									2
17
Sheet1

Cell Formulas
Range	Formula
I2:I16	I2	=TEXTSPLIT(TEXTJOIN(" ",1,IF(B2:G6=0,"",B2:G6)),," ")
Dynamic array formulas.

 

[bearcub]

Hi Peter,

No, it isn't sorted. If it needed to be sorted how would that work

I tried your format and totally amazing. I just saved myself about 30 minutes of work.

Another issue I have is i need to put the associated customer name next to value. Is that possible

 

[Fluff]

No sorting needed (I think you misunderstood what Peter was saying), how about

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J
1
2	A	3	1	0	0	2	0		A	3
3	B	0	0	2	1	2	3		A	1
4	C	0	2	1	0	1	2		A	2
5	D	0	0	0	0	0	0		B	2
6	E	0	0	3	1	1	2		B	1
7									B	2
8									B	3
9									C	2
10									C	1
11									C	1
12									C	2
13									E	3
14									E	1
15									E	1
16									E	2
17
Main

Cell Formulas
Range	Formula
I2:J16	I2	=LET(Data,B2:G6,c,COLUMNS(Data),s,SEQUENCE(c*ROWS(Data),,0),tc,TOCOL(Data),hs,HSTACK(INDEX(A2:A6,INT(s/c)+1),tc),FILTER(hs,tc<>0))
Dynamic array formulas.

 

[bearcub]

Yes, you're right. I didn't read his comment correctly, sorry.

Your answer is fantastic. How you do folks come up with all these great formulas - it's like magic (as Bob Umlas was wont to say).

 

[Peter_SSs]

Another issue I have is i need to put the associated customer name next to value. Is that possible

Another way

bearcub.xlsm
	A	B	C	D	E	F	G	H	I	J
1
2	A	3	1	0	0	2	0		A	3
3	B	0	0	2	1	2	3		A	1
4	C	0	2	1	0	1	2		A	2
5	D	0	0	0	0	0	0		B	2
6	E	0	0	3	1	1	2		B	1
7									B	2
8									B	3
9									C	2
10									C	1
11									C	1
12									C	2
13									E	3
14									E	1
15									E	1
16									E	2
17
Sheet2

Cell Formulas
Range	Formula
I2:J16	I2	=LET(n,B2:G6,tcn,TOCOL(n),FILTER(CHOOSE({1,2},TOCOL(IF(n,A2:A6)),tcn),tcn))
Dynamic array formulas.

 

[bearcub]

Incredible! Thank you both very much. It is fascinating to figure out how each piece of the formula works.

 

[Peter_SSs]

Probably should have used HSTACK instead of CHOOSE ..

bearcub.xlsm
	A	B	C	D	E	F	G	H	I	J
1
2	A	3	1	0	0	2	0		A	3
3	B	0	0	2	1	2	3		A	1
4	C	0	2	1	0	1	2		A	2
5	D	0	0	0	0	0	0		B	2
6	E	0	0	3	1	1	2		B	1
7									B	2
8									B	3
9									C	2
10									C	1
11									C	1
12									C	2
13									E	3
14									E	1
15									E	1
16									E	2
17
Sheet2

Cell Formulas
Range	Formula
I2:J16	I2	=LET(n,B2:G6,tcn,TOCOL(n),FILTER(HSTACK(TOCOL(IF(n,A2:A6)),tcn),tcn))
Dynamic array formulas.

 

[bearcub]

Very cool, thank you again

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.