After 3 rounds of quality testing, list the remaining batches from Round0 after each Round. After Round1 = Remove all reject entries from Round0 After Round2 = Remove all reject entries from Round1 After Round3 = Remove all reject entries from Round2
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 664
Challenge Difficulty: ⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Track Quality Test Rounds with Power Query
Power Query solution 1 for Track Quality Test Rounds, proposed by Kris Jaganah:
let
A = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
B = Table.ToColumns(Table.Skip(A)),
C = List.Accumulate({1, 2, 3}, {B{0}}, (x, y) => x & {List.Difference(List.Last(x), B{y})}),
D = Table.ColumnNames(A),
E = Table.RemoveColumns(Table.FromColumns(C, D), D{0})
in
EPower Query solution 2 for Track Quality Test Rounds, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
A = List.Transform(Table.ToColumns(Table.Skip(Source)), List.RemoveNulls),
LG = List.Skip(
List.Generate(
() => [x = 0, y = A{0}],
each [x] < List.Count(A),
each [x = [x] + 1, y = List.Difference([y], A{x})],
each [y]
)
),
Sol = Table.FromColumns(LG, List.Skip(Table.ColumnNames(Source)))
in
SolPower Query solution 3 for Track Quality Test Rounds, proposed by Luan Rodrigues:
let
Fonte =
let
a = Table.ToColumns(Tabela1),
b = List.Accumulate(
{1 .. List.Count(a) - 1},
a{0},
(x, y) => {x} & List.RemoveMatchingItems(x, a{y})
)
in
b,
tab = Table.Skip(
Table.FromColumns(
List.Reverse(
List.Transform(
List.RemoveLastN(List.Select(Fonte, each _ is list)),
(x) => List.Select(x, (y) => not (y is list))
)
)
& {List.Select(Fonte, (y) => not (y is list))}
)
),
res = Table.RenameColumns(
tab,
List.Zip(
{
Table.ColumnNames(tab),
List.Transform(Table.ColumnNames(tab), (x) => Text.Replace(x, "Column", "Round"))
}
)
)
in
resPower Query solution 4 for Track Quality Test Rounds, proposed by Abdallah Ally:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Transform1 = List.Transform(Table.ToColumns(Source), each List.Skip(_)),
Transform2 = List.Transform(
{1 .. List.Count(Transform1) - 1},
each List.RemoveItems(Transform1{0}, List.Combine(List.FirstN(List.Skip(Transform1), _)))
),
Result = Table.FromRows(List.Zip(Transform2), List.Skip(Table.ColumnNames(Source)))
in
ResultPower Query solution 5 for Track Quality Test Rounds, proposed by Ramiro Ayala Chávez:
let
S = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
LD = List.Difference,
a = Table.ToColumns(Table.RemoveRows(S, 0)),
b = LD(a{0}, a{1}),
c = LD(b, a{2}),
d = LD(c, a{3}),
Sol = Table.FromColumns({b} & {c} & {d}, List.Skip(Table.ColumnNames(S)))
in
SolPower Query solution 6 for Track Quality Test Rounds, proposed by Peter Krkos:
let
PromotedHeaders = Table.PromoteHeaders(Source),
Cols = List.Transform(Table.ToColumns(PromotedHeaders), List.RemoveNulls),
Gen = List.Skip(
List.Accumulate(
List.Skip(Cols),
{Cols{0}},
(s, c) => s & {List.RemoveMatchingItems(List.Last(s), c)}
)
),
Tbl = Table.FromColumns(Gen, List.Transform({1 .. List.Count(Gen)}, each "Round" & Text.From(_)))
in
TblPower Query solution 7 for Track Quality Test Rounds, proposed by Alexandre Garcia:
let
U = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
H = List.Skip,
P = Table.ToColumns(Table.Skip(U)),
L = (x, y) => if y = 0 then P{0} else List.RemoveItems(@L(x, y - 1), x{y}),
C = Table.FromColumns(H(List.Transform(List.Positions(P), each L(P, _))), H(Table.ColumnNames(U)))
in
CPower Query solution 8 for Track Quality Test Rounds, proposed by Melissa de Korte:
Table.TransformColumnNames(
Table.FromColumns(
List.Accumulate(
List.Skip(Table.ToColumns(Source)),
{},
(s, a) => s & {List.RemoveItems(List.Last(s) ?? Table.ToColumns(Source){0}, a)}
)
),
each Text.Replace(_, "Column", "Round")
)Solving the challenge of Track Quality Test Rounds with Excel
Excel solution 1 for Track Quality Test Rounds, proposed by Bo Rydobon 🇹🇭:
=DROP(IFNA(REDUCE(0,B4:D4,LAMBDA(a,v,HSTACK(a,FILTER(A4:A16,1-COUNTIF(B9:v,A4:A16))))),""),,1)Excel solution 2 for Track Quality Test Rounds, proposed by Rick Rothstein:
=IFNA(DROP(REDUCE("",{1,2,3},LAMBDA(a,x,HSTACK(a,FILTER(A4:A16,1-COUNTIF(TAKE(B4:D16,,x),A4:A16))))),,1),"")Excel solution 3 for Track Quality Test Rounds, proposed by 🇰🇷 Taeyong Shin:
=DROP(REDUCE(0,B4:D4,LAMBDA(a,v,IFNA(HSTACK(a,UNIQUE(VSTACK(A4:A16,TOCOL(B16:v,1)),,1)),""))),,1)Excel solution 4 for Track Quality Test Rounds, proposed by Kris Jaganah:
=DROP(
REDUCE(
"",
{2,
3,
4},
LAMBDA(
x,
y,
IFNA(
HSTACK(
x,
UNIQUE(
TOCOL(
TAKE(
A4:D16,
,
y
)
),
,
1
)
),
""
)
)
),
,
1
)Excel solution 5 for Track Quality Test Rounds, proposed by Julian Poeltl:
=IFNA(
DROP(
REDUCE(
A4:A16,
SEQUENCE(
3
),
LAMBDA(
A,
B,
HSTACK(
A,
FILTER(
TAKE(
A,
,
-1
),
NOT(
ISNUMBER(
XMATCH(
TAKE(
A,
,
-1
),
INDEX(
B4:D16,
,
B
)
)
)
)
)
)
)
),
,
1
),
""
)Excel solution 6 for Track Quality Test Rounds, proposed by Timothée BLIOT:
=DROP(
REDUCE(
A4:A16,
ROW(
1:3
),
LAMBDA(
w,
v,
IFNA(
HSTACK(
w,
FILTER(
TAKE(
w,
,
-1
),
MAP(
TAKE(
w,
,
-1
),
LAMBDA(
x,
SUM(
N(
x=INDEX(
B4:D7,
,
v
)
)
)=0
)
)
)
),
""
)
)
),
,
1
)Excel solution 7 for Track Quality Test Rounds, proposed by Oscar Mendez Roca Farell:
=LET(
F,
LAMBDA(
i,
UNIQUE(
TOCOL(
A4:i,
1
),
,
1
)
),
IFNA(
HSTACK(
F(
B16
),
F(
C16
),
F(
D16
)
),
""
)
)Excel solution 8 for Track Quality Test Rounds, proposed by Duy Tùng:
=DROP(
REDUCE(
0,
SEQUENCE(
3
),
LAMBDA(
x,
y,
IFNA(
HSTACK(
x,
VSTACK(
"Round"&y,
FILTER(
A4:A16,
ISERROR(
XMATCH(
A4:A16,
TOCOL(
OFFSET(
B3,
1,
,
5,
y
),
3
)
)
)
)
)
),
""
)
)
),
,
1
)
hashtag#NoHeader
=DROP(
REDUCE(
0,
B4:D4,
LAMBDA(
x,
y,
IFNA(
HSTACK(
x,
FILTER(
A4:A16,
ISERROR(
XMATCH(
A4:A16,
TOCOL(
B7:y,
3
)
)
)
)
),
""
)
)
),
,
1
)Excel solution 9 for Track Quality Test Rounds, proposed by Sunny Baggu:
=IFNA(
DROP(
REDUCE(
"",
SEQUENCE(
3
),
LAMBDA(
a,
v,
HSTACK(
a,
LET(
_c,
TOCOL(
TAKE(
B4:D16,
,
v
),
3
),
FILTER(
A4:A16,
ISNA(
XMATCH(
A4:A16,
_c
)
)
)
)
)
)
),
,
1
),
""
)Excel solution 10 for Track Quality Test Rounds, proposed by Sunny Baggu:
=LET(
_v, DROP(
REDUCE("", SEQUENCE(3), LAMBDA(a, v, HSTACK(a, VSTACK(TOCOL(a, 3), TOCOL(INDEX(B4:D16, , v), 3))))),
1,
1
),
IFNA(
DROP(
REDUCE(
"",
SEQUENCE(3),
LAMBDA(x, y,
HSTACK(
x,
LET(
_c, TOCOL(INDEX(_v, , y), 3),
TOCOL(
FILTER(
TOROW(A4:A16),
BYCOL(IFNA(TOROW(A4:A16) = _c, FALSE), LAMBDA(a, NOT(OR(a))))
)
)
)
)
)
),
,
1
),
""
)
)Excel solution 11 for Track Quality Test Rounds, proposed by LEONARD OCHEA 🇷🇴:
=LET(F,LAMBDA(F,n,LET(m,UNIQUE(TOCOL(TAKE(A4:D16,,n)),,1),IF(n<4,HSTACK(m,F(F,n+1)),m))),IFNA(F(F,2),""))
Plus header
=LET(t,A2:D16,F,LAMBDA(F,n,LET(m,VSTACK(INDEX(t,1,n),UNIQUE(TOCOL(TAKE(DROP(t,2),,n)),,1)),IF(n<4,HSTACK(m,F(F,n+1)),m))),IFNA(F(F,2),""))Excel solution 12 for Track Quality Test Rounds, proposed by Anshu Bantra:
=LET(
round0_,
A4:A16,
data_,
B4:D16,
rounds_,
REDUCE(
"",
{1,
2,
3},
LAMBDA(
dat_,
col_,
HSTACK(
dat_,
FILTER(
round0_,
1 - COUNTIFS(
TAKE(
data_,
,
col_
),
round0_
)
)
)
)
),
VSTACK(
{"Round1",
"Round2",
"Round3"},
DROP(
IFNA(
rounds_,
""
),
,
1
)
)
)Excel solution 13 for Track Quality Test Rounds, proposed by Anshu Bantra:
= to_df(REF("A2:D16"))
df = df.iloc[1:]
def col_contents(df, col):
return df[col].dropna().to_list()
for col in range(1, 4):
accept = col_contents(df, 'Round'+str(col-1))
reject = col_contents(df, 'Round'+str(col))
lst = [_ for _ in accept if _ not in reject]
lst += [''] * (13 - len(lst))
df['Round'+str(col)Excel solution 14 for Track Quality Test Rounds, proposed by Md. Zohurul Islam:
=LET(
u,
A4:A16,
v,
B4:D7,
f,
LAMBDA(
p,
q,
r,
FILTER(
p,
ISERROR(
XMATCH(
p,
CHOOSECOLS(
q,
r
)
)
)
)
),
a,
f(
u,
v,
1
),
b,
f(
a,
v,
2
),
c,
f(
b,
v,
3
),
d,
IFNA(
VSTACK(
B2:D2,
HSTACK(
a,
b,
c
)
),
""
),
d
)Excel solution 15 for Track Quality Test Rounds, proposed by Md. Zohurul Islam:
=LET(u,A4:A16,v,B4:D7,w,SEQUENCE(,COLUMNS(v)),hdr,"Round"&w,z,IFNA(DROP(REDUCE("",w,LAMBDA(x,y,LET(a,TOCOL(TAKE(v,,y)),p,FILTER(a,a<>0),b,VSTACK(u,p),c,UNIQUE(b,,1),d,HSTACK(x,c),d))),,1),""),e,VSTACK(hdr,z),e)Excel solution 16 for Track Quality Test Rounds, proposed by Pieter de B.:
=LET(
a,
A4:A16,
b,
B4:D16,
c,
TAKE,
v,
VSTACK,
L,
LAMBDA(
x,
UNIQUE(
v(
a,
TOCOL(
c(
b,
,
x
),
1
)
),
,
1
)
),
IFNA(
HSTACK(
L(
1
),
L(
2
),
L(
3
)
),
""
)
)Excel solution 17 for Track Quality Test Rounds, proposed by Asheesh Pahwa:
=LET(_r1,A4:A16,DROP(IFNA(REDUCE("",SEQUENCE(3),LAMBDA(x,y,HSTACK(x,LET(I,TOCOL(TAKE(B4:D16,,y),1),FILTER(_r1,NOT(ISNUMBER(XMATCH(_r1,I)))))))),""),,1))Excel solution 18 for Track Quality Test Rounds, proposed by Eric Laforce:
=LET(
d,
A4:D16,
r,
REDUCE(
CHOOSECOLS(
d,
1
),
{2; 3; 4},
LAMBDA(
s,
c,
HSTACK(
s,
UNIQUE(
VSTACK(
TAKE(
s,
,
-1
),
CHOOSECOLS(
d,
c
)
),
,
TRUE
)
)
)
),
DROP(
IFNA(
r,
""
),
,
1
)
)Excel solution 19 for Track Quality Test Rounds, proposed by Jaroslaw Kujawa:
=DROP(IFNA(REDUCE(A4:A16;SEQUENCE(3);LAMBDA(a;x;HSTACK(a;FILTER(TAKE(a;;-1);ISERROR(XMATCH(TAKE(a;;-1);CHOOSECOLS(B4:D16;x);0))))));"");;1)Excel solution 20 for Track Quality Test Rounds, proposed by Ankur Sharma:
=IFERROR(
DROP(
REDUCE(
"",
SEQUENCE(
3,
,
2
),
LAMBDA(
a,
b,
HSTACK(
a,
UNIQUE(
TOCOL(
TAKE(
A4:D16,
,
b
),
3,
TRUE
),
,
TRUE
)
)
)
),
,
1
),
""
)Excel solution 21 for Track Quality Test Rounds, proposed by Tolga Demirci, PMP, PMI-ACP, MOS-Expert:
=LET(i,LET(a,A4:A16,FILTER(a,MAP(a,LAMBDA(x,COUNTIF(B4:B16,x)=0)))),LET(b,LET(a,i,FILTER(a,MAP(a,LAMBDA(x,COUNTIF(C4:C16,x)=0)))),HSTACK(i,b,LET(a,b,FILTER(a,MAP(a,LAMBDA(x,COUNTIF(D4:D16,x)=0)))))))Excel solution 22 for Track Quality Test Rounds, proposed by red craven:
=DROP(REDUCE("",B4:D4,LAMBDA(x,y,IFNA(HSTACK(x,FILTER(A4:A16,ISNA(XMATCH(A4:A16,TOCOL(y:B7,1))))),""))),,1)Solving the challenge of Track Quality Test Rounds with Python
Python solution 1 for Track Quality Test Rounds, proposed by Konrad Gryczan, PhD:
import pandas as pd
path = "664 Remove Rejected Batches.xlsx"
input = pd.read_excel(path, usecols="A:D", skiprows=2, nrows=14)
test = pd.read_excel(path, usecols="F:H", skiprows=1, nrows=10).rename(columns=lambda x: x.split('.')[0]).apply(lambda x: x.sort_values().values)
input_list_of_lists = input.values.T.tolist()
input_list_of_lists = [[item for item in sublist if pd.notna(item)] for sublist in input_list_of_lists]
result = pd.DataFrame({"Round1": list(set(input_list_of_lists[0]) - set(input_list_of_lists[1]))})
result["Round2"] = pd.Series(list(set(input_list_of_lists[0]) - set(input_list_of_lists[1] + input_list_of_lists[2])))
result["Round3"] = pd.Series(list(set(input_list_of_lists[0]) - set(input_list_of_lists[1] + input_list_of_lists[2] + input_list_of_lists[3])))
for col in ["Round1", "Round2", "Round3"]:
result[col] = result[col].sort_values().reset_index(drop=True)
print(result.equals(test)) # True
Python solution 2 for Track Quality Test Rounds, proposed by Abdallah Ally:
import pandas as pd
from itertools import zip_longest
file_path = 'Excel_Challenge_664 - Remove Rejected Batches.xlsx'
df = pd.read_excel(io=file_path, usecols='A:D', skiprows=1)
# Perform data manipulation
values = [
[x for x in df['Round0']
if x not in df.loc[:, 'Round1':col].values]
for col in df.columns[1:]
]
df = pd.DataFrame(data=zip_longest(*values), columns=df.columns[1:]).fillna('').loc[1:]
df
Solving the challenge of Track Quality Test Rounds with Python in Excel
Python in Excel solution 1 for Track Quality Test Rounds, proposed by Alejandro Campos:
df = xl("A2:D16", True).iloc[1:].reset_index(drop=True)
def process_batches(df):
remain, results = df.iloc[:, 0].dropna().values, []
for col in df.columns[1:]:
remain = remain[~np.isin(remain, df[col].dropna().values)]
results.append(remain.copy())
return pd.DataFrame(results, index=df.columns[1:]).T.fillna("")
result = process_batches(df)
Python in Excel solution 2 for Track Quality Test Rounds, proposed by Aditya Kumar Darak 🇮🇳:
df = xl("A2:D16", True).loc[1:]
def MyFun(df):
remain = df.iloc[:, 0].dropna().tolist()
results = []
for round_name in df.columns[1:]:
reject = set(df[round_name].dropna().tolist())
remain = [i for i in remain if i not in reject]
results.append(remain[:])
return pd.DataFrame(results, index=df.columns[1:]).T.fillna("")
result = MyFun(df)
result
Solving the challenge of Track Quality Test Rounds with R
R solution 1 for Track Quality Test Rounds, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
path = "Excel/664 Remove Rejected Batches.xlsx"
input = read_excel(path, range = "A3:D16")
test = read_excel(path, range = "F2:H12")
r1 = setdiff(input$Accept, input$Reject...2)
r2 = setdiff(input$Accept, c(input$Reject...2, input$Reject...3))
r3 = setdiff(input$Accept, c(input$Reject...2, input$Reject...3, input$Reject...4))
longest = max(length(r1), length(r2), length(r3))
result = data.frame(Round1 = c(r1, rep(NA, longest - length(r1))),
Round2 = c(r2, rep(NA, longest - length(r2))),
Round3 = c(r3, rep(NA, longest - length(r3))))
all.equal(result, test, check.attributes = FALSE)
#> [1] TRUE
&&