topic Create columns with 'Steps' and 'Runs' based on column with sequential numbers but varying rows, 'Steps' repeat every 4th number. in Discussions

Create columns with 'Steps' and 'Runs' based on column with sequential numbers but varying rows, 'Steps' repeat every 4th number.

Ctron87 — Mon, 30 Oct 2023 13:09:13 GMT

Having a tough time trying to word this question! But I have a data set, 'Sample', that start at 1 and is continuous but number of rows per value is not consistent (ie. 1,1,1,2,2,3,3,3,3,4,4,5...). Format for sample and desired columns below. Thanks!

Sample	Step # (desired)	Run # (desired)
1	1	1
1	1	1
2	2	1
3	3	1
3	3	1
4	4	1
5	1	2
5	1	2
6	2	2
7	3	2
7	3	2
7	3	2
7	3	2
8	4	2
9	1	3
9	1	3
9	1	3
10	2	3
10	2	3
10	2	3
11	3	3
12	4	3
12	4	3
12	4	3
13	1	4
14	2	4
15	3	4
16	4	4
16	4	4

Re: Create columns with 'Steps' and 'Runs' based on column with sequential numbers but varying rows, 'Steps' repeat every 4th number.

jthi — Mon, 30 Oct 2023 13:24:56 GMT

You can get steps using Mod()

Mod(:Sample - 1, 4) + 1

and run with division and Ceiling()

Ceiling(:Sample / 4)

Re: Create columns with 'Steps' and 'Runs' based on column with sequential numbers but varying rows, 'Steps' repeat every 4th number.

bculver — Mon, 30 Oct 2023 13:26:54 GMT

Assuming your data is consistent with the sample data, with 4 samples per run, here are two simple formulas

New Column("Step", Numeric, "Continuous", Format("Best", 12), Formula(1 + Mod(:Sample - 1, 4)));
New Column("Run", Numeric, "Continuous", Format("Best", 12), Formula(Ceiling(:Sample / 4)));

Re: Create columns with 'Steps' and 'Runs' based on column with sequential numbers but varying rows, 'Steps' repeat every 4th number.

Ctron87 — Mon, 30 Oct 2023 14:23:13 GMT

That worked. Thanks!

Re: Create columns with 'Steps' and 'Runs' based on column with sequential numbers but varying rows, 'Steps' repeat every 4th number.

Ctron87 — Mon, 30 Oct 2023 14:23:26 GMT

Thank you!