Extend the given numbers into row and wrap the row where absolute gap between two subsequent numbers is greater than 2. Between row 5 and 6, absolute gap is > 2, hence 7 and 5 will come into different row. Between row 7 and 8, absolute gap is > 2, hence, 2, 1 and 3 will come into different row.
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 641
Challenge Difficulty: ⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Wrap Rows By Number Gaps with Power Query
_x000D_Power Query solution 1 for Wrap Rows By Number Gaps, proposed by Kris Jaganah:
let
A = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
B = Table.AddIndexColumn(A, "Id"),
C = Table.AddColumn(
B,
"Ab",
each Number.From(Number.Abs((try B[Numbers]{[Id] - 1} otherwise 100) - [Numbers]) > 2)
),
D = Table.AddColumn(C, "Ac", each List.Sum(List.FirstN(C[Ab], [Id] + 1))),
E = Table.FromList(Table.Group(D, "Ac", {"All", each [Numbers]})[All], (z) => z)
in
EPower Query solution 2 for Wrap Rows By Number Gaps, proposed by Kris Jaganah:
let
A = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content][Numbers],
B = List.Generate(
() => [a = 0, b = A{a}, c = 1, d = {b}, e = 1],
each [a] < List.Count(A),
each [
a = [a] + 1,
b = A{a},
c = Number.From(Number.Abs(A{a} - A{a - 1}) > 2) + [c],
d = if c = [c] then [d] & {b} else {b}
],
each {[c], [d]}
),
C = Table.FromList(
List.Transform(
List.Distinct(List.Zip(B){0}),
each List.Last(List.Zip(List.Select(B, (z) => z{0} = _)){1})
),
(w) => w
)
in
CPower Query solution 3 for Wrap Rows By Number Gaps, proposed by Aditya Kumar Darak 🇮🇳:
let
Source = Excel.CurrentWorkbook(){[Name = "data"]}[Content],
ToCol = Table.ToColumns(Source),
Table = Table.FromColumns(ToCol & {{null} & List.RemoveLastN(ToCol{0}, 1)}),
Group = Table.Group(
Table,
{"Column1", "Column2"},
{"A", each [Column1]},
0,
(x, y) => Byte.From(Number.Abs(y[Column1] - y[Column2]) > 2)
),
Return = Table.FromList(Group[A], each _)
in
ReturnPower Query solution 4 for Wrap Rows By Number Gaps, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Idx = Table.AddIndexColumn(Source, "Idx", 0),
Sol = Table.FromColumns(
List.Zip(
Table.Group(
Idx,
"Idx",
{{"A", each [Numbers]}},
0,
(x, y) => Number.From(Number.Abs(Idx[Numbers]{y} - Idx[Numbers]{y - 1}) > 2)
)[A]
)
)
in
SolPower Query solution 5 for Wrap Rows By Number Gaps, proposed by Seokho MOON:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content][Numbers],
Rows = List.Accumulate(
Source,
{},
(a, v) =>
if a = {} or Number.Abs(List.Last(List.Last(a)) - v) > 2 then
a & {{v}}
else
List.RemoveLastN(a) & {List.Last(a) & {v}}
),
Res = Table.FromList(Rows, each _)
in
ResPower Query solution 6 for Wrap Rows By Number Gaps, proposed by Seokho MOON:
Seokho MOON
Res = Table.FromColumns(List.Zip(Rows))Power Query solution 7 for Wrap Rows By Number Gaps, proposed by Krzysztof Kominiak:
let
Source = Table.TransformColumnTypes(
Table.FromRows(
Json.Document(
Binary.Decompress(
Binary.FromText("i45WMlSK1YlWMgaTpkhscyQRIzCJrNICxo4FAA==", BinaryEncoding.Base64),
Compression.Deflate
)
),
let
_t = ((type nullable text) meta [Serialized.Text = true])
in
type table [Numbers = _t]
),
{{"Numbers", type number}}
),
Result = Table.Combine(
Table.Group(
Source,
"Numbers",
{{"NT", each Table.FromRows({[Numbers]})}},
0,
(x, y) => Number.From(Number.Abs(y - x) > 2)
)[NT]
)
in
ResultSolving the challenge of Wrap Rows By Number Gaps with Excel
_x000D_Excel solution 1 for Wrap Rows By Number Gaps, proposed by Rick Rothstein:
=IFNA(TEXTSPLIT(CONCAT(IFNA(HSTACK(A2:A12,IF(ABS(A3:A12-A2:A11)>2,"a","b")),"")),"b","a"),"")
or in a more "general" format...
=LET(r,A2:A12,IFNA(TEXTSPLIT(CONCAT(IFNA(HSTACK(r,IF(ABS(DROP(r,1)-DROP(r,-1))>2,"a","b")),"")),"b","a"),""))Excel solution 2 for Wrap Rows By Number Gaps, proposed by John V.:
=TEXTSPLIT(CONCAT(-1^(ABS(N(+A1:A11)-A2:A12)>2)*A2:A12&"|"),"|","-",1,,"")Excel solution 3 for Wrap Rows By Number Gaps, proposed by Kris Jaganah:
=LET(a,
A2:A12,
b,
SCAN(1,
--(ABS(
a-VSTACK(
0,
DROP(
a,
-1
)
)
)>2),
SUM),
DROP(IFNA(REDUCE("",
UNIQUE(
b
),
LAMBDA(x,
y,
VSTACK(x,
TOROW(a/(b=y),
3)))),
""),
1))Excel solution 4 for Wrap Rows By Number Gaps, proposed by Timothée BLIOT:
=LET(A,
A2:A12,
B,
SCAN(0,
VSTACK(1,
--(ABS(
DROP(
A,
1
)-DROP(
A,
-1
)
)>2)),
LAMBDA(
w,
v,
w+v
)),
IFNA(
DROP(
REDUCE(
0,
SEQUENCE(
MAX(
B
)
),
LAMBDA(
w,
v,
VSTACK(
w,
TOROW(
FILTER(
A,
B=v
)
)
)
)
),
1
),
""
))Excel solution 5 for Wrap Rows By Number Gaps, proposed by Oscar Mendez Roca Farell:
=LET(d,A2:A12,TEXTSPLIT(CONCAT(d&IF(ABS(d-VSTACK(DROP(d,1),0))>2,"|","-")),"-","|",1,,""))Excel solution 6 for Wrap Rows By Number Gaps, proposed by Sunny Baggu:
=TEXTSPLIT(
CONCAT(
IFNA(
A2:A12 & IF(ABS(A3:A12 - A2:A11) > 2, "/", ","),
A2:A12
)
),
",",
"/",
,
,
""
)Excel solution 7 for Wrap Rows By Number Gaps, proposed by Md. Zohurul Islam:
=LET(z,A2:A12,
a,IFERROR(MAP(z,LAMBDA(x,ABS(x-OFFSET(x,-1,0)))),0),
b,SCAN(1,IF(a<=2,0,1),SUM),
c,DROP(GROUPBY(b,z,ARRAYTOTEXT,0,0),,1),
d,DROP(REDUCE("",c,LAMBDA(x,y,IFNA(VSTACK(x,ABS(TEXTSPLIT(y,", "))),""))),1),
d)Excel solution 8 for Wrap Rows By Number Gaps, proposed by Pieter de B.:
=LET(a,A2:A12,b,A3:A13,TEXTSPLIT(CONCAT(a&IF(ABS(a-IF(b="",a,b))>2,";",",")),",",";",,,""))Excel solution 9 for Wrap Rows By Number Gaps, proposed by Hamidi Hamid:
=LET(
x,
MAP(
A2:A12,
A3:A13,
LAMBDA(
a,
b,
IF(
ABS(
b-a
)>2,
a&"/",
a&"-"
)
)
),
IFERROR(
TEXTSPLIT(
CONCAT(
x
),
"-",
"/",
),
""
)
)Excel solution 10 for Wrap Rows By Number Gaps, proposed by Imam Hambali:
=LET(
n, A2:A12,
d, SCAN(1, IF(ABS(n-VSTACK(0,DROP(n,-1)))>2,1,0),SUM),
IFNA(TEXTSPLIT(TEXTJOIN(";",1,DROP(GROUPBY(d,n, ARRAYTOTEXT,0,0),,1)),",",";")*1,"")
)Excel solution 11 for Wrap Rows By Number Gaps, proposed by El Badlis Mohd Marzudin:
=LET(o,A2:A12,TEXTSPLIT(TEXTJOIN(".",,DROP(GROUPBY(SCAN(1,ABS(o-VSTACK(1,A2:A11))>2,SUM),o,ARRAYTOTEXT,,0),,1)),",",".",,,""))Excel solution 12 for Wrap Rows By Number Gaps, proposed by Tyler Cameron:
=LET(
a,
A2:A12,
TEXTSPLIT(
CONCAT(
a&IF(
ABS(
a-A3:A13
)>2,
"/",
","
)
),
",",
"/",
,
,
""
)
)Solving the challenge of Wrap Rows By Number Gaps with Python
_x000D_Python solution 1 for Wrap Rows By Number Gaps, proposed by Konrad Gryczan, PhD:
import pandas as pd
import numpy as np
input = pd.read_excel(path, usecols="A", nrows=12)
test = pd.read_excel(path, usecols="B:E", nrows=5, names=["N1", "N2", "N3", "N4"])
test = test.astype('float64')
input['row'] = (input['Numbers'].diff().abs().gt(2) | input['Numbers'].shift().isna()).cumsum()
input['num'] = 'N' + (input.groupby('row').cumcount() + 1).astype(str)
result = input.pivot(index='row', columns='num', values='Numbers').reset_index(drop=True).rename_axis(None, axis=1).astype('float64')
print(result.equals(test)) # True
Solving the challenge of Wrap Rows By Number Gaps with Python in Excel
_x000D_Python in Excel solution 1 for Wrap Rows By Number Gaps, proposed by Alejandro Campos:
def wrap_numbers(numbers):
rows = [[numbers[0]]]
for n, p in zip(numbers[1:], numbers):
(rows.append([n]) if abs(n - p) > 2 else rows[-1].append(n))
return rows
df = pd.DataFrame(wrap_numbers(xl("A2:A12")[0])).fillna(' ')
Python in Excel solution 2 for Wrap Rows By Number Gaps, proposed by Aditya Kumar Darak 🇮🇳:
df = xl("A1:A12", True)
grp = df.groupby((df["Numbers"].diff().abs() > 2).cumsum())["Numbers"].apply(list)
result = pd.DataFrame(grp.tolist()).fillna("").values
result
Python in Excel solution 3 for Wrap Rows By Number Gaps, proposed by Seokho MOON:
def wrap_row(column):
res = []
temp = []
for cell in column:
if temp and abs(temp[-1] - cell) > 2:
res.append(temp)
temp = []
temp.append(cell)
if temp:
res.append(temp)
return pd.DataFrame(res).fillna("")
wrap_row(df["Numbers"])
Solving the challenge of Wrap Rows By Number Gaps with R
_x000D_R solution 1 for Wrap Rows By Number Gaps, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
input = read_excel(path, range = "A1:A12")
test = read_excel(path, range = "B2:E6", col_names = c("N1", "N2", "N3", "N4"))
result = input %>%
mutate(row = cumsum(if_else(is.na(lag(Numbers)) | abs(Numbers - lag(Numbers)) > 2, 1, 0))) %>%
mutate(num = glue::glue("N{row_number()}"), .by = row) %>%
pivot_wider(names_from = num, values_from = Numbers) %>%
select(-row)
all.equal(result, test, check.attributes = FALSE)
#> [1] TRUE