Apr 28 2020 12:25 PM - edited Apr 28 2020 12:28 PM
Hello everybody,
I need a formula that puts a column of numbers as following:
every number of the column should appear 8 times, instead of only one time. After that, in the column on the left, each of the 8 equal numbers should be numbered from 1 to 8.
It would be great, if you could help me, please!
Thank you.
Apr 28 2020 11:09 PM
@chiarastmn Please have a look at the attached workbook. It generates sequences of numbers 1-8 in column E, depending on the count of numbers entered in column C. Then, in F1, you'll find a formula that needs to be copied down as far as needed in order to generate the list of numbers from C in groups of eight.
Apr 29 2020 03:36 AM - edited Apr 29 2020 04:01 AM
This is a task that becomes trivial with Office 365.
First define a named formula 'k' that provides a zero base index
= SEQUENCE(8*ROWS(numbers),1,0)
The cycle of occurrence numbers is then given by
= 1+MOD(k,8) 8)
whilst the blocks of numbers are returned by
= INDEX( numbers, 1 + QUOTIENT(k, 88))
If the source list is a table named 'numbers' the output grows dynamically as values are added to the table. Why oh why wasn't Excel always like this?
ps Without SEQUENCE, 'k' could be defined by
= ROW( INDIRECT("1:"&8*N) ) - 1