Page 1 of 1
Excel query 2
Posted: Sat Nov 15, 2025 1:20 pm
by sna
Dear all ,
I need dynamic array to solve problem n get outputs as in attached
Thanks
Re: Excel query 2
Posted: Sat Nov 15, 2025 3:28 pm
by snasui
Go ahead and give it a try.
Code: Select all
=MAKEARRAY(ROWS(A2:A7),4,
LAMBDA(r,c,
LET(
row, INDEX(A2:A7,r),
countryRaw, TEXTBEFORE(row," ("),
country, TEXTBEFORE(countryRaw," ",-1),
utc, "UTC"&TEXTBEFORE(TEXTAFTER(row,"(UTC"),")"),
latToken, TEXTAFTER(row,"LAT "),
lat, IFERROR(TEXTBEFORE(latToken," "), latToken),
longToken, TEXTAFTER(row,"LONG "),
long, IFERROR(TEXTBEFORE(longToken," "), longToken),
CHOOSE(c, country, utc, --lat, --long)
)
)
)
Re: Excel query 2
Posted: Sat Nov 15, 2025 9:57 pm
by sna
Can be without MAKEARRAY? Thk
Re: Excel query 2
Posted: Sat Nov 15, 2025 10:07 pm
by sna
Yes your formula works awesome, i try it but no luck
=LET(
a, A2:A7,
GETVAL, LAMBDA(m, --TEXTBEFORE(TEXTAFTER(a, m), IF({1,0}, " ", CHAR(10)))),
Country, TEXTBEFORE(TEXTBEFORE(a, " (", 1), " "),
t, TEXTAFTER(TEXTBEFORE(TEXTAFTER(a, "("), ")"), " "),
LAT, GETVAL("LAT "),
LONG, GETVAL("LONG "),
HSTACK(Country,t, LAT, LONG)
)
Re: Excel query 2
Posted: Sat Nov 15, 2025 11:12 pm
by snasui
sna wrote: Sat Nov 15, 2025 9:57 pm
Can be without MAKEARRAY? Thk
Try this,
Code: Select all
=DROP(
REDUCE("", A2:A7,
LAMBDA(acc,row,
LET(
countryRaw, TEXTBEFORE(row," ("),
country, TEXTBEFORE(countryRaw," ",-1),
utc, "UTC"&TEXTBEFORE(TEXTAFTER(row,"(UTC"),")"),
latToken, TEXTAFTER(row,"LAT "),
lat, IFERROR(TEXTBEFORE(latToken," "), latToken),
longToken, TEXTAFTER(row,"LONG "),
long, IFERROR(TEXTBEFORE(longToken," "), longToken),
VSTACK(acc, HSTACK(country, utc, --lat, --long))
)
)
),
1
)
Re: Excel query 2
Posted: Sat Nov 15, 2025 11:17 pm
by sna
Thanks you