transpose() function application

[Sumanmathew]

I need it as shown in TABLE 2, the items can vary from project to project. The transpose() array should expand and contract as items increase or decrease wihtout those zero rows being displayed.How to replace those 0 rows? Is it possible that way?

transposeTrial.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z
1			MDB									SDB									CP
2			qnty	1	2	1						qnty	3	1							qnty	1	2	1	4	1
3	tot ac	520	ACC PR	100	150	120				tot ac	520	ACC PR	100	150					tot ac	520	ACC PR	100	150	120	20	5
4	tot pr	6500	U. PR	1000	2000	1500				tot pr	5000	U. PR	1000	2000					tot pr	5000	U.PR	1000	2000	1500	500	50
5		7020	ITEMS	MDB-1	MDB-2	MDB-3					5520	ITEMS	SDB-1	SDB-2						5520	ITEMS	CP-1	CP-2	CP-3	CP-4	CP-5
6
7
8
9
10			SL	ITEMS	QNTY	U.PR	ACC.PR	TOT. PR
11				MDB									TABLE 2
12			1	MDB-1	1	1000	100	1100					SL	ITEMS	QNTY	U.PR	ACC.PR	TOT. PR
13			2	MDB-2	2	2000	150	4300						MDB
14			3	MDB-3	1	1500	120	1620					1	MDB-1	1	1000	100	1100
15			4	0	0	0	0	0					2	MDB-2	2	2000	150	4300
16			5	0	0	0	0	0					3	MDB-3	1	1500	120	1620
17				SUBTOTAL	4									SUBTOTAL	4
18				SDB										SDB
19			1	SDB-1	3	1000	100	3300					1	SDB-1	3	1000	100	3300
20			2	SDB-2	1	2000	150	2150					2	SDB-2	1	2000	150	2150
21			3	0	0	0	0	0						SUBTOTAL	4
22			4	0	0	0	0	0						CP
23			5	0	0	0	0	0					1	CP-1	1	1000	100	1100
24				SUBTOTAL	4								2	CP-2	2	2000	150	4300
25				CP									3	CP-3	1	1500	120	1620
26			1	CP-1	1	1000	100	1100					4	CP-4	4	500	20	2080
27			2	CP-2	2	2000	150	4300					5	CP-5	1	50	5	55
28			3	CP-3	1	1500	120	1620						SUBTOTAL	4
29			4	CP-4	4	500	20	2080						GRAND TOTAL				0
30			5	CP-5	1	50	5	55
31				SUBTOTAL	4
32				GRAND TOTAL				0
33
Sheet1

Cell Formulas
Range	Formula
B3,T3,K3	B3	=SUMPRODUCT($D$3:$H$3,$D$2:$H$2)
B4	B4	=SUMPRODUCT($D$4:$H$4,$D$2:$H$2)
B5,T5,K5	B5	=SUM(B3:B4)
K4,T4	K4	=SUMPRODUCT($M$4:$Q$4,$M$2:$Q$2)
D12:D16	D12	=TRANSPOSE(D5:H5)
E12:E16	E12	=TRANSPOSE(D2:H2)
F12:F16	F12	=TRANSPOSE(D4:H4)
G12:G16	G12	=TRANSPOSE(D3:H3)
H12:H16,H26:H30,H19:H23	H12	=(F12+G12)*E12
E17,E31	E17	=SUM(E12:E14)
D19:D23	D19	=TRANSPOSE(M5:Q5)
E19:E23	E19	=TRANSPOSE(M2:Q2)
F19:F23	F19	=TRANSPOSE(M4:Q4)
G19:G23	G19	=TRANSPOSE(M3:Q3)
E24	E24	=SUM(E19:E23)
D26:D30	D26	=TRANSPOSE(V5:Z5)
E26:E30	E26	=TRANSPOSE(V2:Z2)
F26:F30	F26	=TRANSPOSE(V4:Z4)
G26:G30	G26	=TRANSPOSE(V3:Z3)
H32	H32	=SUM(H17+H24+H31)
Press CTRL+SHIFT+ENTER to enter array formulas.
Dynamic array formulas.

 

[Fluff]

How about

=LET(a,TRANSPOSE(D5:H5),FILTER(a,a<>""))

 

[djclements]

This may be more effort than it's worth, but the following should return the entire output table in a single dynamic array, using functions that are compatible with Excel 2021:

=LET(
    lbl, TRANSPOSE(FILTER(A1:Z1, A1:Z1 <> "")),
    pId, QUOTIENT(COLUMN(A1:Z1) - 4, 9),
    rId, IF(LEN(A1:Z1), A1:Z1, MOD(COLUMN(A1:Z1) - 4, 9) + 1),
    qty, A2:Z2,
    apr, A3:Z3,
    upr, A4:Z4,
    itm, A5:Z5,
    tot, qty * (upr + apr),
    tbl, TRANSPOSE(CHOOSE(SEQUENCE(7), pId, rId, itm, qty, upr, apr, tot)),
    bdy, FILTER(tbl, (INDEX(tbl, 0, 2) < 6) * (INDEX(tbl, 0, 3) <> 0)),
    key, SEQUENCE(ROWS(lbl), 1, 0),
    cId, MOD(SEQUENCE(1, 7, -2), 7),
    hdr, IF(cId = 5, key, IF(cId, "", lbl)),
    val, IFERROR(--bdy, 0),
    sub, IF(cId = 5, key, IF(cId = 6, "", IF(cId, MMULT(--(TRANSPOSE(INDEX(bdy, 0, 1)) = key), val), "SUBTOTAL"))),
    grd, IF(cId = 5, MAX(key) + 1, IF(cId = 6, "", IF(cId, MMULT(SEQUENCE(1, ROWS(bdy), 1, 0), val), "GRAND TOTAL"))),
    _h1, ROWS(hdr),
    _h2, ROWS(bdy),
    _r1, SEQUENCE(_h1 * 2 + _h2 + 1, 1, 1 - _h1),
    _r2, _r1 - _h2,
    col, SEQUENCE(1, COLUMNS(bdy)),
    arr, SORT(IF(_r1 < 1, hdr, IF(_r1 <= _h2, INDEX(bdy, _r1, col), IF(_r2 <= _h1, INDEX(sub, _r2, col), grd)))),
    INDEX(arr, SEQUENCE(ROWS(arr)), SEQUENCE(1, COLUMNS(arr) - 1, 2))
)

Please note, this solution is based on each project being allocated the same number of available columns in the worksheet (which is 9 in your mini-sheet example). Cheers!

 

[djclements]

Improved/updated to accommodate a variable number of columns for each project:

=LET(
    col, COLUMN(A1:AZ1),
    key, TRANSPOSE(FILTER(col, LEN(A1:AZ1))),
    lbl, INDEX(1:1, key),
    cId, VLOOKUP(col, key, 1, 1),
    rId, col - IFNA(VLOOKUP(cId, key, 1, 0), 0),
    qty, A2:AZ2,
    apr, A3:AZ3,
    upr, A4:AZ4,
    itm, A5:AZ5,
    num, 7,
    tbl, TRANSPOSE(CHOOSE(SEQUENCE(num), cId, rId, itm, qty, upr, apr, qty * (upr + apr))),
    bdy, FILTER(tbl, INDEX(tbl, 0, 2) * ISTEXT(INDEX(tbl, 0, 3))),
    _h1, ROWS(key),
    _h2, ROWS(bdy),
    _r1, SEQUENCE(_h1 * 2 + _h2 + 1, 1, 1 - _h1),
    _r2, _r1 - _h2,
    _c1, SEQUENCE(1, num),
    _c2, MOD(_c1 - 3, num),
    _t1, _c2 = 5,
    _t2, _c2 = 6,
    hdr, IF(_t1, key, IF(_c2, "", lbl)),
    val, IFERROR(--bdy, 0),
    sub, IF(_t1, key, IF(_t2, "", IF(_c2, MMULT(--(TRANSPOSE(INDEX(bdy, 0, 1)) = key), val), "SUBTOTAL"))),
    grd, IF(_t1, MAX(key), IF(_t2, "", IF(_c2, MMULT(SEQUENCE(1, _h2, 1, 0), val), "GRAND TOTAL"))),
    arr, SORT(IF(_r1 < 1, hdr, IF(_r1 <= _h2, INDEX(bdy, _r1, _c1), IF(_r2 <= _h1, INDEX(sub, _r2, _c1), grd)))),
    INDEX(arr, SEQUENCE(ROWS(arr)), SEQUENCE(1, num - 1, 2))
)

Adjust the range references as needed.