Need help with VBA

M

mccdgxch

New Member
Joined
Jun 20, 2010
Messages
29
Office Version
  1. 365
Platform
  1. Windows
Hi,
I have a spreadsheet that really need your help. Please see the excel spreadsheet below. The above spreadsheet is format I originally had and the spreadsheet below that is what I wanted it to be. Is there a way that you can help me accomplish it using VBA?
Thank you and have a good day.

ActionLabelNumberRelationshipDetails
ReviseTY10A4793669
ReviseTY10B80544557
ReviseTY10C3221972
ReviseTY10D6018855347
ReviseTY10E18878773
ReviseTY10F145600
ReviseTY10G5473895
ReviseTY10H1482812
ReviseTY10I544579
ReviseTY10J824960
ReviseTY10K703221983
ReviseTY10L5755449
YMMMake=Toyota|Model=Corolla|Year=2008
YMMMake=Toyota|Model=Corolla|Year=2008

and I would like it to be like this below

ActionLabelNumberRelationshipDetails
ReviseTY10A4793669
YMMMake=Toyota|Model=Corolla|Year=2008
YMMMake=Toyota|Model=Corolla|Year=2008
ReviseTY10B80544557
YMMMake=Toyota|Model=Corolla|Year=2008
YMMMake=Toyota|Model=Corolla|Year=2008
ReviseTY10C3221972
YMMMake=Toyota|Model=Corolla|Year=2008
YMMMake=Toyota|Model=Corolla|Year=2008
ReviseTY10D6018855347
YMMMake=Toyota|Model=Corolla|Year=2008
YMMMake=Toyota|Model=Corolla|Year=2008
ReviseTY10E18878773
YMMMake=Toyota|Model=Corolla|Year=2008
YMMMake=Toyota|Model=Corolla|Year=2008
ReviseTY10F145600
YMMMake=Toyota|Model=Corolla|Year=2008
YMMMake=Toyota|Model=Corolla|Year=2008
ReviseTY10G5473895
YMMMake=Toyota|Model=Corolla|Year=2008
YMMMake=Toyota|Model=Corolla|Year=2008
ReviseTY10H1482812
YMMMake=Toyota|Model=Corolla|Year=2008
YMMMake=Toyota|Model=Corolla|Year=2008
ReviseTY10I544579
YMMMake=Toyota|Model=Corolla|Year=2008
YMMMake=Toyota|Model=Corolla|Year=2008
ReviseTY10J824960
YMMMake=Toyota|Model=Corolla|Year=2008
YMMMake=Toyota|Model=Corolla|Year=2008
ReviseTY10K703221983
YMMMake=Toyota|Model=Corolla|Year=2008
YMMMake=Toyota|Model=Corolla|Year=2008
ReviseTY10L5755449
YMMMake=Toyota|Model=Corolla|Year=2008
YMMMake=Toyota|Model=Corolla|Year=2008
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
Sort by date Sort by votes
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’)

Also have a look at XL2BB for providing any future sample data.

Best method depends a bit on how big your data set is. If not too big, give this a try with a copy of your workbook.
I have assumed the shown data is in columns A:E

VBA Code: 
Sub Rearrange()
  Dim vInsert As Variant
  Dim lr As Long, r As Long
 
  Application.ScreenUpdating = False
  lr = Range("A" & Rows.Count).End(xlUp).Row
  vInsert = Range("D" & lr + 1).Resize(2, 2).Value
  For r = lr To 3 Step -1
    Rows(r).Resize(2).Insert
    Cells(r, 4).Resize(2, 2).Value = vInsert
  Next r
  Application.ScreenUpdating = True
End Sub
 
Upvote 0
Hi,
Thank you for your help. I appreciate it. the data set is very big, the "Relationship" and "Details" column maybe 5000 row. Is there a way to make it faster?
Thank you
 
Upvote 0
mccdgxch said:
the data set is very big, the "Relationship" and "Details" column maybe 5000 row. Is there a way to make it faster?
Click to expand...
Yes, try the one below.

BTW, what about your version info? ;)

VBA Code: 
Sub Rearrange_v2()
  Dim vInsert As Variant, a As Variant, b As Variant
  Dim i As Long, k As Long
  
  vInsert = Range("D" & Rows.Count).End(xlUp).Offset(-1).Resize(2, 2).Value
  a = Range("A2", Range("C" & Rows.Count).End(xlUp)).Value
  ReDim b(1 To 3 * UBound(a), 1 To 5)
  For i = 1 To UBound(a)
    k = 3 * i - 2
    b(k, 1) = a(i, 1): b(k, 2) = a(i, 2): b(k, 3) = a(i, 3)
    b(k + 1, 4) = vInsert(1, 1): b(k + 1, 5) = vInsert(1, 2)
    b(k + 2, 4) = vInsert(2, 1): b(k + 2, 5) = vInsert(2, 2)
  Next i
  Range("A2").Resize(UBound(b), 5).Value = b
