Sequential Group Numbering Logic - Excel, Power Query, Python Challenges

This challenge is contributed by Ankur Sharma Populate 1, 2, 3…against alphabets in alphabet groups. Blanks will be populated with 0. When groups re-occur, then sequences will be incremented by 1 for that group.

📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 596
Challenge Difficulty: ⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn

Solving the challenge of Sequential Group Numbering Logic with Power Query

Power Query solution 1 for Sequential Group Numbering Logic, proposed by Kris Jaganah:

let
  A = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  B = Table.AddIndexColumn(A, "Id", 1), 
  C = Table.Combine(
    Table.Group(
      B, 
      {"Alphabets"}, 
      {
        "All", 
        (x) =>
          let
            a = Table.SelectRows(
              x, 
              each ([Id] - (try x[Id]{List.PositionOf(x[Id], [Id]) - 1} otherwise - 1)) > 1
            ), 
            b = Table.AddIndexColumn(a, "Idx", 1), 
            c = Table.Distinct(Table.Combine({b, x}), {"Id"}), 
            d = Table.Sort(c, "Id"), 
            e = Table.FillDown(d, {"Idx"})
          in
            e
      }
    )[All]
  ), 
  D = Table.Sort(C, {"Id", 0}), 
  E = Table.AddColumn(D, "Answer Expected", each if [Alphabets] = null then 0 else [Idx])[
    [Alphabets], 
    [Answer Expected]
  ]
in
  E

Power Query solution 2 for Sequential Group Numbering Logic, proposed by Aditya Kumar Darak 🇮🇳:

let
  Source = Excel.CurrentWorkbook(){[Name = "data"]}[Content], 
  Index = Table.AddIndexColumn(Source, "I"), 
  Group1 = Table.Group(Index, "Alphabets", {"A", each _}, 0), 
  Group2 = Table.Group(
    Group1, 
    "Alphabets", 
    {
      "A", 
      each 
        if Table.FirstValue(_) = null then
          Table.AddColumn(_, "Index", (f) => 0)
        else
          Table.AddIndexColumn(_, "Index", 1)
    }
  ), 
  Combine = Table.Combine(Group2[A]), 
  Expand = Table.ExpandTableColumn(Combine, "A", {"I"}), 
  Sort = Table.Sort(Expand, "I")[[Alphabets], [Index]]
in
  Sort

Power Query solution 3 for Sequential Group Numbering Logic, proposed by Alejandro Simón 🇵🇦 🇪🇸:

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Idx = Table.AddIndexColumn(Source, "Idx", 1), 
  Group1 = Table.Group(Idx, {"Alphabets"}, {{"A", each _}}, 0), 
  Group2 = Table.Combine(
    Table.Group(Group1, {"Alphabets"}, {{"B", each Table.AddIndexColumn(_, "Answer", 1)}})[B]
  )[[A], [Answer]], 
  TblA = Table.ExpandTableColumn(Group2, "A", Table.ColumnNames(Group2[A]{0})), 
  Sort = Table.Sort(TblA, {{"Idx", 0}}), 
  Sol = Table.ReplaceValue(
    Sort, 
    each [Answer], 
    each if [Alphabets] = null then 0 else [Answer], 
    Replacer.ReplaceValue, 
    {"Answer"}
  )[[Alphabets], [Answer]]
in
  Sol

Power Query solution 4 for Sequential Group Numbering Logic, proposed by Abdallah Ally:

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Group = Table.Group(Source, "Alphabets", {"Data", each [Alphabets]}, 0), 
  Addind = Table.AddIndexColumn(Group, "Index", 1, 1), 
  AddCol = Table.AddColumn(
    Addind, 
    "Answer Expected", 
    each 
      if [Alphabets] = null then
        0
      else
        List.Count(List.Select(List.FirstN(Addind[Alphabets], [Index]), (x) => x = [Alphabets]))
  ), 
  Result = Table.ExpandListColumn(AddCol, "Data")[[Alphabets], [Answer Expected]]
in
  Result

