Need to duplicate rows of data based upon values in a cell

J

JNowell

New Member
Joined
Jun 15, 2015
Messages
11
I'm desperate. Working on Mac vs. 15.17; I had a macro that use to work in a previous version of Excel. But I cannot get it to work now. I do not not VB or code. Can anyone please help me?

I have several spreadsheets that all have over 20,000 rows of data.
I need to expand the sheet based upon one of the columns.
The sheet has a column (X) of data with years separated by commas. Example: 2000, 2001, 2015
the number of years is varied.
I need a macro that will look at this cell and create duplated rows of data based how many years exsist in the cell; Once the data is duplicated it needs to have just a single year in the (x) Column

Example:
Top row represents original row of data
Next three rows is the result I need to achieve.

[TABLE="width: 1421"]
<colgroup><col span="23"><col></colgroup><tbody>[TR]
[TD]cell A[/TD]
[TD]cell B[/TD]
[TD]cell C[/TD]
[TD]cell D[/TD]
[TD]cell E[/TD]
[TD]cell F[/TD]
[TD]cell G[/TD]
[TD]cell H[/TD]
[TD]cell I[/TD]
[TD]cell J[/TD]
[TD]cell K[/TD]
[TD]cell L[/TD]
[TD]cell M[/TD]
[TD]cell N[/TD]
[TD]cell O[/TD]
[TD]cell P[/TD]
[TD]cell Q[/TD]
[TD]cell R[/TD]
[TD]cell S[/TD]
[TD]cell T[/TD]
[TD]cell U[/TD]
[TD]cell V[/TD]
[TD]cell W[/TD]
[TD]2000, 2001, 2015[/TD]
[/TR]
[TR]
[TD]cell A[/TD]
[TD]cell B[/TD]
[TD]cell C[/TD]
[TD]cell D[/TD]
[TD]cell E[/TD]
[TD]cell F[/TD]
[TD]cell G[/TD]
[TD]cell H[/TD]
[TD]cell I[/TD]
[TD]cell J[/TD]
[TD]cell K[/TD]
[TD]cell L[/TD]
[TD]cell M[/TD]
[TD]cell N[/TD]
[TD]cell O[/TD]
[TD]cell P[/TD]
[TD]cell Q[/TD]
[TD]cell R[/TD]
[TD]cell S[/TD]
[TD]cell T[/TD]
[TD]cell U[/TD]
[TD]cell V[/TD]
[TD]cell W[/TD]
[TD]2000[/TD]
[/TR]
[TR]
[TD]cell A[/TD]
[TD]cell B[/TD]
[TD]cell C[/TD]
[TD]cell D[/TD]
[TD]cell E[/TD]
[TD]cell F[/TD]
[TD]cell G[/TD]
[TD]cell H[/TD]
[TD]cell I[/TD]
[TD]cell J[/TD]
[TD]cell K[/TD]
[TD]cell L[/TD]
[TD]cell M[/TD]
[TD]cell N[/TD]
[TD]cell O[/TD]
[TD]cell P[/TD]
[TD]cell Q[/TD]
[TD]cell R[/TD]
[TD]cell S[/TD]
[TD]cell T[/TD]
[TD]cell U[/TD]
[TD]cell V[/TD]
[TD]cell W[/TD]
[TD]2001[/TD]
[/TR]
[TR]
[TD]cell A[/TD]
[TD]cell B[/TD]
[TD]cell C[/TD]
[TD]cell D[/TD]
[TD]cell E[/TD]
[TD]cell F[/TD]
[TD]cell G[/TD]
[TD]cell H[/TD]
[TD]cell I[/TD]
[TD]cell J[/TD]
[TD]cell K[/TD]
[TD]cell L[/TD]
[TD]cell M[/TD]
[TD]cell N[/TD]
[TD]cell O[/TD]
[TD]cell P[/TD]
[TD]cell Q[/TD]
[TD]cell R[/TD]
[TD]cell S[/TD]
[TD]cell T[/TD]
[TD]cell U[/TD]
[TD]cell V[/TD]
[TD]cell W[/TD]
[TD]2015[/TD]
[/TR]
</tbody>[/TABLE]

Here is example from spreadsheet.

