Sort odd numbered rows into ascending order, even numbered rows into descending order and populate their corresponding cities in the result. Odd and even is for data (not including header). So, excel row no. 2 is first row, hence sorted in ascending order and row no. 3 is second row, hence sorted in descending order and so on.
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 555
Challenge Difficulty: ⭐️⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Sort Alternate Row Patterns with Power Query
Power Query solution 1 for Sort Alternate Row Patterns, proposed by Abdallah Ally:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Transform = Table.TransformRows(
Source,
each [
a = List.Zip({Record.FieldNames(_), Record.ToList(_)}),
b = Number.Mod(Table.PositionOf(Source, _), 2),
c = List.Sort(
a,
(x, y) => if b = 0 then Value.Compare(x{1}, y{1}) else Value.Compare(y{1}, x{1})
),
d = List.Transform(c, each if _{1} = null then null else _{0}),
e = List.RemoveNulls(d) & List.Repeat({null}, List.Count(d) - List.NonNullCount(d))
][e]
),
Result = Table.FromRows(Transform)
in
ResultPower Query solution 2 for Sort Alternate Row Patterns, proposed by Ramiro Ayala Chávez:
let
S = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
a = List.TransformMany(
Table.ToRows(S),
(x) => {Table.ColumnNames(S)},
(x, y) => Table.SelectRows(Table.FromRows(List.Zip({x, y})), each [Column1] <> null)
),
b = List.Transform(List.Alternate(a, 1, 1, 1), each Table.Sort(_, {"Column1", 0})[Column2]),
c = List.Transform(List.Alternate(a, 1, 1), each Table.Sort(_, {"Column1", 1})[Column2]),
Sol = Table.Combine(List.Transform(List.Zip({b, c}), each Table.FromColumns(List.Zip(_))))
in
SolPower Query solution 3 for Sort Alternate Row Patterns, proposed by Alexandre Garcia:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
rec =
let
x = Table.UnpivotOtherColumns(Source, {}, "Attribute", "Value")
in
Record.FromList(x[Attribute], x[Value]),
Sort =
let
x = Table.TransformColumns(
Table.AddIndexColumn(Source, "Index", 0, 1),
{"Index", each Number.From(Number.IsOdd(_))}
)
in
Table.Combine(
Table.AddColumn(
x,
"x",
each
let
x = [Index]
in
Table.FromRows(
{List.Sort(List.RemoveLastN(List.RemoveNulls(Record.ToList(_)), 1), x)}
)
)[x]
),
Result = List.Accumulate(
Table.ColumnNames(Sort),
Sort,
(s, c) => Table.TransformColumns(s, {c, each try Record.Field(rec, _) otherwise null})
)
in
ResultSolving the challenge of Sort Alternate Row Patterns with Excel
Excel solution 1 for Sort Alternate Row Patterns, proposed by Rick Rothstein:
=IFNA(
DROP(
REDUCE(
"",
SEQUENCE(
ROWS(
A2:E19
)
),
LAMBDA(
a,
x,
LET(
i,
INDEX(
A2:E19,
x,
),
VSTACK(
a,
TAKE(
SORTBY(
A1:E1,
i,
IF(
ISODD(
x
),
1,
-1
)
),
,
COUNTA(
i
)
)
)
)
)
),
1
),
""
)Excel solution 2 for Sort Alternate Row Patterns, proposed by John V.:
=DROP(
REDUCE(
0,
E2:E19,
LAMBDA(
a,
v,
LET(
r,
TAKE(
A19:v,
1
),
VSTACK(
a,
SORTBY(
REPT(
A1:E1,
r>0
),
r,
-1^ROWS(
E1:v
)
)
)
)
)
),
1
)Excel solution 3 for Sort Alternate Row Patterns, proposed by 🇰🇷 Taeyong Shin:
=LET(
d,
A2:E19,
MAKEARRAY(
ROWS(
d
),
COLUMNS(
d
),
LAMBDA(
r,
c,
LET(
x,
INDEX(
d,
r,
),
INDEX(
SORTBY(
REPT(
A1:E1,
x>0
),
x,
IF(
ISODD(
r
),
1,
-1
)
),
c
)
)
)
)
)Excel solution 4 for Sort Alternate Row Patterns, proposed by Julian Poeltl:
=LET(
C,
A2:E19,
DROP(
REDUCE(
0,
SEQUENCE(
ROWS(
C
)
),
LAMBDA(
A,
B,
VSTACK(
A,
LET(
CR,
CHOOSEROWS(
C,
B
),
S,
SORT(
CR,
,
IF(
ISEVEN(
B
),
-1,
1
),
1
),
IFNA(
"Cities"&XMATCH(
IF(
S<>0,
S,
""
),
CR
),
""
)
)
)
)
),
1
)
)Excel solution 5 for Sort Alternate Row Patterns, proposed by Timothée BLIOT:
=LET(
A,
A2:E19,
IFNA(
DROP(
REDUCE(
"",
SEQUENCE(
ROWS(
A
)
),
LAMBDA(
w,
v,
LET(
B,
INDEX(
A,
v,
),
C,
ISBLANK(
B
),
D,
TOCOL(
IF(
C,
1/0,
A1:E1
),
3
),
E,
TOCOL(
IF(
C,
1/0,
B
),
3
),
VSTACK(
w,
TOROW(
SORTBY(
D,
E,
1
)
)
)
)
)
),
1
),
""
)
)Excel solution 6 for Sort Alternate Row Patterns, proposed by Oscar Mendez Roca Farell:
=DROP(REDUCE("", A2:A19, LAMBDA(i, x, LET(f, TAKE(x:E19, 1), IFNA(VSTACK(i, "Cities"&XMATCH(SORT(TOROW(f, 1), ,-1^ROW(x), 1), f)), "")))), 1)Excel solution 7 for Sort Alternate Row Patterns, proposed by Sunny Baggu:
=LET(
_s, SEQUENCE(ROWS(A2:E19)),
_v, IF(MOD(_s, 2), 1, -1),
IFNA(
DROP(
REDUCE(
"🌼",
_s,
LAMBDA(a, v,
VSTACK(
a,
LET(
r, INDEX(A2:E19, v, ),
SORTBY(FILTER(A1:E1, r <> ""), FILTER(r, r <> ""), INDEX(_v, v, 1))
)
)
)
),
1
),
""
)
)Excel solution 8 for Sort Alternate Row Patterns, proposed by Abdallah Ally:
=DROP(
REDUCE(
"",
A2:A19,
LAMBDA(
x,
y,
LET(
a,
A1:E1,
b,
OFFSET(
y,
,
,
,
5
),
c,
MAP(
a,
b,
LAMBDA(
u,
v,
IF(
v="",
"",
u
)
)
),
VSTACK(
x,
SORTBY(
c,
b,
IF(
MOD(
ROW(
y
),
2
),
-1,
1
)
)
)
)
)
),
1
)Excel solution 9 for Sort Alternate Row Patterns, proposed by ferhat CK:
=LET(a,A2:E19,b,ROWS(a),c,MAP(a,LAMBDA(y,IF((MOD(ROW(y),2)=1)*(y=""),"aaa",IFS(y<>"",y&"-"&CHOOSECOLS(A1:E1,COLUMN((y))))))),d,DROP(REDUCE(0,SEQUENCE(b),LAMBDA(x,y,VSTACK(x,SORT(CHOOSEROWS(c,y),,IF(MOD(y,2)=0,-1,1),TRUE)))),1),e,IF(d="aaa","",d),IFNA(MAP(e,LAMBDA(x,TEXTAFTER(x,"-"))),""))Excel solution 10 for Sort Alternate Row Patterns, proposed by Bilal Mahmoud kh.:
=DROP(
REDUCE(
0,
SEQUENCE(
13
),
LAMBDA(
x,
y,
VSTACK(
x,
LET(
a,
SORTBY(
CHOOSEROWS(
A2:E14,
y
),
CHOOSEROWS(
A2:E14,
y
),
IF(
ISODD(
y
),
1,
-1
)
),
b,
XLOOKUP(
IF(
a=0,
"",
a
),
CHOOSEROWS(
A2:E14,
y
),
A1:E1,
"",
0
),
b
)
)
)
),
1
)Excel solution 11 for Sort Alternate Row Patterns, proposed by Eddy Wijaya:
=IFNA(
DROP(
REDUCE(
0,
A2:A19,
LAMBDA(
a,
v,
VSTACK(
a,
LET(
d,
OFFSET(
v,
,
,
1,
5
),
c_d,
TOCOL(
d
),
cat,
ROW(
v
),
adj_d,
SORT(
c_d,
,
IF(
ISODD(
cat
),
-1,
1
)
),
TOROW(
"Cities"&XMATCH(
FILTER(
adj_d,
adj_d<>""
),
c_d,
0
)
)
)
)
)
),
1
),
""
)Excel solution 12 for Sort Alternate Row Patterns, proposed by Pieter de Bruijn:
=LET(x,
A2:E19,
MAKEARRAY(ROWS(
x
),
COLUMNS(
x
),
LAMBDA(r,
c,
LET(i,
INDEX(
x,
r,
),
IFERROR(INDEX(XLOOKUP(SORT(FILTER(
i,
i<>""
),
,
-1+(ISODD(
r
)*2),
1),
i,
A1:E1),
c),
"")))))Solving the challenge of Sort Alternate Row Patterns with Python
Python solution 1 for Sort Alternate Row Patterns, proposed by Konrad Gryczan, PhD:
import pandas as pd
import numpy as np
path = "555 Order Cities.xlsx"
input = pd.read_excel(path, usecols="A:E")
test = pd.read_excel(path, usecols="G:K")
def transform(input_df):
input_df['rn'] = np.arange(len(input_df)) + 1
result = input_df.melt(id_vars=['rn'], var_name='key', value_name='value')
def custom_sort(group):
even_sort = group[group['rn'] % 2 == 0].sort_values(by='value', ascending=False)
odd_sort = group[group['rn'] % 2 != 0].sort_values(by='value', ascending=True)
return pd.concat([even_sort, odd_sort])
result =& result.groupby('rn').apply(custom_sort).reset_index(drop=True)
result['rn2'] = result.groupby('rn').cumcount() + 1
result['key'] = result['key'].where(result['value'].notna(), np.nan)
result = result.pivot(index='rn', columns='rn2', values='key').reset_index(drop=True)
return result
output = transform(input)
test.columns = output.columns
print(output.equals(test)) # True
Solving the challenge of Sort Alternate Row Patterns with Python in Excel
Python in Excel solution 1 for Sort Alternate Row Patterns, proposed by Abdallah Ally:
df = xl("A1:E13", headers=True)
# Perform data manipulation
cols = df.columns
values = []
# transform rows and append each to a list values
for i in df.index: # transform each row
a = zip(cols, df.fillna('').iloc[i, :])
b = sorted(a, key=lambda x: x[1], reverse=(i % 2))
c = [x[0] if x[1] else '' for x in b]
d = [x for x in c if x]
values.append(d + [''] * (len(c) - len(d)))
# Create a dataframe from transformed rows
df = pd.DataFrame(data=values)
df
Solving the challenge of Sort Alternate Row Patterns with R
R solution 1 for Sort Alternate Row Patterns, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
path = "Excel/555 Order Cities.xlsx"
input = read_excel(path, range = "A1:E19")
test = read_excel(path, range = "G2:K19", col_names = FALSE)
names(test) = c("1", "2", "3", "4", "5")
result = input %>%
mutate(rn = row_number()) %>%
select(rn, everything()) %>%
pivot_longer(-rn, names_to = "key", values_to = "value") %>%
group_by(rn) %>%
arrange(
rn,
desc(if_else(rn %% 2 == 0, value, NA_character_)),
if_else(rn %% 2 != 0, value, NA_character_)
) %>%
mutate(rn2 = row_number(),
key = if_else(is.na(value), NA, key)) %>%
ungroup() %>%
select(-value) %>%
pivot_wider(names_from = rn2, values_from = key) %>%
select(-rn)
all.equal(result, test)
# [1] TRUE
&&