Snasui Excel Forum

Posted: **Sat Nov 15, 2025 7:44 am**

I have a query 2,required dynamic array to transform data

I attach a template

Thk

Posted: **Sat Nov 15, 2025 11:10 am**

Give this a shot,

=HSTACK(
    FILTER(B3:B100,B3:B100<>""),
    DROP(FILTER(C3:C100,C3:C100<>""),1),
    DROP(FILTER(D3:D100,D3:D100<>""),1),
    DROP(FILTER(E3:E100,E3:E100<>""),1)
)

If you’re dealing with many columns, give this a try,

Code: Select all

=LET(a,B3:E11,
    REDUCE(
    FILTER(B3:B11,B3:B11<>""),
    SEQUENCE(COLUMNS(a)-1,1,2),
    LAMBDA(acc,e,
        HSTACK(acc,DROP(FILTER(INDEX(a,0,e),INDEX(a,0,e)<>""),1))
    )
  )
)

You can change B3:E11 to suit your needs.

Posted: **Sat Nov 15, 2025 9:56 pm**

It both works.thk

Snasui Excel Forum

Excel query 2

Excel query 2

Re: Excel query 2

Re: Excel query 2