Creation of formula

%3CLINGO-SUB%20id%3D%22lingo-sub-1345092%22%20slang%3D%22en-US%22%3ECreation%20of%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1345092%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20everybody%2C%3C%2FP%3E%3CP%3EI%20need%20a%20formula%20that%20puts%20a%20column%20of%20numbers%20as%20following%3A%3C%2FP%3E%3CP%3Eevery%20number%20of%20the%20column%20should%20appear%208%20times%2C%20instead%20of%20only%20one%20time.%20After%20that%2C%20in%20the%20column%20on%20the%20left%2C%20each%20of%20the%208%20equal%20numbers%20should%20be%20numbered%20from%201%20to%208.%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20would%20be%20great%2C%20if%20you%20could%20help%20me%2C%20please!%3C%2FP%3E%3CP%3EThank%20you.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1345092%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1346362%22%20slang%3D%22en-US%22%3ERe%3A%20Creation%20of%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1346362%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F640359%22%20target%3D%22_blank%22%3E%40chiarastmn%3C%2FA%3E%26nbsp%3BPlease%20have%20a%20look%20at%20the%20attached%20workbook.%20It%20generates%20sequences%20of%20numbers%201-8%20in%20column%20E%2C%20depending%20on%20the%20count%20of%20numbers%20entered%20in%20column%20C.%20Then%2C%20in%20F1%2C%20you'll%20find%20a%20formula%20that%20needs%20to%20be%20copied%20down%20as%20far%20as%20needed%20in%20order%20to%20generate%20the%20list%20of%20numbers%20from%20C%20in%20groups%20of%20eight.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1346561%22%20slang%3D%22en-US%22%3ERe%3A%20Creation%20of%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1346561%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3Bthank%20you%20so%2C%20so%20much!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1346650%22%20slang%3D%22en-US%22%3ERe%3A%20Creation%20of%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1346650%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F640359%22%20target%3D%22_blank%22%3E%40chiarastmn%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20is%20a%20task%20that%20becomes%20trivial%20with%20Office%20365.%3C%2FP%3E%3CP%3EFirst%20define%20a%20named%20formula%20'%3CSTRONG%3Ek%3C%2FSTRONG%3E'%20that%20provides%20a%20zero%20base%20index%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3CFONT%3E%3D%20SEQUENCE(8*ROWS(numbers)%2C1%2C0)%3C%2FFONT%3E%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CFONT%3EThe%20cycle%20of%20occurrence%20numbers%20is%20then%20given%20by%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3CFONT%3E%3D%201%2BMOD(k%2C8)%26nbsp%3B%3CLI-EMOJI%20id%3D%22lia_smiling-face-with-sunglasses%22%20title%3D%22%3Asmiling_face_with_sunglasses%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FFONT%3E%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%3CFONT%3Ewhilst%20the%20blocks%20of%20numbers%20are%20returned%20by%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3CFONT%3E%3D%20INDEX(%20numbers%2C%201%20%2B%20QUOTIENT(k%2C%2088))%3C%2FFONT%3E%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CFONT%3EIf%20the%20source%20list%20is%20a%20table%20named%20'%3CSTRONG%3Enumbers%3C%2FSTRONG%3E'%20the%20output%20grows%20dynamically%20as%20values%20are%20added%20to%20the%20table.%26nbsp%3B%20Why%20oh%20why%20wasn't%20Excel%20always%20like%20this%3F%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CFONT%3Eps%20Without%20%3CSTRONG%3ESEQUENCE%3C%2FSTRONG%3E%2C%20'%3CSTRONG%3Ek%3C%2FSTRONG%3E'%20could%20be%20defined%20by%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3CFONT%3E%3CFONT%3E%3D%20ROW(%20INDIRECT(%221%3A%22%26amp%3B8*N)%20)%20-%201%3C%2FFONT%3E%3C%2FFONT%3E%3C%2FSTRONG%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

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. 

3 Replies
Highlighted

@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.

Highlighted

@Riny_van_Eekelen thank you so, so much!

Highlighted

@chiarastmn 

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) 

whilst the blocks of numbers are returned by

= INDEX( numbers, 1 + QUOTIENT(k, 8)

 

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