End Sub
 
Upvote 0
friend, what if I had this one here:
ActionLabelNumberRelationshipDetails
ReviseTY10A4793669
ReviseTY10B80544557
ReviseTY10C3221972
ReviseTY10D6018855347
ReviseTY10E18878773
ReviseTY10F145600
ReviseTY10G5473895
ReviseTY10H1482812
ReviseTY10I544579
ReviseTY10J824960
ReviseTY10K703221983
ReviseTY10L5755449
YMMMake=Toyota|Model=Corolla|Year=2008
YMMMake=Toyota|Model=Corolla|Year=2008
YMMMake=Toyota|Model=Corolla|Year=2008
YMMMake=Toyota|Model=Corolla|Year=2008
YMMMake=Toyota|Model=Corolla|Year=2008
YMMMake=Toyota|Model=Corolla|Year=2008
YMMMake=Toyota|Model=Corolla|Year=2008
YMMMake=Toyota|Model=Corolla|Year=2008
YMMMake=Toyota|Model=Corolla|Year=2008
YMMMake=Toyota|Model=Corolla|Year=2008
YMMMake=Toyota|Model=Corolla|Year=2008

and I would like it like this...

ActionLabelNumberRelationshipDetails
ReviseTY10A4793669
YMMMake=Toyota|Model=Corolla|Year=2008
YMMMake=Toyota|Model=Corolla|Year=2008
YMMMake=Toyota|Model=Corolla|Year=2008
YMMMake=Toyota|Model=Corolla|Year=2008
YMMMake=Toyota|Model=Corolla|Year=2008
YMMMake=Toyota|Model=Corolla|Year=2008
YMMMake=Toyota|Model=Corolla|Year=2008
YMMMake=Toyota|Model=Corolla|Year=2008
YMMMake=Toyota|Model=Corolla|Year=2008
YMMMake=Toyota|Model=Corolla|Year=2008
YMMMake=Toyota|Model=Corolla|Year=2008
ReviseTY10B80544557
YMMMake=Toyota|Model=Corolla|Year=2008
YMMMake=Toyota|Model=Corolla|Year=2008
YMMMake=Toyota|Model=Corolla|Year=2008
YMMMake=Toyota|Model=Corolla|Year=2008
YMMMake=Toyota|Model=Corolla|Year=2008
YMMMake=Toyota|Model=Corolla|Year=2008
YMMMake=Toyota|Model=Corolla|Year=2008
YMMMake=Toyota|Model=Corolla|Year=2008
YMMMake=Toyota|Model=Corolla|Year=2008
YMMMake=Toyota|Model=Corolla|Year=2008
YMMMake=Toyota|Model=Corolla|Year=2008
ReviseTY10C3221972
YMMMake=Toyota|Model=Corolla|Year=2008
YMMMake=Toyota|Model=Corolla|Year=2008
YMMMake=Toyota|Model=Corolla|Year=2008
YMMMake=Toyota|Model=Corolla|Year=2008
YMMMake=Toyota|Model=Corolla|Year=2008
YMMMake=Toyota|Model=Corolla|Year=2008
YMMMake=Toyota|Model=Corolla|Year=2008
YMMMake=Toyota|Model=Corolla|Year=2008
YMMMake=Toyota|Model=Corolla|Year=2008
YMMMake=Toyota|Model=Corolla|Year=2008
YMMMake=Toyota|Model=Corolla|Year=2008
ReviseTY10D6018855347
YMMMake=Toyota|Model=Corolla|Year=2008
YMMMake=Toyota|Model=Corolla|Year=2008
YMMMake=Toyota|Model=Corolla|Year=2008
YMMMake=Toyota|Model=Corolla|Year=2008
YMMMake=Toyota|Model=Corolla|Year=2008
YMMMake=Toyota|Model=Corolla|Year=2008
YMMMake=Toyota|Model=Corolla|Year=2008
YMMMake=Toyota|Model=Corolla|Year=2008
YMMMake=Toyota|Model=Corolla|Year=2008
YMMMake=Toyota|Model=Corolla|Year=2008
YMMMake=Toyota|Model=Corolla|Year=2008
ReviseTY10E18878773
YMMMake=Toyota|Model=Corolla|Year=2008
YMMMake=Toyota|Model=Corolla|Year=2008
YMMMake=Toyota|Model=Corolla|Year=2008
YMMMake=Toyota|Model=Corolla|Year=2008
YMMMake=Toyota|Model=Corolla|Year=2008
YMMMake=Toyota|Model=Corolla|Year=2008
YMMMake=Toyota|Model=Corolla|Year=2008
YMMMake=Toyota|Model=Corolla|Year=2008
YMMMake=Toyota|Model=Corolla|Year=2008
YMMMake=Toyota|Model=Corolla|Year=2008
YMMMake=Toyota|Model=Corolla|Year=2008
ReviseTY10F145600
YMMMake=Toyota|Model=Corolla|Year=2008
YMMMake=Toyota|Model=Corolla|Year=2008
YMMMake=Toyota|Model=Corolla|Year=2008
YMMMake=Toyota|Model=Corolla|Year=2008
YMMMake=Toyota|Model=Corolla|Year=2008
YMMMake=Toyota|Model=Corolla|Year=2008
YMMMake=Toyota|Model=Corolla|Year=2008
YMMMake=Toyota|Model=Corolla|Year=2008
YMMMake=Toyota|Model=Corolla|Year=2008
YMMMake=Toyota|Model=Corolla|Year=2008
YMMMake=Toyota|Model=Corolla|Year=2008
ReviseTY10G5473895
YMMMake=Toyota|Model=Corolla|Year=2008
YMMMake=Toyota|Model=Corolla|Year=2008
YMMMake=Toyota|Model=Corolla|Year=2008
YMMMake=Toyota|Model=Corolla|Year=2008
YMMMake=Toyota|Model=Corolla|Year=2008
YMMMake=Toyota|Model=Corolla|Year=2008
YMMMake=Toyota|Model=Corolla|Year=2008
YMMMake=Toyota|Model=Corolla|Year=2008
YMMMake=Toyota|Model=Corolla|Year=2008
YMMMake=Toyota|Model=Corolla|Year=2008
YMMMake=Toyota|Model=Corolla|Year=2008
ReviseTY10H1482812
YMMMake=Toyota|Model=Corolla|Year=2008
YMMMake=Toyota|Model=Corolla|Year=2008
YMMMake=Toyota|Model=Corolla|Year=2008
YMMMake=Toyota|Model=Corolla|Year=2008
YMMMake=Toyota|Model=Corolla|Year=2008
YMMMake=Toyota|Model=Corolla|Year=2008
YMMMake=Toyota|Model=Corolla|Year=2008
YMMMake=Toyota|Model=Corolla|Year=2008
YMMMake=Toyota|Model=Corolla|Year=2008
YMMMake=Toyota|Model=Corolla|Year=2008
YMMMake=Toyota|Model=Corolla|Year=2008
ReviseTY10I544579
YMMMake=Toyota|Model=Corolla|Year=2008
YMMMake=Toyota|Model=Corolla|Year=2008
YMMMake=Toyota|Model=Corolla|Year=2008
YMMMake=Toyota|Model=Corolla|Year=2008
YMMMake=Toyota|Model=Corolla|Year=2008
YMMMake=Toyota|Model=Corolla|Year=2008
YMMMake=Toyota|Model=Corolla|Year=2008
YMMMake=Toyota|Model=Corolla|Year=2008
YMMMake=Toyota|Model=Corolla|Year=2008
YMMMake=Toyota|Model=Corolla|Year=2008
YMMMake=Toyota|Model=Corolla|Year=2008
ReviseTY10J824960
YMMMake=Toyota|Model=Corolla|Year=2008
YMMMake=Toyota|Model=Corolla|Year=2008
YMMMake=Toyota|Model=Corolla|Year=2008
YMMMake=Toyota|Model=Corolla|Year=2008
YMMMake=Toyota|Model=Corolla|Year=2008
YMMMake=Toyota|Model=Corolla|Year=2008
YMMMake=Toyota|Model=Corolla|Year=2008
YMMMake=Toyota|Model=Corolla|Year=2008
YMMMake=Toyota|Model=Corolla|Year=2008
YMMMake=Toyota|Model=Corolla|Year=2008
YMMMake=Toyota|Model=Corolla|Year=2008
ReviseTY10K703221983
YMMMake=Toyota|Model=Corolla|Year=2008
YMMMake=Toyota|Model=Corolla|Year=2008
YMMMake=Toyota|Model=Corolla|Year=2008
YMMMake=Toyota|Model=Corolla|Year=2008
YMMMake=Toyota|Model=Corolla|Year=2008
YMMMake=Toyota|Model=Corolla|Year=2008
YMMMake=Toyota|Model=Corolla|Year=2008
YMMMake=Toyota|Model=Corolla|Year=2008
YMMMake=Toyota|Model=Corolla|Year=2008
YMMMake=Toyota|Model=Corolla|Year=2008
YMMMake=Toyota|Model=Corolla|Year=2008
ReviseTY10K703221983
YMMMake=Toyota|Model=Corolla|Year=2008
YMMMake=Toyota|Model=Corolla|Year=2008
YMMMake=Toyota|Model=Corolla|Year=2008
YMMMake=Toyota|Model=Corolla|Year=2008
YMMMake=Toyota|Model=Corolla|Year=2008
YMMMake=Toyota|Model=Corolla|Year=2008
YMMMake=Toyota|Model=Corolla|Year=2008
YMMMake=Toyota|Model=Corolla|Year=2008
YMMMake=Toyota|Model=Corolla|Year=2008
YMMMake=Toyota|Model=Corolla|Year=2008
YMMMake=Toyota|Model=Corolla|Year=2008
ReviseTY10L5755449
YMMMake=Toyota|Model=Corolla|Year=2008
YMMMake=Toyota|Model=Corolla|Year=2008
YMMMake=Toyota|Model=Corolla|Year=2008
YMMMake=Toyota|Model=Corolla|Year=2008
YMMMake=Toyota|Model=Corolla|Year=2008
YMMMake=Toyota|Model=Corolla|Year=2008
YMMMake=Toyota|Model=Corolla|Year=2008
YMMMake=Toyota|Model=Corolla|Year=2008
YMMMake=Toyota|Model=Corolla|Year=2008
YMMMake=Toyota|Model=Corolla|Year=2008
YMMMake=Toyota|Model=Corolla|Year=2008
ReviseTY10B80544557
YMMMake=Toyota|Model=Corolla|Year=2008
YMMMake=Toyota|Model=Corolla|Year=2008
YMMMake=Toyota|Model=Corolla|Year=2008
YMMMake=Toyota|Model=Corolla|Year=2008
YMMMake=Toyota|Model=Corolla|Year=2008
YMMMake=Toyota|Model=Corolla|Year=2008
YMMMake=Toyota|Model=Corolla|Year=2008
YMMMake=Toyota|Model=Corolla|Year=2008
YMMMake=Toyota|Model=Corolla|Year=2008
YMMMake=Toyota|Model=Corolla|Year=2008
YMMMake=Toyota|Model=Corolla|Year=2008
 
