This challenge is contributed by Mehmet Çiçek Create the vortex grid of numbers (n x n) for a value given in column A. Samples are given for 2, 4 and 7.
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 651
Challenge Difficulty: ⭐️⭐️⭐️⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Create Vortex Number Grid with Power Query
Power Query solution 1 for Create Vortex Number Grid, proposed by Peter Krkos:
let
n = 4,
Grid = List.Repeat({List.Repeat({null}, n)}, n),
Numbers = [ a = {1..n * n},
b = List.Generate(
()=> [ x = n, y = {x}, z = {0} ],
each [x] > 0,
each [ x = [x]-1, y = [y] & List.Repeat({x}, 2), z = [z] & {List.Last([z]) + [x] } & {List.Last([z]) + [x] + x} ],
each {[z], [y]} ),
c = List.Transform(List.Zip(List.Last(b)), each List.Range(a, _{0}, _{1})),
d = List.Transform(List.Positions(c), each Number.Mod(_, 4)),
e = List.Zip({d, c})
][e],
Power Query solution 2 for Create Vortex Number Grid, proposed by Peter Krkos:
Result = [ a = Record.FieldNames(DirFunctions),
b = List.Accumulate(Numbers, Grid, (st, cur)=>
Record.Field(DirFunctions, a{cur{0}})(st, cur{1})),
c = Table.FromColumns(b)
][c]
in
Result
Solving the challenge of Create Vortex Number Grid with Excel
Excel solution 1 for Create Vortex Number Grid, proposed by Bo Rydobon 🇹🇭:
=LET(n,A11,S,SEQUENCE,q,S(n),XMATCH((q-n-1)*100+TOROW(q-1),SCAN(,TOCOL(REDUCE(-100*q^0,-SORT(-S(n-1)),LAMBDA(a,i,VSTACK(a,-1^(i+n+1)*S(,i)^0*{1;100}))),3),SUM)))
=LET(x,A11,q,x^2,n,SEQUENCE(q),m,ROUNDUP(n^0.5,),d,m^2-n,s,-1^(m+x),c,IF(d>=m,m-1,d),j,s*(d-INT(m/2)-c)*100,i,s*(EVEN(m)/2-c-1),r,SORT(UNIQUE(j)),q+1-XLOOKUP(r%+TOROW(r),i+j,n,""))Excel solution 2 for Create Vortex Number Grid, proposed by John V.:
=LET(n,A1,MAKEARRAY(n,n,LAMBDA(r,c,LET(d,MIN(r-1,c-1,n-r,n-c),1+4*d*(n-d)+IFS(c=1+d,n-d-r,r=1+d,n-3*d+c-2,c=n-d,2*n-5*d+r-3,1,4*n-7*d-c-3)))))Excel solution 3 for Create Vortex Number Grid, proposed by Timothée BLIOT:
=LET(N,A11,REDUCE(SEQUENCE(N,N)*0,SEQUENCE(CEILING(N/2,1)),LAMBDA(w,v,LET(A,MAKEARRAY(N,N,LAMBDA(x,y,IF((y=v)*(x>v-1)*(xv)*(yv)*(xv)*(y Excel solution 4 for Create Vortex Number Grid, proposed by Oscar Mendez Roca Farell:
=LET(F,LAMBDA(F,n,LET(m,MAKEARRAY(n,n,LAMBDA(r,c,LET(i,n+c-r, IFS((c=1)+(r=1),i,(c=n)+(r=n),i+2*(n+r-c-1),1,0)))),j,ROWS(m)-2,m+ IF(j,IFNA(VSTACK(0,HSTACK(0,IF(j>1,MAX(m)+F(F,j),IF(j=1,n^2,m)),0),0),0),0))),F(F,A11))Excel solution 5 for Create Vortex Number Grid, proposed by Pieter de B.:
=LET(n,
A11,
s,
SEQUENCE,
c,
COLUMNS,
r,
ROWS,
v,
VSTACK,
h,
HSTACK,
L,
REDUCE(n*n,
s((n-1)*2),
LAMBDA(
a,
y,
LET(
m,
MIN(
a
),
CHOOSE(
MOD(
y-1,
4
)+1,
v(
m-s(
,
c(
a
)
),
a
),
h(
m-s(
r(
a
)
),
a
),
v(
a,
m-s(
,
c(
a
),
1
)
),
h(
a,
m-r(
a
)+s(
r(
a
),
,
0
)
)
)
)
)),
IF(
MOD(
n,
2
),
SORTBY(
SORT(
L
),
-s(
,
n
)
),
L
))Excel solution 6 for Create Vortex Number Grid, proposed by Narayanan J 🇮🇳:
=LET(sqr,
A1,
mr,
sqr,
mc,
sqr,
hr,
sqr/2,
hc,
sqr/2,
MAKEARRAY(sqr,
sqr,
LAMBDA(r,
c,
LET(cr,
IF(
r<=hr,
r,
sqr-r+1
),
cc,
IF(
c<=hc,
c,
sqr-c+1
),
loop,
MIN(
cc,
cr
),
prv,
LAMBDA(lp,
IF(lp=1,
0,
(sqr+1)*4-(lp-1)*8)),
netPrv,
LAMBDA(
lp,
SUM(
MAP(
SEQUENCE(
lp
),
LAMBDA(
l,
prv(
l
)
)
)
)
),
op,
IFS(c=loop,
sqr-r+1-loop+1,
cc=loop,
r-loop+1+sqr*2-loop*4+2,
r=cr,
(sqr+2)-loop*2+IF(
c>loop,
c-loop,
0
),
r<>cr,
(sqr-loop*2+2)*3-2+(sqr-c-loop+1),
TRUE,
cc&":"&sqr-c-loop+1)+netPrv(
loop
),
op))))Solving the challenge of Create Vortex Number Grid with Python
Python solution 1 for Create Vortex Number Grid, proposed by Abdallah Ally:
import pandas as pd
def create_vortex_grid(n):
grid = [[0 for _ in range(n)] for _ in range(n)]
num = 1
left, right, top, bottom = 0, n - 1, 0, n - 1
while left <= right and top <= bottom:
for i in range(bottom, top - 1, -1):
grid[i][left] = num
num += 1
left += 1
for i in range(left, right + 1):
grid[top][i] = num
num += 1
top += 1
if left <= right:
for i in range(top, bottom + 1):
grid[i][right] = num
num += 1
right -= 1
if top <= bottom:
for i in range(right, left - 1, -1):
grid[bottom][i] = num
num += 1
bottom -= 1
return pd.DataFrame(data=grid)
# Perform data manipulation
df = create_vortex_grid(10)
df
Solving the challenge of Create Vortex Number Grid with Python in Excel
Python in Excel solution 1 for Create Vortex Number Grid, proposed by Alejandro Campos:
def generate_vortex_grid(n):
grid, num, l, r, t, b = [[0]*n for _ in range(n)], n, 0, n-1, 0, n-1
while l <= r and t <= b:
for i in range(l, r+1): grid[t][i], num = num, num+1
t += 1
for i in range(t, b+1): grid[i][r], num = num, num+1
r -= 1
for i in range(r, l-1, -1): grid[b][i], num = num, num+1
b -= 1
for i in range(b, t-1, -1): grid[i][l], num = num, num+1
l += 1
return grid
n = 4
grid = generate_vortex_grid(n)
grid
Python in Excel solution 2 for Create Vortex Number Grid, proposed by Aditya Kumar Darak 🇮🇳:
def Spiral(n):
def MyFun(i, j):
k = min(i, n - 1 - i, j, n - 1 - j)
s = 1 + 4 * k * (n - k)
if j == k: # Up side
return s + (n - 1 - k - i)
elif i == k: # Right side
return s + (n - 2 * k - 1) + (j - k)
elif j == n - 1 - k: # Down side
return s + 2 * (n - 2 * k - 1) + (i - k)
else: # Left side (i == n-1 - k)
return s + 3 * (n - 2 * k - 1) + (n - 1 - k - j)
return [[MyFun(i, j) for j in range(n)] for i in range(n)]
Spiral(4)
Python in Excel solution 3 for Create Vortex Number Grid, proposed by Aditya Kumar Darak 🇮🇳:
def MyFun(n):
i, j = np.indices((n, n), dtype=int)
k = np.minimum(np.minimum(i, n - 1 - i), np.minimum(j, n - 1 - j))
s = 1 + 4 * k * (n - k)
offsets = np.select(
[
j == k,
i == k,
j == n - 1 - k,
i == n - 1 - k,
],
[
(n - 1 - k - i),
(n - 2 * k - 1) + (j - k),
2 * (n - 2 * k - 1) + (i - k),
3 * (n - 2 * k - 1) + (n - 1 - k - j),
],
)
return s + offsets
MyFun(7)
&&&