filter and transpose non empty cells

[ExcelNewbie2020]

i have this data below,

if i search the TAG #, its information will get filtered and will rearrange from single rows to 3 columns (including the column header) and ignored empty cells. please see the expected result. many thanks

Book4
	A	B	C	D	E	F	G	H	I
1	RAW DATA
2	TAG #	DOG	QTY	CAT	QTY	MOUSE	QTY	HORSE	QTY
3	1003	2	10000			6	5000	8	2500
4	1004	10	20000	12	20000	14	20000	16	20000
5	1005			20	30000	22	30000	24	30000
6
7
8
9
10
11
12
13
14	EXPECTED RESULT
15
16	TAG #
17	1003	DOG	2	10000
18		MOUSE	6	2500
19		HORSE	8	2500
20
Sheet5

 

[Fluff]

How about

Fluff.xlsm
	A	B	C	D	E	F	G	H	I
1	RAW DATA
2	TAG #	DOG	QTY	CAT	QTY	MOUSE	QTY	HORSE	QTY
3	1003	2	10000			6	5000	8	2500
4	1004	10	20000	12	20000	14	20000	16	20000
5	1005			20	30000	22	30000	24	30000
6
7
8
9
10
11
12
13
14	EXPECTED RESULT
15
16	TAG #
17	1003	DOG	2	10000
18		MOUSE	6	5000
19		HORSE	8	2500
Data

Cell Formulas
Range	Formula
B17:D19	B17	=LET(f,FILTER(B3:I5,A3:A5=A17),HSTACK(TOCOL(CHOOSECOLS(IF(f<>"",B2:I2,1/0),1,3,5,7),2,),WRAPROWS(TOCOL(f,1),2)))
Dynamic array formulas.

 

[ExcelNewbie2020]

1

 

[Fluff]

If they contain 0 why did you say to ignore empty cells?

 

[ExcelNewbie2020]

If they contain 0 why did you say to ignore empty cells?

yes my bad.. first i thought only empty cells need to be ignored. when i run the formula to my actual data, there are zero values and is not good in the eyes

 

[Fluff]

Try

=LET(f,FILTER(B3:I5,A3:A5=A17),HSTACK(TOCOL(CHOOSECOLS(IF(f<>0,B2:I2,1/0),1,3,5,7),2,),WRAPROWS(TOCOL(IF(f<>0,f,1/0),2),2)))

 

[ExcelNewbie2020]

and it worx like a charm.. thank you very much...

 

[Fluff]

You're welcome & thanks for the feedback.