Create columns with 'Steps' and 'Runs' based on column with sequential numbers but varying rows, 'Steps' repeat every 4th number.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 |
Content OrganizationData Blending and CleanupRe: Create columns with 'Steps' and 'Runs' based on column with sequential numbers but varying rows, 'Steps' repeat every 4th number.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.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.That worked. Thanks!
Re: Create columns with 'Steps' and 'Runs' based on column with sequential numbers but varying rows, 'Steps' repeat every 4th number.Thank you!
