VBA code with IF, cut/paste, & last column

R

RLCornish

New Member
Joined
Feb 11, 2014
Messages
42
I have a list of travel details where each row is a different leg of a travelers journey. There are common details at the beginning of the row, such as passenger name and such. In order to use the data to auto-fill a templated itinerary I need to align the legs of the journeys per passenger into a single row. Here's an example of what the data I receive looks like...

[TABLE="class: grid, width: 800"]
<colgroup><col width="145" style="width: 109pt;"><col width="80" style="width: 60pt;"><col width="115" span="5" style="width: 86pt;"></colgroup><tbody>[TR]
[TD="width: 145"]DOE/JOHN[/TD]
[TD="width: 80, align: right"]1[/TD]
[TD="width: 115, align: right"]19/01/17[/TD]
[TD="width: 115, align: right"]19/01/17[/TD]
[TD="width: 115"]Duluth, MN[/TD]
[TD="width: 115"]Minneapolis, MN[/TD]
[TD="width: 115"]7:00AM[/TD]
[/TR]
[TR]
[TD]DOE/JOHN[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]19/01/17[/TD]
[TD="align: right"]19/01/17[/TD]
[TD]Minneapolis, MN[/TD]
[TD]Kansas City, MO[/TD]
[TD]9:55AM[/TD]
[/TR]
[TR]
[TD]DOE/JOHN[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]25/01/17[/TD]
[TD="align: right"]25/01/17[/TD]
[TD]Kansas City, MO[/TD]
[TD]Minneapolis, MN[/TD]
[TD]7:49PM[/TD]
[/TR]
[TR]
[TD]DOE/JOHN[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]25/01/17[/TD]
[TD="align: right"]25/01/17[/TD]
[TD]Minneapolis, MN[/TD]
[TD]Duluth, MN[/TD]
[TD]10:00PM[/TD]
[/TR]
[TR]
[TD]PICKLE/FARMER[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]31/07/17[/TD]
[TD="align: right"]31/07/17[/TD]
[TD]Minneapolis, MN[/TD]
[TD]Kansas City, MO[/TD]
[TD]7:05AM[/TD]
[/TR]
[TR]
[TD]PICKLE/FARMER[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2/08/17[/TD]
[TD="align: right"]2/08/17[/TD]
[TD]Kansas City, MO[/TD]
[TD]Minneapolis, MN[/TD]
[TD]7:49PM[/TD]
[/TR]
[TR]
[TD]SMITH/MRS.[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]31/03/17[/TD]
[TD="align: right"]31/03/17[/TD]
[TD]Madison, WI[/TD]
[TD]Minneapolis, MN[/TD]
[TD]6:40AM[/TD]
[/TR]
[TR]
[TD]SMITH/MRS.[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]31/03/17[/TD]
[TD="align: right"]31/03/17[/TD]
[TD]Minneapolis, MN[/TD]
[TD]Kansas City, MO[/TD]
[TD]9:55AM[/TD]
[/TR]
[TR]
[TD]SMITH/MRS.[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]1/04/17[/TD]
[TD="align: right"]1/04/17[/TD]
[TD]Kansas City, MO[/TD]
[TD]Minneapolis, MN[/TD]
[TD]7:49PM[/TD]
[/TR]
[TR]
[TD]SMITH/MRS.[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]1/04/17[/TD]
[TD="align: right"]1/04/17[/TD]
[TD]Minneapolis, MN[/TD]
[TD]Madison, WI[/TD]
[TD]10:20PM[/TD]
[/TR]
</tbody>[/TABLE]

Ideally what I would have would look like this...

[TABLE="class: grid, width: 2366"]
<colgroup><col width="168" style="width:126pt"> <col width="20" style="width:15pt"> <col width="85" style="width:64pt" span="2"> <col width="149" style="width:112pt"> <col width="152" style="width:114pt"> <col width="75" style="width:56pt"> <col width="20" style="width:15pt"> <col width="85" style="width:64pt" span="2"> <col width="152" style="width:114pt" span="2"> <col width="75" style="width:56pt"> <col width="20" style="width:15pt"> <col width="74" style="width:56pt" span="2"> <col width="152" style="width:114pt"> <col width="149" style="width:112pt"> <col width="75" style="width:56pt"> <col width="20" style="width:15pt"> <col width="74" style="width:56pt" span="2"> <col width="149" style="width:112pt"> <col width="116" style="width:87pt"> <col width="86" style="width:65pt"> </colgroup><tbody>[TR]
[TD="width: 168"]DOE/JOHN[/TD]
[TD="width: 20, align: right"]1[/TD]
[TD="width: 85, align: right"]6/08/17[/TD]
[TD="width: 85, align: right"]6/08/17[/TD]
[TD="width: 149"]Duluth, MN[/TD]
[TD="width: 152"]Minneapolis, MN[/TD]
[TD="width: 75"]7:00AM[/TD]
[TD="width: 20, align: right"]2[/TD]
[TD="width: 85, align: right"]6/08/17[/TD]
[TD="width: 85, align: right"]6/08/17[/TD]
[TD="width: 152"]Minneapolis, MN[/TD]
[TD="width: 152"]Kansas City, MO[/TD]
[TD="width: 75"]9:55AM[/TD]
[TD="width: 20, align: right"]3[/TD]
[TD="width: 74, align: right"]7/08/17[/TD]
[TD="width: 74, align: right"]7/08/17[/TD]
[TD="width: 152"]Kansas City, MO[/TD]
[TD="width: 149"]Minneapolis, MN[/TD]
[TD="width: 75"]7:49PM[/TD]
[TD="width: 20, align: right"]4[/TD]
[TD="width: 74, align: right"]7/08/17[/TD]
[TD="width: 74, align: right"]7/08/17[/TD]
[TD="width: 149"]Minneapolis, MN[/TD]
[TD="width: 116"]Duluth, MN[/TD]
[TD="width: 86"]10:00PM[/TD]
[/TR]
[TR]
[TD]PICKLE/FARMER[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]31/07/17[/TD]
[TD="align: right"]31/07/17[/TD]
[TD]Minneapolis, MN[/TD]
[TD]Kansas City, MO[/TD]
[TD]7:05AM[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2/08/17[/TD]
[TD="align: right"]2/08/17[/TD]
[TD]Kansas City, MO[/TD]
[TD]Minneapolis, MN[/TD]
[TD]7:49PM[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]SMITH/MRS.[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]31/07/17[/TD]
[TD="align: right"]31/07/17[/TD]
[TD]Madison, WI[/TD]
[TD]Minneapolis, MN[/TD]
[TD]6:40AM[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]31/07/17[/TD]
[TD="align: right"]31/07/17[/TD]
[TD]Minneapolis, MN[/TD]
[TD]Kansas City, MO[/TD]
[TD]9:55AM[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]1/08/17[/TD]
[TD="align: right"]1/08/17[/TD]
[TD]Kansas City, MO[/TD]
[TD]Minneapolis, MN[/TD]
[TD]7:49PM[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]1/08/17[/TD]
[TD="align: right"]1/08/17[/TD]
[TD]Minneapolis, MN[/TD]
[TD]Madison, WI[/TD]
[TD]10:20PM[/TD]
[/TR]
</tbody>[/TABLE]

I was thinking something along the lines of IF name field matches cell above cut cells 8 thru 13 of current row, offset up 1 row, find first empty cell to the right, then paste. IF does not match cell above, drop to cell in col A and repeat. Loop thru each row until end.

If you have anything idea of how to accomplish this, I'm good with that, but this was what my mind conceived.

Any help would be appreciated!
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
Sort by date Sort by votes
RLCornish, try this code - assuming your current sheet structure is exactly the same as shown above. If you need any further help let me know

Code: 
Sub test()

Dim PassName As String, Seq As Integer, LastCol As Single, LastRow As Single, OrgRg As Range, NewRg As Range

LastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
LastCol = 7

For x = 2 To LastRow
    PassName = Cells(x, 1).Value
    Seq = ActiveSheet.Range("B" & x).Value
        If PassName = Cells(x, 1).Value And Seq > 1 Then
            Set OrgRg = Range("B" & x & ":G" & x)
            Set NewRg = Range(Cells(x - Seq + 1, (Seq * 6) - 4), Cells(x - Seq + 1, (Seq * 6) + 1))
                If ((Seq * 6) + 1) > LastCol Then LastCol = ((Seq * 6) + 1)
                NewRg.Value = OrgRg.Value
        End If
Next x

Set NewRg = ActiveSheet.Range(Cells(1, 1), Cells(LastRow, LastCol))

NewRg.Sort Key1:=Range("B2:B" & LastRow), Order1:=xlAscending, Header:=xlYes

Dim FindFirst As Single
FindFirst = Application.WorksheetFunction.Match(2, Range("B2:B" & LastRow), 0) + 1

Rows(FindFirst & ":" & LastRow + 1).EntireRow.Delete

End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question Question
Locate first instance of all dates in a column and hide all remaining rows
Replies
4
Views
126
Peter_SSs
Peter_SSs
O
  • Solved
VBA - Filter columns, cut/paste rows new sheet, pivot table
2
Replies
12
Views
315
Skyybot
Skyybot
T
  • Question Question
copy last column for each sheet and the ID for each adjacent cells
Replies
0
Views
144
tubrak
T
D
  • Question Question
Problems Encountered When Generating VBA Excel Charts with 15-Minute Interval Data.
Replies
0
Views
366
dilshod_k
D
Dazzawm
  • Solved
Macro To Find Latest Date To Corresponding Data In Column B And Add To Sheet 2
Replies
7
Views
344
Fuji
F

Forum statistics

Threads
1,224,820
Messages
6,181,161
Members
453,021
Latest member
Justyna P

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