For production runs, number of items (say N) to be selected in one go is given in every column. First it selects N items from the front and then N items from the back and then N from the front, then N from back and keeps repeating. Find the last item to be selected. Ex. Queue has A, B, C, D, E, F and N = 2. First A & B will be selected, then E & F will be selected and finally C & D will be selected. Hence, last item to be selected is D.
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 679
Challenge Difficulty: ⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Last Selected Queue Item with Power Query
Power Query solution 1 for Last Selected Queue Item, proposed by Kris Jaganah:
let
A = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
B = Table.ToColumns(A),
C = List.Transform(
B,
each [
a = Text.Combine(List.Skip(_)),
b = Text.Length(a),
c = List.Max({_{0}, List.Median({1 .. b})}),
d = if Number.IsOdd(b) then c else Number.IntegerDivide(c, 1) + _{0},
e = Text.At(a, d - 1)
][e]
),
D = Table.FromColumns({Table.ColumnNames(A), C}, {"Run", "Last Item"})
in
DPower Query solution 2 for Last Selected Queue Item, proposed by Luan Rodrigues:
let
Fonte = List.Transform(
Table.ToColumns(Tabela1),
(x) =>
[
a = List.RemoveNulls(x),
b = a{0},
c = List.Split(List.Skip(a), b),
d = List.Combine(List.Transform(c, (y) => List.Reverse(y))),
e = List.Median({1 .. List.Count(d)}),
f =
if List.Count(d) = b then
List.First(d)
else if e = Number.RoundDown(e, 0) then
d{Number.RoundUp(e)}
else
d{Number.RoundUp(e) - 1}
][f]
),
res = Table.FromRows(List.Zip({Table.ColumnNames(Tabela1), Fonte}), {"Run", "Last Item"})
in
resPower Query solution 3 for Last Selected Queue Item, proposed by Ramiro Ayala Chávez:
let
S = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
a = Table.ToColumns(S),
b = List.Transform(a, List.RemoveNulls),
Fx = (x) =>
let
c = List.Split(List.Skip(x), x{0}),
d = Number.RoundDown(List.Count(c) / 2),
e = if Number.IsOdd(d) then List.First(c{d}) else List.Last(c{d})
in
e,
f = List.Transform(b, each Fx(_)),
Sol = Table.FromRows(List.Zip({Table.ColumnNames(S), f}), {"Run", "Last Item"})
in
SolPower Query solution 4 for Last Selected Queue Item, proposed by Seokho MOON:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Res = Table.FromList(Table.ToColumns(Table.DemoteHeaders(Source)), Fun_1, {"Run", "Last Item"}),
Fun_1 = each {_{0}, List.Last(List.Last(Fun_2(List.Skip(_, 2), {}, _{1})))},
Fun_2 = (x as list, y as list, n as number) =>
[
A = {{List.RemoveFirstN, List.FirstN}, {List.RemoveLastN, List.LastN}},
B = {List.RemoveNulls(x), Number.Mod(List.Count(y), 2)},
C = if B{0} = {} then y else @Fun_2(A{B{1}}{0}(B{0}, n), y & {A{B{1}}{1}(B{0}, n)}, n)
][C]
in
ResPower Query solution 5 for Last Selected Queue Item, proposed by Peter Krkos:
let
F = (lst as list, n) =>
[
a = lst,
num = Number.From(n),
b = List.RemoveFirstN(a, num),
c = if List.Count(b) > num then List.RemoveLastN(b, num) else b
][c],
Gen = List.TransformMany(
Table.ToColumns(Source),
each {List.Skip(List.RemoveNulls(_))},
(x, y) =>
List.Last(
List.Last(
List.Generate(
() => if List.Count(y) <= Number.From(x{0}) then y else F(y, x{0}),
each List.Count(_) > 0,
each F(_, x{0})
)
)
)
),
Tbl = Table.FromRows(
List.Zip({Table.ColumnNames(Source), Gen}),
type table [Run = text, Last Item = text]
)
in
TblPower Query solution 6 for Last Selected Queue Item, proposed by Alexandre Garcia:
let
A = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
B = List.Transform(
Table.ToColumns(A),
(v) =>
List.Last(
List.Last(
List.Generate(
() => List.Skip(List.RemoveNulls(v)),
each _ <> {},
each List.RemoveLastN(List.Skip(_, v{0}), v{0})
)
)
)
),
C = Table.FromRows(List.Zip({Table.ColumnNames(A), B}), {"Run", "Last Item"})
in
CSolving the challenge of Last Selected Queue Item with Excel
Excel solution 1 for Last Selected Queue Item, proposed by Bo Rydobon 🇹🇭:
=TRANSPOSE(VSTACK(A2:D2,BYCOL(A3:D19,LAMBDA(x,INDEX(x,COUNTA(x)-FLOOR(COUNTA(x)-2,@+x*2)/2)))))
=TRANSPOSE(VSTACK(A2:D2,BYCOL(A3:D19,LAMBDA(x,LET(s,INT(SEQUENCE(COUNTA(x)-1,,0)/@+x),INDEX(x,1+XMATCH(0,s+SORT(-s),1,-1)))))))Excel solution 2 for Last Selected Queue Item, proposed by John V.:
=TRANSPOSE(VSTACK(A2:D2,BYCOL(A3:D19,LAMBDA(r,LET(s,SEQUENCE,z,COUNTA(r)-1,i,-INT(-z/@+r)-1,INDEX(r,1+MAX(IF(i,REDUCE(s(z),s(i)-1,LAMBDA(a,v,DROP(a,@+r*-1^v))),s(z)))))))))Excel solution 3 for Last Selected Queue Item, proposed by 🇰🇷 Taeyong Shin:
=LET(
R,
LAMBDA(
R,
x,
i,
IF(
ROWS(
x
)>i*2,
R(
R,
DROP(
DROP(
x,
i
),
-i
),
i
),
LOOKUP(
"z",
x
)
)
),
TRANSPOSE(
VSTACK(
A2:D2,
BYCOL(
A3:D19,
LAMBDA(
c,
R(
R,
DROP(
TOCOL(
c,
1
),
1
),
N(
c
)
)
)
)
)
)
)Excel solution 4 for Last Selected Queue Item, proposed by Kris Jaganah:
=WRAPCOLS(HSTACK(A2:D2,BYCOL(A3:D19,LAMBDA(x,LET(a,CONCAT(x),b,--LEFT(a),c,LEN(a)-1,e,SEQUENCE(c),f,MAX(b,MEDIAN(e)),g,IF(ISODD(c),f,INT(f)+b),MID(a,g+1,1))))),4)Excel solution 5 for Last Selected Queue Item, proposed by Julian Poeltl:
=LET(A,A2:D19,S,SEQUENCE(COLUMNS(A)),VSTACK(HSTACK("Run","Last Item"),HSTACK("Run"&S,CHAR(64+MAP(S,LAMBDA(B,LET(M,INDEX(A,2,B),R,COUNTA(INDEX(A,,B))-2,RD,ROUNDDOWN(R/M,),TAKE(UNIQUE(TOCOL(TOCOL(HSTACK(SEQUENCE(RD,,,M),SEQUENCE(RD,,R-M+1,-M)))+SEQUENCE(,M,0))),-1))))))))Excel solution 6 for Last Selected Queue Item, proposed by Timothée BLIOT:
=HSTACK(TOCOL(A2:D2),MAP(SEQUENCE(4),LAMBDA(x,LET(A,INDEX(A4:D19,,x),B,FILTER(A,A<>0),C,INDEX(A3:D3,,x),TAKE(REDUCE(B,SEQUENCE(CEILING(ROWS(B)/C,1)+1),LAMBDA(w,v,IF((v<3),w,IF(ISODD(v),DROP(w,C),DROP(w,-C))))),-1)))))Excel solution 7 for Last Selected Queue Item, proposed by Hussein SATOUR:
=TRANSPOSE(BYCOL(A3:D19,LAMBDA(z,LET(r,z,n,TAKE(r,1),a,DROP(r,1),w,CONCAT(a),b,SCAN(w,SEQUENCE(COUNTA(a))^0,LAMBDA(x,y,MID(x,y+n,LEN(x)-2*n))),RIGHT(IFERROR(TAKE(TOCOL(IF(b="",1/0,b),3),-1),w))))))Excel solution 8 for Last Selected Queue Item, proposed by LEONARD OCHEA 🇷🇴:
=LET(i,A4:D19,C,TOCOL,l,BYCOL(i,COUNTA),k,A3:D3,HSTACK(C(A2:D2),C(INDEX(i,l-k*INT((l-k)/2/k)-k*MOD(l,k),k))))
=LET(C,TOCOL,t,BYCOL(A4:D19,LAMBDA(x,CONCAT(TOROW(x)))),l,LEN(t),k,A3:D3,HSTACK(C(A2:D2),C(MID(t,l-k*INT((l-k)/2/k)-k*MOD(l,k),1))))Excel solution 9 for Last Selected Queue Item, proposed by Pieter de B.:
=TRANSPOSE(VSTACK(A2:D2,BYCOL(A2:D21,LAMBDA(a,LET(c,CONCAT(DROP(a,2)),n,@+DROP(a,1),REDUCE(c,SEQUENCE(INT(LEN(c)/n)),LAMBDA(x,y,LET(r,RIGHT,z,IF(ISODD(y),LEFT,r),IF(LEN(x)<=n,r(x),SUBSTITUTE(x,z(x,n),))))))))))
Or
=REDUCE({"Run","Last Item"},A2:D2,LAMBDA(a,b,LET(c,DROP(TAKE(b:D21,,1),1),d,@+c,VSTACK(a,HSTACK(b,REDUCE(TOCOL(DROP(c,1),1),SEQUENCE(ROWS(c)/d),LAMBDA(x,y,IF(ROWS(x)<=d,TAKE(x,-1),DROP(x,d*IF(ISODD(y),1,-1))))))))))Excel solution 10 for Last Selected Queue Item, proposed by Jaroslaw Kujawa:
=TRANSPOSE(
VSTACK(
A2:D2;
BYCOL(
A3:D19;
LAMBDA(
x;
LET(
n;
COUNTA(
x
)-1;
CHOOSEROWS(
x;
1+IF(
TAKE(
x;
1
)<>n;
TAKE(
x;
1
)+n/2;
TAKE(
x;
1
)
)
)
)
)
)
)
)Excel solution 11 for Last Selected Queue Item, proposed by JvdV -:
=TRANSPOSE(
VSTACK(
A2:D2,
BYCOL(
A3:D19,
LAMBDA(
c,
LET(
n,
@+c,
d,
DROP,
x,
LAMBDA(
f,
s,
IF(
ROWS(
s
)>2*n,
f(
f,
d(
d(
s,
n
),
-n
)
),
TAKE(
s,
-1
)
)
),
x(
x,
d(
TOCOL(
c,
1
),
1
)
)
)
)
)
)
)Excel solution 12 for Last Selected Queue Item, proposed by Sandeep Marwal:
=LET(
range,
A4:A11,
countofchar,
COUNTA(
range
),
run,
A3,
remainder,
MOD(
countofchar,
run
),
subt,
IF(
remainder=0,
countofchar-run,
countofchar-remainder
),
dp_1,
ROUNDUP(
subt/2/run,
0
)*run,
dp_2,
ROUNDDOWN(
subt/2/run,
0
)*run,
st_1,
DROP(
range,
dp_1
),
st_2,
DROP(
st_1,
-dp_2
),
result,
TAKE(
st_2,
-1
),
result
)Excel solution 13 for Last Selected Queue Item, proposed by Ben Warshaw:
=LET(
_Run_1,
A3:D3,
_Run_2,
A4:D19,
_Step1,
BYCOL(
_Run_2,
COUNTA
),
_Step2,
_Step1 / _Run_1,
_Step3,
BYCOL(
_Step1,
LAMBDA(
x,
IF(
ISODD(
x
),
ROUND(
x / 2,
0
),
ROUND(
x / 2,
0
) + 1
)
)
),
_Step4,
CEILING(
_Step3,
_Run_1
) - IF(
INT(
_Step2
) <> _Step2,
1,
0
),
_Result,
TOCOL(
INDEX(
_Run_2,
_Step4,
_Run_1
)
),
_Result
)Solving the challenge of Last Selected Queue Item with Python
Python solution 1 for Last Selected Queue Item, proposed by Konrad Gryczan, PhD:
import pandas as pd
path = "679 Last Item to be Selected.xlsx"
input = pd.read_excel(path, usecols="A:D", skiprows=1, nrows=18)
test = pd.read_excel(path, usecols="F:G", skiprows=1, nrows=4)
df = input.fillna('')
def last_item(data):
N = int(data[0])
items = [i for i in data[1:] if i]
front = True
while len(items) > N:
if front:
items = items[N:]
else:
items = items[:-N]
front = not front
return items[-1]
result = pd.DataFrame({
'Run': df.columns,
'Last Item': [last_item(df[column].tolist()) for column in df.columns]
})
print(result.equals(test)) # True
Solving the challenge of Last Selected Queue Item with Python in Excel
Python in Excel solution 1 for Last Selected Queue Item, proposed by Alejandro Campos:
df = xl("A2:D19", headers=True).fillna('')
def last_item(data):
N, items, front = int(data[0]), [i for i in data[1:] if i], True
while len(items) > N: items, front = (items[N:], not front) if front else (items[:-N], not front)
return items[-1]
pd.DataFrame({'Run': df.columns, 'Last Item': [last_item(df[c].tolist()) for c in df.columns]})
Python in Excel solution 2 for Last Selected Queue Item, proposed by &Aditya Kumar Darak 🇮🇳:
import math
df = xl("A2:D19", True)
N = df.iloc[0].astype(int)
Lsts = df.iloc[1:].apply(lambda col: col.dropna().tolist(), axis=0)
def MyFun(lst, n):
L = len(lst)
k = math.ceil(L / n)
d = "F" if k % 2 == 1 else "B"
r = L - (k - 1) * n
if d == "F":
return lst[(k // 2) * n + r - 1]
else:
return lst[L - 1 - ((k - 1) // 2) * n]
Last = Lsts.index.to_series().apply(lambda run: MyFun(Lsts[run], N[run]))
result = pd.DataFrame({"Run": Last.index, "Last": Last.values})
result
Solving the challenge of Last Selected Queue Item with R
R solution 1 for Last Selected Queue Item, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
path = "Excel/679 Last Item to be Selected.xlsx"
input = read_excel(path, range = "A2:D19")
test = read_excel(path, range = "F2:G6")
last_item = function(data) {
N = as.numeric(data[1])
items = na.omit(data[-1])
front = TRUE
while (length(items) > N) {
items = if (front) items[(N + 1):length(items)] else items[1:(length(items) - N)]
front = !front
}
items[length(items)]
}
result = data.frame(
Run = names(input),
items = map_chr(input, last_item)
)
all.equal(result, test, check.attributes = FALSE)
#> [1] TRUE
&&