[TABLE="width: 2060"]
<colgroup><col span="8"><col span="8"><col span="6"><col><col></colgroup><tbody>[TR]
[TD]Model Object ID[/TD]
[TD]Section Name[/TD]
[TD]Item Group ID[/TD]
[TD]Item Group Name[/TD]
[TD]Make Object ID[/TD]
[TD]Make Name[/TD]
[TD]Model Name[/TD]
[TD]Model Years[/TD]
[TD]Color|Fiment[/TD]
[TD]Color[/TD]
[TD]Engine Szie[/TD]
[TD]Footnote[/TD]
[TD]Kits Required[/TD]
[TD]O.E.M.[/TD]
[TD]Position[/TD]
[TD]Spring Rate[/TD]
[TD]Model Name Path[/TD]
[TD]Item Object ID[/TD]
[TD]Item Name[/TD]
[TD]Item Brand[/TD]
[TD]Item Description[/TD]
[TD]Item Segment[/TD]
[TD]Unique Model Names[/TD]
[TD]Year List[/TD]
[/TR]
[TR]
[TD]1719317[/TD]
[TD]Control[/TD]
[TD]53762[/TD]
[TD]Clutch Cable[/TD]
[TD]1718762[/TD]
[TD]Arctic Cat[/TD]
[TD]400 DVX[/TD]
[TD]04-08[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]/Structures/Publication/Cat_QuadBoss/Control/Cable Assemblies/Clutch Cable/Arctic Cat/400 DVX[/TD]
[TD]53685[/TD]
[TD]414484[/TD]
[TD]QuadBoss®[/TD]
[TD]ATV Clutch Cable [/TD]
[TD]ATV|UTV[/TD]
[TD]400 DVX[/TD]
[TD]2004, 2005, 2006, 2007, 2008[/TD]
[/TR]
[TR]
[TD]1719318[/TD]
[TD]Control[/TD]
[TD]53762[/TD]
[TD]Clutch Cable[/TD]
[TD]1718763[/TD]
[TD]Can-Am[/TD]
[TD]DS 450[/TD]
[TD]10-15[/TD]
[TD][/TD]
[TD]Red[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]/Structures/Publication/Cat_QuadBoss/Control/Cable Assemblies/Clutch Cable/Can-Am/DS 450[/TD]
[TD]53726[/TD]
[TD]414525[/TD]
[TD]QuadBoss®[/TD]
[TD]ATV Clutch Cable [/TD]
[TD]ATV|UTV[/TD]
[TD]DS 450[/TD]
[TD]2010, 2011, 2012, 2013, 2014, 2015[/TD]
[/TR]
[TR]
[TD]1719319[/TD]
[TD]Control[/TD]
[TD]53762[/TD]
[TD]Clutch Cable[/TD]
[TD]1718763[/TD]
[TD]Can-Am[/TD]
[TD]DS 450 EFI X mx[/TD]
[TD]10-12[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]xx[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]/Structures/Publication/Cat_QuadBoss/Control/Cable Assemblies/Clutch Cable/Can-Am/DS 450 EFI X mx[/TD]
[TD]53726[/TD]
[TD]414525[/TD]
[TD]QuadBoss®[/TD]
[TD]ATV Clutch Cable [/TD]
[TD]ATV|UTV[/TD]
[TD]DS 450 X mx[/TD]
[TD]2010, 2011, 2012[/TD]
[/TR]
[TR]
[TD]1719320[/TD]
[TD]Control[/TD]
[TD]53762[/TD]
[TD]Clutch Cable[/TD]
[TD]1718763[/TD]
[TD]Can-Am[/TD]
[TD]DS 450 EFI X xc (2)[/TD]
[TD]09-12[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]x[/TD]
[TD][/TD]
[TD][/TD]
[TD]/Structures/Publication/Cat_QuadBoss/Control/Cable Assemblies/Clutch Cable/Can-Am/DS 450 EFI X xc (2)[/TD]
[TD]53726[/TD]
[TD]414525[/TD]
[TD]QuadBoss®[/TD]
[TD]ATV Clutch Cable [/TD]
[TD]ATV|UTV[/TD]
[TD]DS 450 X xc[/TD]
[TD]2009, 2010, 2011, 2012[/TD]
[/TR]
[TR]
[TD]1719321[/TD]
[TD]Control[/TD]
[TD]53762[/TD]
[TD]Clutch Cable[/TD]
[TD]1718763[/TD]
[TD]Can-Am[/TD]
[TD]DS 450 STD/X[/TD]
[TD]08-09[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]/Structures/Publication/Cat_QuadBoss/Control/Cable Assemblies/Clutch Cable/Can-Am/DS 450 STD@fs:X[/TD]
[TD]53726[/TD]
[TD]414525[/TD]
[TD]QuadBoss®[/TD]
[TD]ATV Clutch Cable [/TD]
[TD]ATV|UTV[/TD]
[TD]DS 450, DS 450 X[/TD]
[TD]2008, 2009[/TD]
[/TR]
[TR]
[TD]1719322[/TD]
[TD]Control[/TD]
[TD]53762[/TD]
[TD]Clutch Cable[/TD]
[TD]1718763[/TD]
[TD]Can-Am[/TD]
[TD]DS 450 X xc/X mx[/TD]
[TD]2015[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]xx[/TD]
[TD][/TD]
[TD]/Structures/Publication/Cat_QuadBoss/Control/Cable Assemblies/Clutch Cable/Can-Am/DS 450 X xc@fs:X mx[/TD]
[TD]53726[/TD]
[TD]414525[/TD]
[TD]QuadBoss®[/TD]
[TD]ATV Clutch Cable [/TD]
[TD]ATV|UTV[/TD]
[TD]DS 450 X mx, DS 450 X xc[/TD]
[TD]2015[/TD]
[/TR]
[TR]
[TD]1719323[/TD]
[TD]Control[/TD]
[TD]53762[/TD]
[TD]Clutch Cable[/TD]
[TD]1718764[/TD]
[TD]Honda[/TD]
[TD]ATC250R[/TD]
[TD]1986[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]/Structures/Publication/Cat_QuadBoss/Control/Cable Assemblies/Clutch Cable/Honda/ATC250R[/TD]
[TD]53706[/TD]
[TD]414505[/TD]
[TD]QuadBoss®[/TD]
[TD]ATV Clutch Cable [/TD]
[TD]ATV|UTV[/TD]
[TD]ATC250R[/TD]
[TD]1986[/TD]
[/TR]
[TR]
[TD]1719324[/TD]
[TD]Control[/TD]
[TD]53762[/TD]
[TD]Clutch Cable[/TD]
[TD]1718764[/TD]
[TD]Honda[/TD]
[TD]ATC250R (2)[/TD]
[TD]1985[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]/Structures/Publication/Cat_QuadBoss/Control/Cable Assemblies/Clutch Cable/Honda/ATC250R (2)[/TD]
[TD]53706[/TD]
[TD]414505[/TD]
[TD]QuadBoss®[/TD]
[TD]ATV Clutch Cable [/TD]
[TD]ATV|UTV[/TD]
[TD]ATC250R[/TD]
[TD]1985[/TD]
[/TR]
[TR]
[TD]1719325[/TD]
[TD]Control[/TD]
[TD]53762[/TD]
[TD]Clutch Cable[/TD]
[TD]1718764[/TD]
[TD]Honda[/TD]
[TD]ATC250R (3)[/TD]
[TD]82-84[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]x[/TD]
[TD][/TD]
[TD][/TD]
[TD]/Structures/Publication/Cat_QuadBoss/Control/Cable Assemblies/Clutch Cable/Honda/ATC250R (3)[/TD]
[TD]53688[/TD]
[TD]414487[/TD]
[TD]QuadBoss®[/TD]
[TD]ATV Clutch Cable [/TD]
[TD]ATV|UTV[/TD]
[TD]ATC250R[/TD]
[TD]1982, 1983, 1984[/TD]
[/TR]
</tbody>[/TABLE]


