Automatically Generate Numbers based on 2 values

[WendyHubard]

Hello Experts,

I work with the Criminal Justice system and we are trying to generate a spreadsheet that will populate as below. There would be a single value for "number of sets" and multiple numbers for "Exhibit Number" - These values would then be used to populate the "Results Generated" column. Any help greatly appreciated. The spreadsheet would then be used with a mail merge document to produce labels for Photo's (have got that parted nutted out) Thanks in advance

Number Of Sets	Exhibit Number	Results Generated
5​	1​	1​
	34​	1​
	99​	1​
	etc	1​
		1​
		34​
		34​
		34​
		34​
		34​
		99​
		99​
		99​
		99​
		99​

 

[Snakehips]

@WendyHubard You may need to tweak your required ranges but maybe something like below?

MrExcel July.xlsm
	A	B	C
1	Sets	Exhibit #	Result
2	5	1	1
3		34	1
4		99	1
5			1
6			1
7			34
8			34
9			34
10			34
11			34
12			99
13			99
14			99
15			99
16			99
17
18
19
20
Sheet3

Cell Formulas
Range	Formula
C2:C20	C2	=IF($A$2*COUNTA(B$2:B$200)<ROWS(A$2:A2),"",INDEX(B$2:B200,ROUNDUP(ROWS(A$2:A2)/$A$2,)))

 

[Peter_SSs]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[Peter_SSs]

Is this a repeat of this question?

 

[WendyHubard]

Is this a repeat of this question?

Hi Pete, Have updated my details. AND. it's a different question. The original one was where the user could enter the sequential photo numbers. THey also need to be able to enter selective photo numbers (these numbers are generated by the Police system and come in different formats). I now have solutions to both scenarios with the help of the awesome guru's.

 

[WendyHubard]

Thank you Snakehips! works a treat

 

[Peter_SSs]

Hi Pete, Have updated my details. AND. it's a different question.

OK, thanks.

With 365, here is another option (only requires the formula in the top cell).

23 07 09.xlsm
	A	B	C
1	Sets	Exhibit #	Result
2	5	1	1
3		34	1
4		99	1
5			1
6			1
7			34
8			34
9			34
10			34
11			34
12			99
13			99
14			99
15			99
16			99
17
Sheet2 (2)

Cell Formulas
Range	Formula
C2:C16	C2	=INDEX(B2:B4,SEQUENCE(ROWS(B2:B4)*A2,,,1/A2))
Dynamic array formulas.

 

[WendyHubard]

Thanks Pete, Muchly appreciated. The poor folks who work within our Justice system are running Excel 2016... and from my extremely limited and vague knowledge... that version is void of supporting the "sequence" function. (a major detail I failed to mention when submitting this post). I have made a note of your formula in hope they find some funds and do an upgrade and crawl out of their cave. THanks again, appreciate your help

 

[Peter_SSs]

Yes, if you need a solution in a particular version, of course you have to tell us.

 

[Peter_SSs]

The poor folks who work within our Justice system are running Excel 2016...

In that case, another option is:

23 07 09.xlsm
	A	B	C
1	Sets	Exhibit #	Result
2	5	1	1
3		34	1
4		99	1
5			1
6			1
7			34
8			34
9			34
10			34
11			34
12			99
13			99
14			99
15			99
16			99
17
Generate Numbers

Cell Formulas
Range	Formula
C2:C17	C2	=IFERROR(INDEX(B$2:B$4,INT((ROWS(C$2:C2)-1)/A$2)+1),"")