Copy lines of data based on a cell number, 1 = 10 copies

[phillip87]

I have a sheet to be filled out by staff which shows how many racks and shelves they have in a storeroom, I need a second sheet that copies this data the number of time equal to the amount of shelving they have in order to create bin locations on the shelf.

Each shelf of each cabinet bay needs to have 10 locations to accommodate the stock that will be put on it which at the moment has to be done manually as every site/storeroom is different.

example - section AA has 1 shelf - the result I need is - section AA listed 10 times, this means if they put a 2 for the number of shelves then it would copy it 20 times.

In the example I would want them to fill in just 1 line of the blue section (not including "Bin" in order to create all 10 lines shown

 

[Fluff]

Please update your profile to show that you are using 365 as previously requested.
Also what is your data when you get the error?

 

[phillip87]

Please update your profile to show that you are using 365 as previously requested.
Also what is your data when you get the error?

Please see the screenshot attached on page 2, the sheet is working just as planned except if a location has more than 4 shelves it starts to repeat the for the first line in each new level.

example:

MS-ST01-AA-04-01
MS-ST01-AA-05-02
MS-ST01-AA-05-03
MS-ST01-AA-05-04
MS-ST01-AA-05-05
MS-ST01-AA-05-06
MS-ST01-AA-05-01
MS-ST01-AA-06-02
MS-ST01-AA-06-03
MS-ST01-AA-06-04
MS-ST01-AA-06-05
MS-ST01-AA-06-06
MS-ST01-AA-06-01
MS-ST01-AA-07-02
MS-ST01-AA-07-03
MS-ST01-AA-07-04
MS-ST01-AA-07-05
MS-ST01-AA-07-06

 

[Fluff]

That is the output, not the data. Also you have still not updated your profile.

 

[phillip87]

That is the output, not the data. Also you have still not updated your profile.

The data is per image, this can change depending on the location and rooms. This front page is fine and no 2 lines are ever the same, just need to figure out why the formula is duplicating and switching the rows around as soon as it goes over 4 "shelfs"

 

[Fluff]

Is there any reason why you have not updated your profile as requested?
If you cannot be bothered to help us by showing your Excel version & platform, why should be bother to help you?

I'm not sure what your problem is as it works for me.

Fluff.xlsm
	A	B	C	D	E	F	G	H	I
1
2		MS	ST01	A	A	5	MS-ST01-AA		MS-ST01-AA-01-01
3		MS	ST02	A	B	6	MS-ST02-AB		MS-ST01-AA-01-02
4		MS	ST03	A	C	0	MS-ST03-AC		MS-ST01-AA-01-03
5		MS	ST04	A	D	2	MS-ST04-AD		MS-ST01-AA-01-04
6									MS-ST01-AA-01-05
7									MS-ST01-AA-01-06
8									MS-ST01-AA-01-07
9									MS-ST01-AA-01-08
10									MS-ST01-AA-01-09
11									MS-ST01-AA-01-10
12									MS-ST01-AA-02-01
13									MS-ST01-AA-02-02
14									MS-ST01-AA-02-03
15									MS-ST01-AA-02-04
16									MS-ST01-AA-02-05
17									MS-ST01-AA-02-06
18									MS-ST01-AA-02-07
19									MS-ST01-AA-02-08
20									MS-ST01-AA-02-09
21									MS-ST01-AA-02-10
22									MS-ST01-AA-03-01
23									MS-ST01-AA-03-02
24									MS-ST01-AA-03-03
25									MS-ST01-AA-03-04
26									MS-ST01-AA-03-05
27									MS-ST01-AA-03-06
28									MS-ST01-AA-03-07
29									MS-ST01-AA-03-08
30									MS-ST01-AA-03-09
31									MS-ST01-AA-03-10
32									MS-ST01-AA-04-01
33									MS-ST01-AA-04-02
34									MS-ST01-AA-04-03
35									MS-ST01-AA-04-04
36									MS-ST01-AA-04-05
37									MS-ST01-AA-04-06
38									MS-ST01-AA-04-07
39									MS-ST01-AA-04-08
40									MS-ST01-AA-04-09
41									MS-ST01-AA-04-10
42									MS-ST01-AA-05-01
43									MS-ST01-AA-05-02
44									MS-ST01-AA-05-03
45									MS-ST01-AA-05-04
46									MS-ST01-AA-05-05
47									MS-ST01-AA-05-06
48									MS-ST01-AA-05-07
49									MS-ST01-AA-05-08
50									MS-ST01-AA-05-09
51									MS-ST01-AA-05-10
52									MS-ST02-AB-01-01
Sheet3

Cell Formulas
Range	Formula
I2:I131	I2	=TOCOL(IF(SEQUENCE(,MAX(F2:F100*10))<=F2:F100*10,G2:G100&"-",1/0),2)&DROP(REDUCE("",F2:F100,LAMBDA(x,y,IF(y>0,VSTACK(x,TEXT(INT(SEQUENCE(y*10,,0,0.1))+1,"00")),x))),1)&"-"&TEXT(MOD(SEQUENCE(SUM(F2:F100)*10,,0),10)+1,"00")
G2:G5	G2	=B2&"-"&C2&"-"&D2&E2
Dynamic array formulas.