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

 

[phillip87]

Ok, how about

Fluff.xlsm
	A	B	C	D	E	F	G	H
1
2	MS	ST01	A	A	1	MS-ST01-AA		MS-ST01-AA-01-01
3	MS	ST02	A	B	2	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-ST02-AB-01-01
13								MS-ST02-AB-01-02
14								MS-ST02-AB-01-03
15								MS-ST02-AB-01-04
16								MS-ST02-AB-01-05
17								MS-ST02-AB-01-06
18								MS-ST02-AB-01-07
19								MS-ST02-AB-01-08
20								MS-ST02-AB-01-09
21								MS-ST02-AB-01-10
22								MS-ST02-AB-02-01
23								MS-ST02-AB-02-02
24								MS-ST02-AB-02-03
25								MS-ST02-AB-02-04
26								MS-ST02-AB-02-05
27								MS-ST02-AB-02-06
28								MS-ST02-AB-02-07
29								MS-ST02-AB-02-08
30								MS-ST02-AB-02-09
31								MS-ST02-AB-02-10
32								MS-ST04-AD-01-01
33								MS-ST04-AD-01-02
34								MS-ST04-AD-01-03
35								MS-ST04-AD-01-04
36								MS-ST04-AD-01-05
37								MS-ST04-AD-01-06
38								MS-ST04-AD-01-07
39								MS-ST04-AD-01-08
40								MS-ST04-AD-01-09
41								MS-ST04-AD-01-10
42								MS-ST04-AD-02-01
43								MS-ST04-AD-02-02
44								MS-ST04-AD-02-03
45								MS-ST04-AD-02-04
46								MS-ST04-AD-02-05
47								MS-ST04-AD-02-06
48								MS-ST04-AD-02-07
49								MS-ST04-AD-02-08
50								MS-ST04-AD-02-09
51								MS-ST04-AD-02-10
52
Master

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

Wow love the work thank you, I will have a good read over this and try to understand the code and build my knowledge of how it works, thank you for this.

 

[Fluff]

You're welcome & thanks for the feedback.

 

[phillip87]

Ok, how about

Fluff.xlsm
	A	B	C	D	E	F	G	H
1
2	MS	ST01	A	A	1	MS-ST01-AA		MS-ST01-AA-01-01
3	MS	ST02	A	B	2	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-ST02-AB-01-01
13								MS-ST02-AB-01-02
14								MS-ST02-AB-01-03
15								MS-ST02-AB-01-04
16								MS-ST02-AB-01-05
17								MS-ST02-AB-01-06
18								MS-ST02-AB-01-07
19								MS-ST02-AB-01-08
20								MS-ST02-AB-01-09
21								MS-ST02-AB-01-10
22								MS-ST02-AB-02-01
23								MS-ST02-AB-02-02
24								MS-ST02-AB-02-03
25								MS-ST02-AB-02-04
26								MS-ST02-AB-02-05
27								MS-ST02-AB-02-06
28								MS-ST02-AB-02-07
29								MS-ST02-AB-02-08
30								MS-ST02-AB-02-09
31								MS-ST02-AB-02-10
32								MS-ST04-AD-01-01
33								MS-ST04-AD-01-02
34								MS-ST04-AD-01-03
35								MS-ST04-AD-01-04
36								MS-ST04-AD-01-05
37								MS-ST04-AD-01-06
38								MS-ST04-AD-01-07
39								MS-ST04-AD-01-08
40								MS-ST04-AD-01-09
41								MS-ST04-AD-01-10
42								MS-ST04-AD-02-01
43								MS-ST04-AD-02-02
44								MS-ST04-AD-02-03
45								MS-ST04-AD-02-04
46								MS-ST04-AD-02-05
47								MS-ST04-AD-02-06
48								MS-ST04-AD-02-07
49								MS-ST04-AD-02-08
50								MS-ST04-AD-02-09
51								MS-ST04-AD-02-10
52
Master

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

I have copied the formula in however I am getting the #CALC! error on the cells? I have changed the cells from E&F to F&G but that shouldn't be the problem.

 

[phillip87]

I have looked up the error but cannot understand how to fix it.

 

[Fluff]

Check that the cells with the quantity either have a number, or are completely empty.

 

[phillip87]

Check that the cells with the quantity either have a number, or are completely empty.

Thank you, works perfectly. and a cheeky follow up. how might I change it to 20 line instead of 10, I tried changing the 10's to 20's but it did not threw it out of sequence.

 

[Fluff]

Try it like

=LET(q,20,TOCOL(IF(SEQUENCE(,MAX(E2:E100*q))<=E2:E100*q,F2:F100&"-",1/0),2)&DROP(REDUCE("",E2:E100,LAMBDA(x,y,IF(y>0,VSTACK(x,TEXT(INT(SEQUENCE(y*q,,0,1/q))+1,"00")),x))),1)&"-"&TEXT(MOD(SEQUENCE(SUM(E2:E100)*q,,0),q)+1,"00"))

Just change the value of q at the start of the formula to whatever you want

 

[phillip87]

Try it like

=LET(q,20,TOCOL(IF(SEQUENCE(,MAX(E2:E100*q))<=E2:E100*q,F2:F100&"-",1/0),2)&DROP(REDUCE("",E2:E100,LAMBDA(x,y,IF(y>0,VSTACK(x,TEXT(INT(SEQUENCE(y*q,,0,1/q))+1,"00")),x))),1)&"-"&TEXT(MOD(SEQUENCE(SUM(E2:E100)*q,,0),q)+1,"00"))

Just change the value of q at the start of the formula to whatever you want

absolute legend, you have saved so much paint in the upcoming project we would have been creating thousands of locations copying and pasting.

 

[Fluff]

You're welcome & thanks for the feedback.

 

[phillip87]

I have hit an issue with the formula, anything over the 4th column gets its initial result overridden with the first result of the 4th column 04-0 creating duplicates in the end result.

=IFERROR(LET(q,20,TOCOL(IF(SEQUENCE(,MAX('location recorder'!F2:F160*q))<='location recorder'!F2:F160*q,'location recorder'!G2:G160&"-",1/0),2)&DROP(REDUCE("",'location recorder'!F2:F160,LAMBDA(x,y,IF(y>0,VSTACK(x,TEXT(INT(SEQUENCE(y*q,,0,1/q))+1,"00")),x))),1)&"-"&TEXT(MOD(SEQUENCE(SUM('location recorder'!F2:F160)*q,,0),q)+1,"00")),"")