Upvote 0
the data row in column D is varies, sometime 20 row, sometimes 30 rows, sometimes 1000 row and sometimes 5000 row
 
Upvote 0
it varies, it's not exactly 2 row data, how would you do it to match exactly the various row data in column D and E.
 
Upvote 0
mccdgxch said:
the data row in column D is varies, sometime 20 row, sometimes 30 rows, sometimes 1000 row and sometimes 5000 row
Click to expand...
Hopefully that is all the changes now?

VBA Code: 
Sub Rearrange_v3()
  Dim vInsert As Variant, a As Variant, b As Variant
  Dim i As Long, k As Long, r As Long, c As Long, lrC As Long, lrD As Long, insrws As Long
 
  lrC = Range("C" & Rows.Count).End(xlUp).Row
  lrD = Range("D" & Rows.Count).End(xlUp).Row
  insrws = lrD - lrC
  vInsert = Range("D" & lrC + 1).Resize(insrws, 2).Value
  a = Range("A2:C" & lrC).Value
  ReDim b(1 To (insrws + 1) * UBound(a), 1 To 5)
  For i = 1 To UBound(a)
    k = (insrws + 1) * i - insrws
    b(k, 1) = a(i, 1): b(k, 2) = a(i, 2): b(k, 3) = a(i, 3)
    For r = 1 To insrws
      For c = 1 To 2
        b(k + r, 3 + c) = vInsert(r, c)
      Next c
    Next r
  Next i
  Range("A2").Resize(UBound(b), 5).Value = b
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Solved
Using IF, AND, OR formula
Replies
5
Views
114
dreid1011
dreid1011
A
  • Solved
Comparing two excel files for matching / mismatching rows (long text strings - match() wont work
Replies
4
Views
575
ahaye
A
T
  • Question
Pop Up Message if specific cells are greater than other specific cells
Replies
5
Views
433
tabs1063
T
M
  • Question
Find & Replace Text for Hidden Tabs
Replies
7
Views
93
igold
igold
B
  • Question
Sheetname 'problem' and another Index-match problem
Replies
2
Views
510
Blackov
B

Forum statistics

Threads
1,215,328
Messages
6,124,299
Members
449,149
Latest member
mwdbActuary

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