Here is the code that use to work.
Sub Split_DataPro()
Application.ScreenUpdating = False
lr = Cells(Rows.Count, "A").End(xlUp).Row
For r = lr To 2 Step -1
Set Rng1 = Cells(r, "D")
Arry = Split(Trim(Rng1), ", ")
n = UBound(Arry)
If n > 0 Then
Set Rng2 = Range("A" & r & ":H" & r)
Set Rng3 = Rng2.Resize(n, 5)
Rng3.EntireRow.Insert
For c = n To 1 Step -1
Rng2.Offset(-c, 0).Value = Rng2.Value
Next c

For c = n To 0 Step -1
Rng1.Offset(-c, 0).Value = Arry(n - c)
Next c
End If




Next r
Application.ScreenUpdating = True
End Sub
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
You must log in or register to reply here.

Similar threads

Cassiorosj
  • Question Question
How to retrieve column and row headers values for every marked cell in a grid/matrix?
Replies
0
Views
349
Cassiorosj
Cassiorosj
N
  • Question Question
Export from Excel to Access via VBA based on cell Value
Replies
0
Views
1K
NewPadawan
N
V
  • Solved
Copy value from one cell to balance cells in same row
4 5 6
Replies
50
Views
2K
maabadi
M
G
  • Question Question
Copy data based on cell value
Replies
5
Views
897
DanteAmor
DanteAmor
L
  • Question Question
Concatenate data from multiple cells in a row but grouping them based on duplicate values
2 3
Replies
29
Views
4K
mirabeau
M

Forum statistics

Threads
1,223,228
Messages
6,170,871
Members
452,363
Latest member
merico17

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