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

P

phillip87

Board Regular
Joined
Jan 28, 2019
Messages
69
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
 

Attachments

  • Mr excel.PNG
    Mr excel.PNG
    30.4 KB · Views: 19
Sort by date Sort by votes
Fluff said:
Ok, how about
Fluff.xlsm
ABCDEFGH
1
2MSST01AA1MS-ST01-AAMS-ST01-AA-01-01
3MSST02AB2MS-ST02-ABMS-ST01-AA-01-02
4MSST03AC0MS-ST03-ACMS-ST01-AA-01-03
5MSST04AD2MS-ST04-ADMS-ST01-AA-01-04
6MS-ST01-AA-01-05
7MS-ST01-AA-01-06
8MS-ST01-AA-01-07
9MS-ST01-AA-01-08
10MS-ST01-AA-01-09
11MS-ST01-AA-01-10
12MS-ST02-AB-01-01
13MS-ST02-AB-01-02
14MS-ST02-AB-01-03
15MS-ST02-AB-01-04
16MS-ST02-AB-01-05
17MS-ST02-AB-01-06
18MS-ST02-AB-01-07
19MS-ST02-AB-01-08
20MS-ST02-AB-01-09
21MS-ST02-AB-01-10
22MS-ST02-AB-02-01
23MS-ST02-AB-02-02
24MS-ST02-AB-02-03
25MS-ST02-AB-02-04
26MS-ST02-AB-02-05
27MS-ST02-AB-02-06
28MS-ST02-AB-02-07
29MS-ST02-AB-02-08
30MS-ST02-AB-02-09
31MS-ST02-AB-02-10
32MS-ST04-AD-01-01
33MS-ST04-AD-01-02
34MS-ST04-AD-01-03
35MS-ST04-AD-01-04
36MS-ST04-AD-01-05
37MS-ST04-AD-01-06
38MS-ST04-AD-01-07
39MS-ST04-AD-01-08
40MS-ST04-AD-01-09
41MS-ST04-AD-01-10
42MS-ST04-AD-02-01
43MS-ST04-AD-02-02
44MS-ST04-AD-02-03
45MS-ST04-AD-02-04
46MS-ST04-AD-02-05
47MS-ST04-AD-02-06
48MS-ST04-AD-02-07
49MS-ST04-AD-02-08
50MS-ST04-AD-02-09
51MS-ST04-AD-02-10
52
Master
Cell Formulas
RangeFormula
H2:H51H2=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:F5F2=A2&"-"&B2&"-"&C2&D2
Dynamic array formulas.
Click to expand...
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.
 
Upvote 0

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
You're welcome & thanks for the feedback.
 
Upvote 0
Fluff said:
Ok, how about
Fluff.xlsm
ABCDEFGH
1
2MSST01AA1MS-ST01-AAMS-ST01-AA-01-01
3MSST02AB2MS-ST02-ABMS-ST01-AA-01-02
4MSST03AC0MS-ST03-ACMS-ST01-AA-01-03
5MSST04AD2MS-ST04-ADMS-ST01-AA-01-04
6MS-ST01-AA-01-05
7MS-ST01-AA-01-06
8MS-ST01-AA-01-07
9MS-ST01-AA-01-08
10MS-ST01-AA-01-09
11MS-ST01-AA-01-10
12MS-ST02-AB-01-01
13MS-ST02-AB-01-02
14MS-ST02-AB-01-03
15MS-ST02-AB-01-04
16MS-ST02-AB-01-05
17MS-ST02-AB-01-06
18MS-ST02-AB-01-07
19MS-ST02-AB-01-08
20MS-ST02-AB-01-09
21MS-ST02-AB-01-10
22MS-ST02-AB-02-01
23MS-ST02-AB-02-02
24MS-ST02-AB-02-03
25MS-ST02-AB-02-04
26MS-ST02-AB-02-05
27MS-ST02-AB-02-06
28MS-ST02-AB-02-07
29MS-ST02-AB-02-08
30MS-ST02-AB-02-09
31MS-ST02-AB-02-10
32MS-ST04-AD-01-01
33MS-ST04-AD-01-02
34MS-ST04-AD-01-03
35MS-ST04-AD-01-04
36MS-ST04-AD-01-05
37MS-ST04-AD-01-06
38MS-ST04-AD-01-07
39MS-ST04-AD-01-08
40MS-ST04-AD-01-09
41MS-ST04-AD-01-10
42MS-ST04-AD-02-01
43MS-ST04-AD-02-02
44MS-ST04-AD-02-03
45MS-ST04-AD-02-04
46MS-ST04-AD-02-05
47MS-ST04-AD-02-06
48MS-ST04-AD-02-07
49MS-ST04-AD-02-08
50MS-ST04-AD-02-09
51MS-ST04-AD-02-10
52
Master
Cell Formulas
RangeFormula
H2:H51H2=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:F5F2=A2&"-"&B2&"-"&C2&D2
Dynamic array formulas.
Click to expand...
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.
 
Upvote 0
Check that the cells with the quantity either have a number, or are completely empty.
 
Upvote 0
Fluff said:
Check that the cells with the quantity either have a number, or are completely empty.
Click to expand...
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.
 
Upvote 0
Try it like
Excel Formula: 
=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
 
Upvote 0
Fluff said:
Try it like
Excel Formula: 
=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
Click to expand...
absolute legend, you have saved so much paint in the upcoming project we would have been creating thousands of locations copying and pasting.
 
Upvote 0
You're welcome & thanks for the feedback.
 
Upvote 0
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.

1694995497164.png


=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")),"")
 
Upvote 0
You must log in or register to reply here.

Similar threads

L
  • Solved
copy cell and cell above onto different sheet, under the next available line.
Replies
5
Views
128
Alex Blakenburg
A
C
  • Solved
Count number of non-empty lines in a multiline cell
2
Replies
11
Views
243
Fluff
Fluff
ChrisMac1
  • Question Question
VBA - Compare specific data in one column on one sheet, with the same column on 4 other sheets, copy specific cells if match found.
Replies
1
Views
369
ChrisMac1
ChrisMac1
P
  • Solved
Duplicate number of lines based on conditions
Replies
7
Views
166
Anthony47
A
Q
  • Question Question
Macro needed to insert 4 rows in table, copy data from above, copy data from another sheet and paste special (transpose values) to those new rows...re
Replies
1
Views
229
sijpie
sijpie

Forum statistics

Threads
1,223,229
Messages
6,170,881
Members
452,364
Latest member
springate

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top