Create Rows Based on Cell Values

77winston

New Member
Joined
Sep 11, 2013
Messages
27
Office Version
  1. 2013
Platform
  1. Windows
Hi, Hope someone can assist with this query.

I have an excel sheet with 2 Columns. sample below
NameProduct
PaulApple
SaraOrange
AdamBerry
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
PaulApple
PaulOrange
PaulBerry
PaulPeach

Is there a formula I can use to do this?

Thanks
 

Some videos you may like

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,794
Office Version
  1. 365
Platform
  1. Windows

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,400
Office Version
  1. 365
Platform
  1. Windows
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
LMNOP
1NameProductNameProduct
2PaulApplePaulApple
3SaraOrangePaulOrange
4AdamBerryPaulBerry
5PeachPaulPeach
6SaraApple
7SaraOrange
8SaraBerry
9SaraPeach
10AdamApple
11AdamOrange
12AdamBerry
13AdamPeach
14
Expand Lists
Cell Formulas
RangeFormula
O2:O13O2=INDEX(L2:L100,SEQUENCE(COUNTA(L2:L100)*COUNTA(M2:M100),,1,1/COUNTA(M2:M100)))
P2:P13P2=INDEX(M2:M100,MOD(ROW(O2#)-ROW(O$1)-1,COUNTA(M2:M100))+1)
Dynamic array formulas.
 

77winston

New Member
Joined
Sep 11, 2013
Messages
27
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

⚡ 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
LMNOP
1NameProductNameProduct
2PaulApplePaulApple
3SaraOrangePaulOrange
4AdamBerryPaulBerry
5PeachPaulPeach
6SaraApple
7SaraOrange
8SaraBerry
9SaraPeach
10AdamApple
11AdamOrange
12AdamBerry
13AdamPeach
14
Expand Lists
Cell Formulas
RangeFormula
O2:O13O2=INDEX(L2:L100,SEQUENCE(COUNTA(L2:L100)*COUNTA(M2:M100),,1,1/COUNTA(M2:M100)))
P2:P13P2=INDEX(M2:M100,MOD(ROW(O2#)-ROW(O$1)-1,COUNTA(M2:M100))+1)
Dynamic array formulas.
Thank you for the clarification.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,400
Office Version
  1. 365
Platform
  1. Windows
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
LMNOPQR
1NameProductNameProduct3
2PaulApplePaulApple4
3SaraOrangePaulOrange12
4AdamBerryPaulBerry
5PeachPaulPeach
6SaraApple
7SaraOrange
8SaraBerry
9SaraPeach
10AdamApple
11AdamOrange
12AdamBerry
13AdamPeach
14  
15  
16  
17  
Expand Lists (2)
Cell Formulas
RangeFormula
O2:O17O2=IF(ROWS(O$2:O2)>R$3,"",INDEX(L$2:L$100,1+INT((ROWS(O$2:O2)-1)/R$2)))
P2:P17P2=IF(O2="","",INDEX(M$2:M$100,COUNTIF(O$2:O2,O2)))
R1R1=COUNTA(L2:L100)
R2R2=COUNTA(M2:M100)
R3R3=R1*R2
 

77winston

New Member
Joined
Sep 11, 2013
Messages
27
Office Version
  1. 2013
Platform
  1. Windows
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
LMNOPQR
1NameProductNameProduct3
2PaulApplePaulApple4
3SaraOrangePaulOrange12
4AdamBerryPaulBerry
5PeachPaulPeach
6SaraApple
7SaraOrange
8SaraBerry
9SaraPeach
10AdamApple
11AdamOrange
12AdamBerry
13AdamPeach
14  
15  
16  
17  
Expand Lists (2)
Cell Formulas
RangeFormula
O2:O17O2=IF(ROWS(O$2:O2)>R$3,"",INDEX(L$2:L$100,1+INT((ROWS(O$2:O2)-1)/R$2)))
P2:P17P2=IF(O2="","",INDEX(M$2:M$100,COUNTIF(O$2:O2,O2)))
R1R1=COUNTA(L2:L100)
R2R2=COUNTA(M2:M100)
R3R3=R1*R2
Great! Thank you so much.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,400
Office Version
  1. 365
Platform
  1. Windows
No problem. Glad to help. :)
 

Watch MrExcel Video

Forum statistics

Threads
1,128,129
Messages
5,628,865
Members
416,347
Latest member
AT2021

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
Top