digest
Short Summary
Main recommendation: Build the sequence in one formula that combines an expanding range (for the running row count) with INT (for the integer part), then join the two parts with an ampersand and a space to create the text string of the form "18440 1, 18441 1, 18442 2..." in which the base number increments and the repeat counter stays at 1 for 50 records before moving to 2. The video also highlights two resources: Charles Williams' article on improving formula performance and XLTV's history of Excel.
Key Points
- The base number is built from an expanding range: B$2:B2 is copied downward so ROWS counts 1, 2, 3, and so on.
- Subtracting one and dividing by 50 with INT yields 0 for the first 50 rows, 1 for the next 50, and so on, which produces the repeating second number.
- Joining the two calculations with an ampersand and a space creates the final text string in each cell.
- Changing the starting number and the repeat count in assumption cells makes the same formula produce different sequences, such as 1, 2, 3, 4, 5 repeated.
- Two named resources were recommended: Charles Williams' article on formula performance, which includes a copy-paste timer for workbooks, and XLTV's Excel history episode featuring Sylvia.
Details Worth Noting
- Concrete instructions:
- In cell B2, enter a formula using the expandable range B$2:B2, lock the row with the dollar sign, and copy it down with Control+Enter and double-click (fill down).
- Use ROWS(B$2:B2) so the count increases with each row; subtract one from the ROWS result to get a starting value of zero.
- Place the repeat count (50) in an assumption cell, then compute INT of the ROWS-minus-one value divided by 50, and add one to produce the repeating counter.
- Use F4 to lock references to the start number and steal the portion of the formula that adds one, so the increment runs down all rows.
- Join the base-number formula and the repeat-counter formula with an ampersand, placing a space in double quotes between them: first part & " " & second part.
- Fill the completed formula down the column, then change the start number and repeat count cells to test the pattern (for example, start 1852 with repeat 5 yields 1, 2, 3, 4, 5, 1, 2, 3, 4, 5).
- Copy the formatting from the source cell to keep the new cells consistent, then fill down.
- The video shows the sequence starting at 18440, with the second number staying at 1 for 50 records before moving to 2.
- The video suggests reading Charles Williams' article for formula performance and volatile-function guidance, and the XLTV Excel history episode.
Bottom Line
A single formula combining an expanding range, INT, and a text join can generate a sequence whose base number increments while a second number repeats in blocks, and both inputs can be adjusted in assumption cells.
