Add an Index column for the Question table with a separate counter for each stock
📌 Challenge Details and Links
Challenge Number: 117
Challenge Difficulty: ⭐
📥Download Sample File
📥Link to the solutions on LinkedIn
📥Link to the solution on YouTube
Solving the challenge of Add Index Column! Part 3 with Power Query
Power Query solution 1 for Add Index Column! Part 3, proposed by Zoran Milokanović:
let
Source = Excel.CurrentWorkbook(){[Name = "Input"]}[Content],
S = Table.FromRows(
List.Accumulate(
Table.ToRows(Source),
{},
(b, n) => b & {n & {List.Count(List.Select(b, each _{0}? = n{0})) + 1}}
),
Table.ColumnNames(Source) & {"index"}
)
in
SPower Query solution 2 for Add Index Column! Part 3, proposed by Brian Julius:
let
Source = Table.AddIndexColumn(
Table.PromoteHeaders(Excel.CurrentWorkbook(){[Name = "Table1"]}[Content]),
"OrigOrder"
),
Group = Table.Group(Source, {"Stock"}, {{"All", each Table.AddIndexColumn(_, "Index", 1, 1)}}),
Expand = Table.ExpandTableColumn(
Group,
"All",
{"Price", "OrigOrder", "Index"},
{"Price", "OrigOrder", "Index"}
),
Sort = Table.RemoveColumns(Table.Sort(Expand, {{"OrigOrder", Order.Ascending}}), "OrigOrder")
in
SortPower Query solution 3 for Add Index Column! Part 3, proposed by Konrad Gryczan, PhD:
let
Source = Excel.CurrentWorkbook(){[Name = "Tabela1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(
Source,
{{"Stock", type text}, {"Price", Int64.Type}}
),
#"Added Index" = Table.AddIndexColumn(#"Changed Type", "Indeks", 1, 1, Int64.Type),
#"Grouped Rows" = Table.Group(
#"Added Index",
{"Stock"},
{{"all", each _, type table [Stock = nullable text, Price = nullable number, Indeks = number]}}
),
#"Added Custom" = Table.AddColumn(
#"Grouped Rows",
"indexed",
each Table.AddIndexColumn([all], "index", 1)
),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom", {"all"}),
#"Expanded {0}" = Table.ExpandTableColumn(
#"Removed Columns",
"indexed",
{"Price", "Indeks", "index"},
{"Price", "Indeks", "index"}
),
#"Sorted Rows" = Table.Sort(#"Expanded {0}", {{"Indeks", Order.Ascending}}),
#"Removed Columns1" = Table.RemoveColumns(#"Sorted Rows", {"Indeks"})
in
#"Removed Columns1"Power Query solution 4 for Add Index Column! Part 3, proposed by Luan Rodrigues:
let
Fonte = Tabela1,
gp = Table.Group(Fonte, {"Stock"}, {{"tab", each Table.AddIndexColumn(_, "index", 1, 1)}})[tab],
cmb = Table.Combine(gp),
rst = Table.Sort(cmb, {each List.PositionOf(Fonte[Price], [Price])})
in
rstPower Query solution 5 for Add Index Column! Part 3, proposed by Ramiro Ayala Chávez:
let
S = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
a = Table.Combine(Table.Group(S,{"Stock"},{"G", each Table.AddIndexColumn(_,"index",1)})[[G]][G]),
Sol = Table.Sort(a,{each List.PositionOf(S[Price],[Price])})
in
SolPower Query solution 6 for Add Index Column! Part 3, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Idx = Table.AddIndexColumn(Source, "Idx", 1, 1),
Stock = Table.Combine(
Table.Group(
Idx,
{"Stock"},
{
{
"A",
each
let
a = Table.AddIndexColumn(_, "C", 1),
b = Table.RemoveColumns(a, "C")
in
a
}
}
)[A]
),
Sol = Table.RemoveColumns(Table.Sort(Stock, {{"Idx", 0}}), "Idx")
in
SolPower Query solution 7 for Add Index Column! Part 3, proposed by Abdallah Ally:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Result = Table.AddColumn(
Source,
"index",
each List.Count(
List.Select(List.FirstN(Source[Stock], Table.PositionOf(Source, _) + 1), (x) => x = [Stock])
)
)
in
ResultPower Query solution 8 for Add Index Column! Part 3, proposed by Kris Jaganah:
let
A = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
B = Table.Group(A, {"Stock"}, {"All", each Table.AddIndexColumn(_, "index", 1)}),
C = Table.ExpandTableColumn(B, "All", {"Price", "index"}),
D = Table.Sort(C, {each List.PositionOf(A[Price], [Price]), 0})
in
DPower Query solution 9 for Add Index Column! Part 3, proposed by 🇮🇷 Navid Esmaeilzadeh اسماعیل زاده:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
A = Table.AddIndexColumn(Source, "I", 1, 1),
B = Table.Group(
A,
{"Stock"},
{{"T", each _, type table [Stock = nullable text, Price = nullable number, I = number]}}
),
C = Table.AddColumn(B, "T2", each Table.AddIndexColumn([T], "index", 1, 1)),
D = Table.SelectColumns(C, {"T2"}),
E = Table.ExpandTableColumn(
D,
"T2",
{"Stock", "Price", "I", "index"},
{"Stock", "Price", "I", "index"}
),
F = Table.Sort(E, {{"I", Order.Ascending}}),
G = Table.SelectColumns(F, {"Stock", "Price", "index"})
in
GPower Query solution 10 for Add Index Column! Part 3, proposed by Ahmed Ariem:
let
f = (t)=> Table.ReplaceValue(t,(x)=>x,(x)=>x,(x,y,z)=>List.PositionOf(Table.SelectRows(t, each [Stock]=y[Stock])[Index],x,0)+1,{"Index"}),
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
AddDubl = Table.DuplicateColumn(Source, "Price", "Index"),
to = f(AddDubl)
in
toSolving the challenge of Add Index Column! Part 3 with Excel
Excel solution 1 for Add Index Column! Part 3, proposed by :
=LET(b,
B3:B13,
r,
ROW(
b
),
HSTACK(B3:C13,
MMULT((b=TOROW(
b
))*(r>=TOROW(
r
)),
r^0)))Excel solution 2 for Add Index Column! Part 3, proposed by Oscar Mendez Roca Farell:
=HSTACK(
B3:C13,
MAP(
B3:B13,
LAMBDA(
b,
SUM(
N(
+B3:b=b
)
)
)
)
)Excel solution 3 for Add Index Column! Part 3, proposed by Julian Poeltl:
=DROP(
REDUCE(
HSTACK(
"",
"index"
),
B3:B13,
LAMBDA(
A,
B,
VSTACK(
A,
HSTACK(
B,
IFERROR(
ROWS(
FILTER(
TAKE(
A,
,
1
),
TAKE(
A,
,
1
)=TAKE(
B,
,
1
)
)
),
0
)+1
)
)
)
),
,
1
)Excel solution 4 for Add Index Column! Part 3, proposed by Kris Jaganah:
=MAP(
B3:B13,
LAMBDA(
x,
COUNTIF(
B3:x,
x
)
)
)Excel solution 5 for Add Index Column! Part 3, proposed by Sunny Baggu:
=HSTACK( B3:C13, MAP(
B3:B13,
LAMBDA(
x,
COUNTIF(
B3:x,
x
)
)
))
Bigger solution below :
2️⃣
=LET( l,
LAMBDA(
x,
y, LET(
_a,
N(
x = y
),
_b,
SCAN(
0,
_a,
LAMBDA(
a,
v,
IF(
v,
a + v,
a
)
)
) * _a,
_b
) ), HSTACK( B3:C13, BYROW(
DROP(
REDUCE(
0,
UNIQUE(
B3:B13
),
LAMBDA(
a,
v,
HSTACK(
a,
l(
B3:B13,
v
)
)
)
),
,
1
),
LAMBDA(
g,
SUM(
g
)
)
) ))Excel solution 6 for Add Index Column! Part 3, proposed by Asheesh Pahwa:
=MAP(
B3:B13,
LAMBDA(
x,
COUNTIF(
B3:x,
x
)
)
)Excel solution 7 for Add Index Column! Part 3, proposed by Asheesh Pahwa:
=LET(
d,
DROP(
REDUCE(
"",
UNIQUE(
B3:B13
),
LAMBDA(
x,
y,
HSTACK(
x,
LET(
e,
N(
B3:B13=y
),
s,
SCAN(
0,
e,
LAMBDA(
a,
v,
IF(
v,
a+1,
a
)
)
)*e,
s
)
)
)
),
,
1
), t,
TAKE(
d,
,
1
),
HSTACK(
B3:C13,
IF(
t,
t,
TAKE(
d,
,
-1
)
)
)
)Excel solution 8 for Add Index Column! Part 3, proposed by Gerson Pineda:
=MAP(
B3:B13,
LAMBDA(
x,
COUNTIF(
B3:x,
x
)
)
)Excel solution 9 for Add Index Column! Part 3, proposed by Hamidi Hamid:
=HSTACK(B3:C13,MAP(B3:B13,LAMBDA(a,COUNTIF(B3:a,a))))Excel solution 10 for Add Index Column! Part 3, proposed by Hussein SATOUR:
=MAP(B3:B13,LAMBDA(x,COUNTIF(B3:x,x)))Excel solution 11 for Add Index Column! Part 3, proposed by Pieter de B.:
=HSTACK(
B3:C13,
MAP(
B3:B13,
LAMBDA(
b,
SUM(
N(
B2:b=b
)
)
)
)
)Excel solution 12 for Add Index Column! Part 3, proposed by Rick Rothstein:
=COUNTIF(
B$3:B3,
B3
)Excel solution 13 for Add Index Column! Part 3, proposed by Rick Rothstein:
=MAP(
B3:B13,
LAMBDA(
x,
COUNTIF(
B3:x,
x
)
)
)Excel solution 14 for Add Index Column! Part 3, proposed by Songglod Petchamras:
=COUNTIF(
$B$3:B3,
B3
)
then drag down to the last row
Dynamic array
=MAP(
B3:B13,
LAMBDA(
r,
COUNTIF(
B3:r,
r
)
)
)Excel solution 15 for Add Index Column! Part 3, proposed by Wisdom Uboh:
=COUNTIF(
A$1:A2,
A2
)
=IF(
A2="",
"",
COUNTIF(
A$1:A2,
A2
)
)Solving the challenge of Add Index Column! Part 3 with Python
Python solution 1 for Add Index Column! Part 3, proposed by Konrad Gryczan, PhD:
import pandas as pd
path = "CH-117 Add Index Column.xlsx"
input = pd.read_excel(path, usecols="B:C", skiprows=1)
test = pd.read_excel(path, usecols="E:G", skiprows=1).rename(columns=lambda x: x.replace(".1", ""))
result = input.assign(index = input.groupby("Stock").cumcount() + 1)
print(result.equals(test)) # TrueSolving the challenge of Add Index Column! Part 3 with Python in Excel
Python in Excel solution 1 for Add Index Column! Part 3, proposed by Abdallah Ally:
df = xl("B2:C13", headers=True)
# Perform data munging
df['index'] = df.groupby('Stock').transform('cumcount') + 1
# Display the final results
dfPython in Excel solution 2 for Add Index Column! Part 3, proposed by Alejandro Campos:
df = xl("B2:C13", headers=True)
df['Index'] = df.groupby('Stock').cumcount() + 1
dfSolving the challenge of Add Index Column! Part 3 with R
R solution 1 for Add Index Column! Part 3, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
path = "files/CH-117 Add Index Column.xlsx"
input = read_excel(path, range = "B2:C13")
test = read_excel(path, range = "E2:G13")
result = input %>%
mutate(index = row_number(), .by = Stock)
all.equal(result, test)
#> [1] TRUESolving the challenge of Add Index Column! Part 3 with Google Sheets
Google Sheets solution 1 for Add Index Column! Part 3, proposed by Milan Shrimali:
Google sheets:
=let(call1,B3:B13,call2,C3:C13,main, tocol(byrow(unique(call1),lambda(x,byrow(x,lambda(z,map( let(a,torow(FILTER(call2,call1=x)),BYCOL(vstack(a,transpose(sequence(counta(a),1,1,1))),lambda(x,join("-",X)))),lambda(y,z&"-"&y))))))),tble,ARRAYFORMULA(SPLIT(main,"-")),hstack(call1,call2,map(call1,call2,lambda(x,y,torow(FILTER(CHOOSECOLS(tble,3),(choosecols(tble,1)=x)*(choosecols(tble,2)=y)))))))Google Sheets solution 2 for Add Index Column! Part 3, proposed by Peter Krkos:
PowerQuery Solution:
https://docs.google.com/spreadsheets/d/1zR5IZLz8OT76vhaPEHfsPrw8-RDKnLyyqS49IJjdhFk/edit?gid=1438311988#gid=1438311988