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]

I currently have this formula which does almost exactly what I am looking for except am needing it to multiply the value in column E by 10 but also increase the number sequentially

=INDEX(B2:B6,MATCH(SEQUENCE(SUM(E:E),,0),SUBTOTAL(9,OFFSET(E1,0,0,SEQUENCE(COUNT(E:E))))))

 

[Fluff]

What version of Excel are you using?

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’)

 

[Fluff]

If you have 365 how about

=TOCOL(IF(SEQUENCE(,MAX(E2:E100*10))<=E2:E100*10,B2:B100,1/0),2)

 

[phillip87]

If you have 365 how about

=TOCOL(IF(SEQUENCE(,MAX(E2:E100*10))<=E2:E100*10,B2:B100,1/0),2)

Yes I am on 365, thank you for this it works perfectly. however I have not given the right breakdown of what I need sorry.

so the blue section will result in a partial location being made which looks like this MS-ST01-AA, based on the column E (the number of shelves) I need it to auto fill to things. The shelf and then the Bins, this means if E has 2 then it should return MS-ST01-AA-01 & MS-ST01-AA-02 for the shelves but also create 10 bins for each of these to look like MS-ST01-AA-01-01 through to MS-ST01-AA-01-10 and the same for the second entry.

This is because I want to make it easy for the people recording the layouts only having to count the number of shelves and from there we will allow every shelf to hold 20 items but I don't want the people walking around having to copy the same data for every shelf over and over as there could be 100 shelves in a room.

 

[Fluff]

Can you post some sample data, as I don't know what columns your data is in.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[phillip87]

Can you post some sample data, as I don't know what columns your data is in.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

I will try this when I am on the home computer, through work systems I am limited but I will try to add a better example onto here for you.

 

[phillip87]

I will try this when I am on the home computer, through work systems I am limited but I will try to add a better example onto here for you.

 

[phillip87]

this should return this result

MS-ST01-AA-01-01 through to MS-ST01-AA-01-10
MS-ST01-AA-02-01 through to MS-ST01-AA-02-10
MS-ST01-AB-01-01 through to MS-ST01-AB-01-10
MS-ST01-AB-02-01 through to MS-ST01-AB-02-10
MS-ST01-AB-03-01 through to MS-ST01-AB-03-10
MS-ST02-AA-01-01 through to MS-ST02-AA-01-10
MS-ST02-AA-02-01 through to MS-ST02-AA-02-10
MS-ST02-AA-03-01 through to MS-ST02-AA-03-10
MS-ST02-AA-04-01 through to MS-ST02-AA-04-10

 

[Fluff]

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.