Power Query solution 5 for Sequential Group Numbering Logic, proposed by Ahmed Ariem:

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Count = Table.AddColumn(Source, "Count", each 1), 
  Gr1 = Table.Group(Count, {"Alphabets"}, {"tmp", each _}, 0, (a, b) => Number.From(b <> a)), 
  Order = Table.AddIndexColumn(Gr1, "Order", 1, 1, Int64.Type), 
  Gr2 = Table.Group(Order, {"Alphabets"}, {"tmp", (x) => Table.AddIndexColumn(x, "Idx", 1, 1)}), 
  Expand1 = Table.ExpandTableColumn(
    Gr2, 
    "tmp", 
    {"Alphabets", "tmp", "Order", "Idx"}, 
    {"Alphabetss", "tmp", "Order", "Idx"}
  ), 
  Expand2 = Table.ExpandTableColumn(Expand1, "tmp", {"Count"}, {"Count"}), 
  Sort = Table.Sort(Expand2, {{"Order", Order.Ascending}}), 
  AddColumn = Table.AddColumn(
    Sort, 
    "Answer Expected", 
    each if [Alphabets] = null then 0 else if [Idx] = [Count] then [Count] else [Idx]
  ), 
  AddCol = Table.RemoveColumns(AddColumn, {"Alphabetss", "Count", "Order", "Idx"})
in
  AddCol

Solving the challenge of Sequential Group Numbering Logic with Excel

Excel solution 1 for Sequential Group Numbering Logic, proposed by Bo Rydobon 🇹🇭:

=MAP(A2:A18,
    LAMBDA(a,
    SUM((DROP(
        a:A1,
        -1
    )<>a:A2)*(a:A2=a)*(a>0))))

Excel solution 2 for Sequential Group Numbering Logic, proposed by Bo Rydobon 🇹🇭:

=LET(a,A2:A18,b,SCAN(0,DROP(VSTACK(0,a),-1)<>a,SUM),MAP(a,b,LAMBDA(c,d,(c>0)*XMATCH(d,UNIQUE(FILTER(b,a=c))))))

Excel solution 3 for Sequential Group Numbering Logic, proposed by John V.:

=MAP(A2:A18,A1:A17,LAMBDA(x,y,SUM((x>0)*(A2:x=x)*(A1:y<>x))))
=MAP(A2:A18,LAMBDA(x,SUM((x>0)*(A2:x=x)*(DROP(A1:x,-1)<>x))))

Excel solution 4 for Sequential Group Numbering Logic, proposed by Kris Jaganah:

=LET(a,
    A2:A18,
    b,
    SEQUENCE(
        ROWS(
            a
        )
    ),
    c,
    SORT(FILTER(HSTACK(
        a,
        b
    ),
    (OFFSET(
        a,
        -1,
        
    )<>a)*(a<>""))),
    d,
    TAKE(
        c,
        ,
        1
    ),
    e,
    TAKE(
        b,
        ROWS(
            d
        )
    )-XMATCH(
        d,
        d
    )+1,
    IF(
        a="",
        0,
        XLOOKUP(
            b,
            DROP(
        c,
        ,
        1
    ),
            e,
            ,
            -1
        )
    ))

Excel solution 5 for Sequential Group Numbering Logic, proposed by Julian Poeltl:

=BYROW(LET(L,A2:A18,DROP(REDUCE(0,TOROW(UNIQUE(L),1),LAMBDA(A,B,HSTACK(A,IF(L=B,SCAN(0,L,LAMBDA(C,D,IF(D=B,C+1,C)))-SCAN(0,VSTACK(0,DROP((DROP(L,1)=L)*(L=B),-1)),SUM),0)))),,1)),SUM)

Excel solution 6 for Sequential Group Numbering Logic, proposed by Timothée BLIOT:

Excel solution 7 for Sequential Group Numbering Logic, proposed by Oscar Mendez Roca Farell:

=MAP(A2:A18, LAMBDA(a, LET(C, COUNTIFS, r, A1:a, C(r,a)-C(DROP(r,1), a, DROP(r,-1), a))))

Excel solution 8 for Sequential Group Numbering Logic, proposed by Sunny Baggu:

=LET(
 _u, UNIQUE(TOCOL(A2:A18, 3)),
 _s, SEQUENCE(ROWS(A2:A18)),
 _v, DROP(
 REDUCE(
 "",
 _u,
 LAMBDA(x, y,
 VSTACK(
 x,
 LET(
 _c, N(A2:A18 = y),
 _f, FILTER(_s, _c),
 _a, 1 - VSTACK(0, N(DROP(_f, 1) - DROP(_f, -1) = 1)),
 _b, SCAN(0, _a, LAMBDA(a, v, a + v)),
 _d, SCAN(0, _a * _b, LAMBDA(a, v, IF(v = 0, a, v))),
 HSTACK(_f, _d)
 )
 )
 )
 ),
 1
 ),
 XLOOKUP(_s, TAKE(_v, , 1), TAKE(_v, , -1), 0)
)

