Create Rows Based on Cell Values

[77winston]

Hi, Hope someone can assist with this query.

I have an excel sheet with 2 Columns. sample below

Name	Product
Paul	Apple
Sara	Orange
Adam	Berry
	Peach

What I'm trying to do is, for each value in Column 1, I need to create 4 rows as per column 2.
Expected

Paul	Apple
Paul	Orange
Paul	Berry
Paul	Peach

Is there a formula I can use to do this?

Thanks

 

[Fluff]

How about

+Fluff v2.xlsm
	L	M	N	O	P
1	Name	Product		Name	Product
2	Paul	Apple		Paul	Apple
3	Sara	Orange		Paul	Orange
4	Adam	Berry		Paul	Berry
5		Peach		Paul	Peach
6				Sara	Apple
7				Sara	Orange
8				Sara	Berry
9				Sara	Peach
10				Adam	Apple
11				Adam	Orange
12				Adam	Berry
13				Adam	Peach
14
15
16
17
18
19
Main

Cell Formulas
Range	Formula
O2:O17	O2	=IFERROR(INDEX($L$2:$L$4,INT((ROW(A1)-1)/COUNTA($M$2:$M$5))+1),"")
P2:P17	P2	=IF(O2="","",INDEX($M$2:$M$5,MOD(ROW(A1)-1,4)+1))

 

[77winston]

Great! thank you.

 

[Peter_SSs]

Great! thank you.

Just to be sure/careful ..

- If any rows are subsequently inserted at the top of the sheet the results will be incorrect.
- Despite the formulas being copied down extra rows, if new values are added to or existing values removed from columns L:M, the results will be incorrect.

I suggest that you update your Account details (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’)

IF you happen to have Excel 365 with the new dynamic array functions then you could try this which requires the shown formulas to be entered into O2 & P2 only and the other results will automatically 'spill' to other required rows and also automatically adjust if values are added/removed or new rows inserted above.

I have made the assumption that there would not be blanks among the listed values.

20 10 10.xlsm
	L	M	N	O	P
1	Name	Product		Name	Product
2	Paul	Apple		Paul	Apple
3	Sara	Orange		Paul	Orange
4	Adam	Berry		Paul	Berry
5		Peach		Paul	Peach
6				Sara	Apple
7				Sara	Orange
8				Sara	Berry
9				Sara	Peach
10				Adam	Apple
11				Adam	Orange
12				Adam	Berry
13				Adam	Peach
14
Expand Lists

Cell Formulas
Range	Formula
O2:O13	O2	=INDEX(L2:L100,SEQUENCE(COUNTA(L2:L100)*COUNTA(M2:M100),,1,1/COUNTA(M2:M100)))
P2:P13	P2	=INDEX(M2:M100,MOD(ROW(O2#)-ROW(O$1)-1,COUNTA(M2:M100))+1)
Dynamic array formulas.

 

[77winston]

Just to be sure/careful ..

- If any rows are subsequently inserted at the top of the sheet the results will be incorrect.
- Despite the formulas being copied down extra rows, if new values are added to or existing values removed from columns L:M, the results will be incorrect.

I suggest that you update your Account details (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’)

IF you happen to have Excel 365 with the new dynamic array functions then you could try this which requires the shown formulas to be entered into O2 & P2 only and the other results will automatically 'spill' to other required rows and also automatically adjust if values are added/removed or new rows inserted above.

I have made the assumption that there would not be blanks among the listed values.

20 10 10.xlsm
	L	M	N	O	P
1	Name	Product		Name	Product
2	Paul	Apple		Paul	Apple
3	Sara	Orange		Paul	Orange
4	Adam	Berry		Paul	Berry
5		Peach		Paul	Peach
6				Sara	Apple
7				Sara	Orange
8				Sara	Berry
9				Sara	Peach
10				Adam	Apple
11				Adam	Orange
12				Adam	Berry
13				Adam	Peach
14
Expand Lists

Cell Formulas
Range	Formula
O2:O13	O2	=INDEX(L2:L100,SEQUENCE(COUNTA(L2:L100)*COUNTA(M2:M100),,1,1/COUNTA(M2:M100)))
P2:P13	P2	=INDEX(M2:M100,MOD(ROW(O2#)-ROW(O$1)-1,COUNTA(M2:M100))+1)
Dynamic array formulas.

Thank you for the clarification.

 

[Peter_SSs]

Thank you for the clarification.

No problem. Thanks for updating your profile.

Since you do not have Excel 365, here is an alternative that also automatically adjusts if values are added/removed & does not result in incorrect values if new rows are inserted above.
O2 & P2 formulas copied down as far as you might ever need.
Column R can be hidden once the formulas are entered.

20 10 10.xlsm
	L	M	N	O	P	Q	R
1	Name	Product		Name	Product		3
2	Paul	Apple		Paul	Apple		4
3	Sara	Orange		Paul	Orange		12
4	Adam	Berry		Paul	Berry
5		Peach		Paul	Peach
6				Sara	Apple
7				Sara	Orange
8				Sara	Berry
9				Sara	Peach
10				Adam	Apple
11				Adam	Orange
12				Adam	Berry
13				Adam	Peach
14
15
16
17
Expand Lists (2)

Cell Formulas
Range	Formula
O2:O17	O2	=IF(ROWS(O$2:O2)>R$3,"",INDEX(L$2:L$100,1+INT((ROWS(O$2:O2)-1)/R$2)))
P2:P17	P2	=IF(O2="","",INDEX(M$2:M$100,COUNTIF(O$2:O2,O2)))
R1	R1	=COUNTA(L2:L100)
R2	R2	=COUNTA(M2:M100)
R3	R3	=R1*R2

 

[77winston]

No problem. Thanks for updating your profile.

Since you do not have Excel 365, here is an alternative that also automatically adjusts if values are added/removed & does not result in incorrect values if new rows are inserted above.
O2 & P2 formulas copied down as far as you might ever need.
Column R can be hidden once the formulas are entered.

20 10 10.xlsm
	L	M	N	O	P	Q	R
1	Name	Product		Name	Product		3
2	Paul	Apple		Paul	Apple		4
3	Sara	Orange		Paul	Orange		12
4	Adam	Berry		Paul	Berry
5		Peach		Paul	Peach
6				Sara	Apple
7				Sara	Orange
8				Sara	Berry
9				Sara	Peach
10				Adam	Apple
11				Adam	Orange
12				Adam	Berry
13				Adam	Peach
14
15
16
17
Expand Lists (2)

Cell Formulas
Range	Formula
O2:O17	O2	=IF(ROWS(O$2:O2)>R$3,"",INDEX(L$2:L$100,1+INT((ROWS(O$2:O2)-1)/R$2)))
P2:P17	P2	=IF(O2="","",INDEX(M$2:M$100,COUNTIF(O$2:O2,O2)))
R1	R1	=COUNTA(L2:L100)
R2	R2	=COUNTA(M2:M100)
R3	R3	=R1*R2

Great! Thank you so much.

 

[Peter_SSs]

No problem. Glad to help.