Macro / VBA needed to concatenate data in multiple rows to multiple columns

[valcin]

The starting data set is the standard output generated from an online application that cannot be altered.
[TABLE="width: 900"]
<tbody>[TR]
[TD][TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Start Date[/TD]
[TD]Qty[/TD]
[TD]Item[/TD]
[TD]First[/TD]
[TD]Last[/TD]
[TD]Email[/TD]
[TD]Comment[/TD]
[TD]Timestamp[/TD]
[TD]Phone #[/TD]
[TD]Phone Type[/TD]
[/TR]
[TR]
[TD]2/27/2014[/TD]
[TD]100[/TD]
[TD]Italian[/TD]
[TD]Jane[/TD]
[TD]Doe[/TD]
[TD]JD@.com[/TD]
[TD]Greg[/TD]
[TD]1/17/2014[/TD]
[TD]xxx-yyy-zzz[/TD]
[TD]M[/TD]
[/TR]
[TR]
[TD]2/27/2014[/TD]
[TD]27[/TD]
[TD]Turkey[/TD]
[TD]Jane[/TD]
[TD]Doe[/TD]
[TD]JD@.com[/TD]
[TD]Greg[/TD]
[TD]1/17/2014[/TD]
[TD]xxx-yyy-zzz[/TD]
[TD]M[/TD]
[/TR]
[TR]
[TD]2/27/2014[/TD]
[TD]33[/TD]
[TD]Turkey[/TD]
[TD]Sally[/TD]
[TD]Smit[/TD]
[TD]SS@.com[/TD]
[TD]Billy[/TD]
[TD]1/18/2014[/TD]
[TD]xxx-yyy-zzzz[/TD]
[TD]H[/TD]
[/TR]
[TR]
[TD]2/27/2014[/TD]
[TD]54[/TD]
[TD]Italian[/TD]
[TD]Sally[/TD]
[TD]Smit[/TD]
[TD]SS@.com[/TD]
[TD]Billy[/TD]
[TD]1/18/2014[/TD]
[TD]xxx-yyy-zzzz[/TD]
[TD]H[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
So if the same person orders 2 items (there are only 2 items available for people to choose from), the online application creates 2 rows, each with duplicate data other than the item and the quantity.


The output data set is desired to be on a new worksheet in the same workbook. the data should be sorted by last name, then first name and look as follows:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Last[/TD]
[TD]First[/TD]
[TD]Email[/TD]
[TD]Phone[/TD]
[TD]Italian[/TD]
[TD]Turkey[/TD]
[/TR]
[TR]
[TD]Doe[/TD]
[TD]Jane[/TD]
[TD]JD@.com[/TD]
[TD]xxx-yyy-zzzz[/TD]
[TD]100[/TD]
[TD]27[/TD]
[/TR]
[TR]
[TD]Smit[/TD]
[TD]Sally[/TD]
[TD]SS@.com[/TD]
[TD]xxx-yyy-zzzz[/TD]
[TD]54[/TD]
[TD]33[/TD]
[/TR]
</tbody>[/TABLE]


I have found many useful scripts / macros on MrExcel.com, but I do not have the skill level to modify them for this requirement. Your help is greatly appreciated. The solution needs to work in Excel 2010 and newer please.

I took a sample output from the web app and pasted it into Excel and then created the desired output on a second tab and manually manipulated the data. i put the file out on Dropbox in case it is of any use to better understand what I need to accomplish.

https://www.dropbox.com/s/2ig3jdd9n7p2h9w/Example.xlsx

 

[AlphaFrog]

[color=darkblue]Sub[/color] Italian_Turkey_Combo_Hoagies()
    
    [color=darkblue]Dim[/color] vOrders [color=darkblue]As[/color] [color=darkblue]Variant[/color], vConsolidated [color=darkblue]As[/color] [color=darkblue]Variant[/color]
    [color=darkblue]Dim[/color] i [color=darkblue]As[/color] [color=darkblue]Long[/color], j [color=darkblue]As[/color] Long
    
    [color=darkblue]With[/color] Range("A1").CurrentRegion
        
        .Sort Key1:=Range("E2"), Order1:=xlAscending, Key2:=Range("D2"), _
            Order2:=xlAscending, Header:=xlYes, OrderCustom:=1, MatchCase:=False, _
            Orientation:=xlTop[color=darkblue]To[/color]Bottom, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal
        
        vOrders = .Value
        [color=darkblue]ReDim[/color] vConsolidated(1 [color=darkblue]To[/color] [color=darkblue]UBound[/color](vOrders, 1), 1 [color=darkblue]To[/color] 6)
        
    [color=darkblue]End[/color] [color=darkblue]With[/color]
    
    [color=darkblue]For[/color] i = 2 To [color=darkblue]UBound[/color](vOrders, 1)
        j = j + 1
        vConsolidated(j, 1) = vOrders(i, 5)
        vConsolidated(j, 2) = vOrders(i, 4)
        vConsolidated(j, 3) = vOrders(i, 6)
        vConsolidated(j, 4) = vOrders(i, 9)
        vConsolidated(j, 5) = 0
        vConsolidated(j, 6) = 0
        [color=darkblue]If[/color] LCase(vOrders(i, 3)) [color=darkblue]Like[/color] "*italian*" [color=darkblue]Then[/color]
            vConsolidated(j, 5) = vOrders(i, 2)
        [color=darkblue]ElseIf[/color] LCase(vOrders(i, 3)) [color=darkblue]Like[/color] "*turkey*" [color=darkblue]Then[/color]
            vConsolidated(j, 6) = vOrders(i, 2)
        [color=darkblue]End[/color] [color=darkblue]If[/color]
        [color=darkblue]If[/color] i < [color=darkblue]UBound[/color](vOrders, 1) [color=darkblue]Then[/color]
            [color=darkblue]If[/color] vConsolidated(j, 1) = vOrders(i + 1, 5) And _
               vConsolidated(j, 2) = vOrders(i + 1, 4) [color=darkblue]Then[/color]
                    i = i + 1
                    [color=darkblue]If[/color] LCase(vOrders(i, 3)) [color=darkblue]Like[/color] "*italian*" [color=darkblue]Then[/color]
                        vConsolidated(j, 5) = vOrders(i, 2)
                    [color=darkblue]ElseIf[/color] LCase(vOrders(i, 3)) [color=darkblue]Like[/color] "*turkey*" [color=darkblue]Then[/color]
                        vConsolidated(j, 6) = vOrders(i, 2)
                    [color=darkblue]End[/color] [color=darkblue]If[/color]
            [color=darkblue]End[/color] [color=darkblue]If[/color]
        [color=darkblue]End[/color] [color=darkblue]If[/color]
    [color=darkblue]Next[/color] i
    
    Application.ScreenUpdating = [color=darkblue]False[/color]
    Sheets.Add After:=Sheets(Sheets.Count)
    Range("A1:F1").Value = Array("Last Name", "First Name", "Email", "Phone", "ITALIAN", "TURKEY")
    Range("A2:F" & j + 1).Value = vConsolidated
    Columns.AutoFit
    Application.ScreenUpdating = [color=darkblue]True[/color]
    
[color=darkblue]End[/color] [color=darkblue]Sub[/color]

 

[valcin]

Thanks AlphaFrog!!!!!!