Create new rows based on cell value

[jo15765]

I have an excel workbook where column G is going to list the total number of boxes, if that value is 1 no action is needed, if that number is greater than 1, I need to duplicate the data until the number of rows for that data = the total number of boxes, and increment the value in column E each row.

I'm sure this is confusing so I am attaching a sample workbook with my current format (described above) and once manipulated the desired format as a worksheet.

Thanks to all!

Seems I can't upload a workbook so here are images:

Current format:

Desired format:

 

[jasonb75]

You can use XL2BB to post samples to the board so that we don't have to retype them (see link in my signature block below this reply).

Here's a way of doing what you want by using a helper column to keep a running total of the number of boxes in the source table (column E) with the output starting in column G. I've used a simplified example but it should be enough to give you an idea of how it works.

Book1
	A	B	C	D	E	F	G	H	I	J
1	id	name	num	Boxes	run
2	331	a	5	6	1		331	a	5	6
3	181	b	2	1	7		331	a	5	6
4	64	c	8	1	8		331	a	5	6
5							331	a	5	6
6							331	a	5	6
7							331	a	5	6
8							181	b	2	1
9							64	c	8	1
Sheet1

Cell Formulas
Range	Formula
E2:E4	E2	=SUM(D$1:D1,1)
G2:J9	G2	=IF(ROWS($G$2:$G2)>MAX($E$2:$E$4),"",INDEX($A$2:$D$4,MATCH(ROWS($G$2:$G2),$E$2:$E$4),0))
Dynamic array formulas.

 

[jo15765]

@jasonb75 - thanks for the help, very close to what I need. I've followed your signature to learn the xl2bb tool so I can provide usable sample data

This is what my source data looks like

sampledata.xlsx
	A	B	C	D	E	F	G	H
1	saleid	UserName	num of items	item description	box number	QFB	total boxes	mgr
2	331	pam123	5	socks	1	4	6	xya
3	181	zen129	2	mittens	1	2	1	aym
4	64	mlb202	8	ovens	1	2	1	sua
Current Format

And this is how I need it formatted:

sampledata.xlsx
	A	B	C	D	E	F	G	H
1	saleid	UserName	num of items	item description	box number	QFB	total boxes	mgr
2	331	pam123	5	socks	1	4	6	xya
3	331	pam123	5	socks	2	4	6	xya
4	331	pam123	5	socks	3	4	6	xya
5	331	pam123	5	socks	4	4	6	xya
6	331	pam123	5	socks	5	4	6	xya
7	331	pam123	5	socks	6	4	6	xya
8	181	zen129	2	mittens	1	2	1	aym
9	64	mlb202	8	ovens	1	2	1	sua
Desired Format

Only thing missing from the formula you provided (which is KILLER bc I didn't know you could do that in a formula) is the incremental column showing the box number

 

[jasonb75]

I missed the box number when I looked at it before, it makes it a little more challenging but not impossible.

I've done this using dynamic arrays in office 365 but I think that it should work in older versions as well.

Book1
	A	B	C	D	E	F	G	H	I
1	saleid	UserName	num of items	item description	box number	QFB	total boxes	mgr	rt
2	331	pam123	5	socks	1	4	6	xya	1
3	181	zen129	2	mittens	1	2	1	aym	7
4	64	mlb202	8	ovens	1	2	1	sua	8
5
6	saleid	UserName	num of items	item description	box number	QFB	total boxes	mgr
7	331	pam123	5	socks	1	4	6	xya
8	331	pam123	5	socks	2	4	6	xya
9	331	pam123	5	socks	3	4	6	xya
10	331	pam123	5	socks	4	4	6	xya
11	331	pam123	5	socks	5	4	6	xya
12	331	pam123	5	socks	6	4	6	xya
13	181	zen129	2	mittens	1	2	1	aym
14	64	mlb202	8	ovens	1	2	1	sua
Sheet1

Cell Formulas
Range	Formula
I2:I4	I2	=SUM(G$1:G1,1)
E7:E14	E7	=IF(A7="","",IF(E6=G6,1,N(E6)+1))
F7:H14	F7	=IF(ROWS($A$7:$A7)>MAX($I$2:$I$4),"",INDEX($F$2:$H$4,MATCH(ROWS($A$7:$A7),$I$2:$I$4),0))
A7:D14	A7	=IF(ROWS($A$7:$A7)>MAX($I$2:$I$4),"",INDEX($A$2:$D$4,MATCH(ROWS($A$7:$A7),$I$2:$I$4),0))
Dynamic array formulas.