Excel solution 9 for Sequential Group Numbering Logic, proposed by LEONARD OCHEA 🇷🇴:

=MAP(
    A2:A18,
    A1:A17,
    LAMBDA(
        a,
        b,
        COUNTIF(
            A2:a,
            a
        )-COUNTIFS(
            A2:a,
            a,
             A1:b,
            a
        )
    )
)

Excel solution 10 for Sequential Group Numbering Logic, proposed by Md. Zohurul Islam:

Excel solution 11 for Sequential Group Numbering Logic, proposed by Jaroslaw Kujawa:

=1*DROP(TEXTSPLIT(REDUCE("";DROP(REDUCE("";A2:A18;LAMBDA(a;x;LET(y;IF(ISBLANK(x);"";IF(RIGHT(TAKE(a;-1);1)<>x;"|"&x;x));VSTACK(a;y))));1);LAMBDA(a;x;LET(ax;a&";"&x;lax;IF(x="";0;(LEN(ax)-LEN(SUBSTITUTE(ax;IF(LEFT(x;1)="|";x;"|"&x);"")))/2);ax&"?"&lax)));"?";";");1;1)

Excel solution 12 for Sequential Group Numbering Logic, proposed by Philippe Brillault:

Excel solution 13 for Sequential Group Numbering Logic, proposed by Gabriel Pugliese:

=LET(z,A2:A18,uz,UNIQUE(TOCOL(IF(z>"",z,NA()),3)),
REDUCE(0,uz,LAMBDA(a,v,
LET(e,IF(z=v,1,0),
a+IF(e,SCAN(0,(VSTACK(0,DROP(e,-1))=0)*(e=1),LAMBDA(a,v,a+v)),0)))))

Excel solution 14 for Sequential Group Numbering Logic, proposed by Tomasz Jakóbczyk:

=--(A2<>A1)
B2: =SUMIF($A$2:A2,A2,$C$2:C2)

Solving the challenge of Sequential Group Numbering Logic with Python

Python solution 1 for Sequential Group Numbering Logic, proposed by Konrad Gryczan, PhD:

import pandas as pd
path = "596 Increment Sequences.xlsx"
input = pd.read_excel(path, usecols="A", nrows=18).fillna("")
test = pd.read_excel(path, usecols="B", nrows=18)
input['nr'] = (input['Alphabets'] != input['Alphabets'].shift()).cumsum()
input['nr2'] = input.groupby('Alphabets')['nr'].rank(method='dense').astype(int)
input['Answer Expected'] = input['Alphabets'].notna().astype(int) * input['nr2'].where(input['Alphabets'] != "", 0)
print(all(input['Answer Expected'] == test['Answer Expected'])) # True

Python solution 2 for Sequential Group Numbering Logic, proposed by Artur Pilipczuk:

Aditya Kumar Darak 🇮🇳 
path=r"Excel_Challenge_596 - Increment Sequences.xlsx"
import polars as pl
pl.Config.set_fmt_table_cell_list_len(1000)
pl.Config.set_tbl_rows(-1)
df=pl.read_excel(path,sheet_name="Sheet1",columns="A",drop_empty_rows=False).fill_null(pl.lit("0"))
df=(df
.with_row_index("index")
.with_columns(pl.when(pl.col("Alphabets")==pl.col("Alphabets").shift(1)).then(None).otherwise(pl.col("index")).alias("index").fill_null(strategy='forward'))
.with_columns(rank=pl.col("index").rank('dense').over("Alphabets")
 )
.with_columns(pl.when(pl.col("Alphabets")==pl.lit("0")).then(None).otherwise(pl.col("Alphabets")).alias("Alphabets"),
pl.when(pl.col("Alphabets")==pl.lit("0")).then(pl.lit(0)).otherwise(pl.col("rank")).alias("ind"))
.select(pl.col("Alphabets"),pl.col("ind").alias("Answer Expected"))
)
print(df)

Python solution 3 for Sequential Group Numbering Logic, proposed by Artur Pilipczuk:

path=r"Excel_Challenge_596 - Increment Sequences.xlsx"
import polars as pl
pl.Config.set_fmt_table_cell_list_len(1000)
pl.Config.set_tbl_rows(-1)
df=pl.read_excel(path,sheet_name="Sheet1",columns="A",drop_empty_rows=False).fill_null(pl.lit("0"))
answer=pl.read_excel(path,sheet_name="Sheet1",columns="B",drop_empty_rows=False)
# print(df)
df=(
df.with_row_index("index")
.with_columns(pl.when(pl.col("Alphabe&ts")==pl.col("Alphabets").shift(1)).then(None).otherwise(pl.col("index")).alias("index").fill_null(strategy='forward')
)
.group_by(["index","Alphabets"],maintain_order=True).agg(pl.col("Alphabets").alias("gr"))
.with_columns(ind=pl.cum_count("Alphabets").over("Alphabets"))
.explode("gr",)
.with_columns(pl.when(pl.col("Alphabets")==pl.lit("0")).then(None).otherwise(pl.col("Alphabets")).alias("Alphabets"),
 pl.when(pl.col("Alphabets")==pl.lit("0")).then(pl.lit(0)).otherwise(pl.col("ind")).alias("ind"))
.select(pl.col("Alphabets"),pl.col("ind").alias("Answer Expected"))
)
print(df)

Solving the challenge of Sequential Group Numbering Logic with Python in Excel

Python in Excel solution 1 for Sequential Group Numbering Logic, proposed by Alejandro Campos:

alphabet_list = xl("A2:A18")[0].fillna('')
group_count = {}
result = []
current_char = None
for char in alphabet_list:
 if char == "":
 result.append(0)
 current_char = None
 else:
 if char != current_char:
 group_count[char] = group_count.get(char, 0) + 1
 current_char = char
 result.append(group_count[char])
result

Python in Excel solution 2 for Sequential Group Numbering Logic, proposed by Aditya Kumar Darak 🇮🇳:

data = xl("A1:A18", headers=True).fillna("")
strt = {}
prev = 0
data["Index"] = [
 [
 strt.update({i: strt.get(i, 0) + (1 if prev != i else 0)}),
 prev := i,
 strt[i] if i else 0,
 ][-1]
 for i in data["Alphabets"]
]
data

Python in Excel solution 3 for Sequential Group Numbering Logic, proposed by Aditya Kumar Darak 🇮🇳:

data = xl("A1:A18", headers=True)
strt = {}
prev = 0
result = [
 (
 strt.update({i: strt.get(i, 0) + (1 if prev != i else 0)}),
 prev := i,
 strt[i] if i else 0,
 )
 for i in data["Alphabets"]
]
data["Index"] = [i[-1] for i in result]
data

Python in Excel solution 4 for Sequential Group Numbering Logic, proposed by Anshu Bantra:

df = xl("A2:A18", headers=False).fillna('0')
answer, temp = [], []
occurrence = {}
for _ in range(len(df[0])):
 val = df[0][_]
 if (val not in temp):
 occurrence[val] = 1
 temp.append(val)
 elif  ( val in temp and val == df[0][_-1] ):
 temp.append(val)
 else:
 occurrence[val] += 1
 if val == '0':
 answer.append(0)
 else:
 answer.append(occurrence[val])
answer

Solving the challenge of Sequential Group Numbering Logic with R

R solution 1 for Sequential Group Numbering Logic, proposed by Konrad Gryczan, PhD:

library(tidyverse)
library(readxl)
 
path = "Excel/596 Increment Sequences.xlsx"
input = read_excel(path, range = "A1:A18")
test = read_excel(path, range = "B1:B18")
result = input %>%
 mutate(nr = consecutive_id(Alphabets)) %>%
 mutate(nr2 = dense_rank(nr), .by = Alphabets) %>%
 mutate(`Answer Expected` = ifelse(is.na(Alphabets), 0, nr2))
all.equal(result$`Answer Expected`, test$`Answer Expected`)
#> [1] TRUE

Solving the challenge of Sequential Group Numbering Logic with Power Query

Solving the challenge of Sequential Group Numbering Logic with Excel

Solving the challenge of Sequential Group Numbering Logic with Python

Solving the challenge of Sequential Group Numbering Logic with Python in Excel

Solving the challenge of Sequential Group Numbering Logic with R

Leave a Reply Cancel reply