Need Seperate Lines of Data in One Row

R

retrobeast

New Member
Joined
Jan 17, 2011
Messages
6
Hi
My orginial excel 7 data looks like this.

<TABLE style="WIDTH: 967pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=1288><COLGROUP><COL style="WIDTH: 87pt; mso-width-source: userset; mso-width-alt: 4242" width=116><COL style="WIDTH: 37pt; mso-width-source: userset; mso-width-alt: 1792" width=49><COL style="WIDTH: 83pt; mso-width-source: userset; mso-width-alt: 4059" width=111><COL style="WIDTH: 78pt; mso-width-source: userset; mso-width-alt: 3803" width=104><COL style="WIDTH: 192pt; mso-width-source: userset; mso-width-alt: 9362" span=2 width=256><COL style="WIDTH: 46pt; mso-width-source: userset; mso-width-alt: 2230" width=61><COL style="WIDTH: 145pt; mso-width-source: userset; mso-width-alt: 7058" width=193><COL style="WIDTH: 72pt; mso-width-source: userset; mso-width-alt: 3510" width=96><COL style="WIDTH: 35pt; mso-width-source: userset; mso-width-alt: 1682" width=46><TBODY><TR style="HEIGHT: 16.5pt" height=22><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: #557437 1.5pt solid; BACKGROUND-COLOR: #557437; WIDTH: 87pt; HEIGHT: 16.5pt; BORDER-TOP: #557437 1.5pt solid; BORDER-RIGHT: black 1pt solid" class=xl64 height=22 width=116>Name</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #557437; WIDTH: 37pt; BORDER-TOP: #557437 1.5pt solid; BORDER-RIGHT: black 1pt solid" class=xl65 width=49>UserID</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #557437; WIDTH: 83pt; BORDER-TOP: #557437 1.5pt solid; BORDER-RIGHT: black 1pt solid" class=xl65 width=111>OrderedDate</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #557437; WIDTH: 78pt; BORDER-TOP: #557437 1.5pt solid; BORDER-RIGHT: black 1pt solid" class=xl65 width=104>RequestedDate</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #557437; WIDTH: 192pt; BORDER-TOP: #557437 1.5pt solid; BORDER-RIGHT: black 1pt solid" class=xl65 width=256>OrderNumber</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #557437; WIDTH: 192pt; BORDER-TOP: #557437 1.5pt solid; BORDER-RIGHT: black 1pt solid" class=xl65 width=256>FoodItem</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #557437; WIDTH: 46pt; BORDER-TOP: #557437 1.5pt solid; BORDER-RIGHT: black 1pt solid" class=xl65 width=61>Quantity</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #557437; WIDTH: 145pt; BORDER-TOP: #557437 1.5pt solid; BORDER-RIGHT: black 1pt solid" class=xl65 width=193>Email</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #557437; WIDTH: 72pt; BORDER-TOP: #557437 1.5pt solid; BORDER-RIGHT: black 1pt solid" class=xl65 width=96>Phone</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #557437; WIDTH: 35pt; BORDER-TOP: #557437 1.5pt solid; BORDER-RIGHT: #557437 1.5pt solid" class=xl66 width=46></TD></TR></TBODY></TABLE>
<TABLE style="WIDTH: 967pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=1288><COLGROUP><COL style="WIDTH: 87pt; mso-width-source: userset; mso-width-alt: 4242" width=116><COL style="WIDTH: 37pt; mso-width-source: userset; mso-width-alt: 1792" width=49><COL style="WIDTH: 83pt; mso-width-source: userset; mso-width-alt: 4059" width=111><COL style="WIDTH: 78pt; mso-width-source: userset; mso-width-alt: 3803" width=104><COL style="WIDTH: 192pt; mso-width-source: userset; mso-width-alt: 9362" span=2 width=256><COL style="WIDTH: 46pt; mso-width-source: userset; mso-width-alt: 2230" width=61><COL style="WIDTH: 145pt; mso-width-source: userset; mso-width-alt: 7058" width=193><COL style="WIDTH: 72pt; mso-width-source: userset; mso-width-alt: 3510" width=96><COL style="WIDTH: 35pt; mso-width-source: userset; mso-width-alt: 1682" width=46><TBODY><TR style="HEIGHT: 30.75pt" height=41><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: #557437 1.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 87pt; HEIGHT: 30.75pt; BORDER-TOP: black 1pt solid; BORDER-RIGHT: black 1pt solid" class=xl65 height=41 width=116>Ashish Patel</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; WIDTH: 37pt; BORDER-TOP: black 1pt solid; BORDER-RIGHT: black 1pt solid" class=xl66 width=49 align=right>12</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; WIDTH: 83pt; BORDER-TOP: black 1pt solid; BORDER-RIGHT: black 1pt solid" class=xl67 width=111 align=right>12/28/2010 0:55</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; WIDTH: 78pt; BORDER-TOP: black 1pt solid; BORDER-RIGHT: black 1pt solid" class=xl68 width=104 align=right>1/3/2011</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; WIDTH: 192pt; BORDER-TOP: black 1pt solid; BORDER-RIGHT: black 1pt solid" class=xl66 width=256>ece6014e-af93-4bca-8ee4-cd999a382547</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; WIDTH: 192pt; BORDER-TOP: black 1pt solid; BORDER-RIGHT: black 1pt solid" class=xl66 width=256>Egg White Empanada - 300 calories</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; WIDTH: 46pt; BORDER-TOP: black 1pt solid; BORDER-RIGHT: black 1pt solid" class=xl66 width=61 align=right>5</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; WIDTH: 145pt; BORDER-TOP: black 1pt solid; BORDER-RIGHT: black 1pt solid" class=xl66 width=193>ashish@chhatrala.com</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; WIDTH: 72pt; BORDER-TOP: black 1pt solid; BORDER-RIGHT: black 1pt solid" class=xl66 width=96>(619) 379-4378</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; WIDTH: 35pt; BORDER-TOP: black 1pt solid; BORDER-RIGHT: #557437 1.5pt solid" class=xl69 width=46>cancel</TD></TR><TR style="HEIGHT: 30.75pt" height=41><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: #557437 1.5pt solid; BACKGROUND-COLOR: beige; WIDTH: 87pt; HEIGHT: 30.75pt; BORDER-TOP: black; BORDER-RIGHT: black 1pt solid" class=xl70 height=41 width=116>Ashish Patel</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: beige; WIDTH: 37pt; BORDER-TOP: black; BORDER-RIGHT: black 1pt solid" class=xl71 width=49 align=right>12</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: beige; WIDTH: 83pt; BORDER-TOP: black; BORDER-RIGHT: black 1pt solid" class=xl72 width=111 align=right>12/28/2010 0:55</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: beige; WIDTH: 78pt; BORDER-TOP: black; BORDER-RIGHT: black 1pt solid" class=xl73 width=104 align=right>1/3/2011</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: beige; WIDTH: 192pt; BORDER-TOP: black; BORDER-RIGHT: black 1pt solid" class=xl71 width=256>ece6014e-af93-4bca-8ee4-cd999a382547</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: beige; WIDTH: 192pt; BORDER-TOP: black; BORDER-RIGHT: black 1pt solid" class=xl71 width=256>Egg White Empanada - 500 calories</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: beige; WIDTH: 46pt; BORDER-TOP: black; BORDER-RIGHT: black 1pt solid" class=xl71 width=61 align=right>2</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: beige; WIDTH: 145pt; BORDER-TOP: black; BORDER-RIGHT: black 1pt solid" class=xl71 width=193>ashish@chhatrala.com</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: beige; WIDTH: 72pt; BORDER-TOP: black; BORDER-RIGHT: black 1pt solid" class=xl71 width=96>(619) 379-4378</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: beige; WIDTH: 35pt; BORDER-TOP: black; BORDER-RIGHT: #557437 1.5pt solid" class=xl74 width=46>cancel</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: #557437 1.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 87pt; HEIGHT: 15.75pt; BORDER-TOP: black; BORDER-RIGHT: black 1pt solid" class=xl65 height=21 width=116>Casey Butler</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; WIDTH: 37pt; BORDER-TOP: black; BORDER-RIGHT: black 1pt solid" class=xl66 width=49 align=right>32</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; WIDTH: 83pt; BORDER-TOP: black; BORDER-RIGHT: black 1pt solid" class=xl67 width=111 align=right>12/30/2010 15:02</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; WIDTH: 78pt; BORDER-TOP: black; BORDER-RIGHT: black 1pt solid" class=xl68 width=104 align=right>1/3/2011</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; WIDTH: 192pt; BORDER-TOP: black; BORDER-RIGHT: black 1pt solid" class=xl66 width=256>6a7b5355-8782-4c89-b26c-30573f500779</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; WIDTH: 192pt; BORDER-TOP: black; BORDER-RIGHT: black 1pt solid" class=xl66 width=256>Chicken Mozzarella Bake - 500 calories</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; WIDTH: 46pt; BORDER-TOP: black; BORDER-RIGHT: black 1pt solid" class=xl66 width=61 align=right>1</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; WIDTH: 145pt; BORDER-TOP: black; BORDER-RIGHT: black 1pt solid" class=xl66 width=193>caseybutler@yahoo.com</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; WIDTH: 72pt; BORDER-TOP: black; BORDER-RIGHT: black 1pt solid" class=xl66 width=96>(781) 983-7073</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; WIDTH: 35pt; BORDER-TOP: black; BORDER-RIGHT: #557437 1.5pt solid" class=xl69 width=46>cancel</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: #557437 1.5pt solid; BACKGROUND-COLOR: beige; WIDTH: 87pt; HEIGHT: 15.75pt; BORDER-TOP: black; BORDER-RIGHT: black 1pt solid" class=xl70 height=21 width=116>Casey Butler</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: beige; WIDTH: 37pt; BORDER-TOP: black; BORDER-RIGHT: black 1pt solid" class=xl71 width=49 align=right>32</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: beige; WIDTH: 83pt; BORDER-TOP: black; BORDER-RIGHT: black 1pt solid" class=xl72 width=111 align=right>12/30/2010 15:02</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: beige; WIDTH: 78pt; BORDER-TOP: black; BORDER-RIGHT: black 1pt solid" class=xl73 width=104 align=right>1/3/2011</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: beige; WIDTH: 192pt; BORDER-TOP: black; BORDER-RIGHT: black 1pt solid" class=xl71 width=256>6a7b5355-8782-4c89-b26c-30573f500779</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: beige; WIDTH: 192pt; BORDER-TOP: black; BORDER-RIGHT: black 1pt solid" class=xl71 width=256>Crispy Sesame Chicken - 500 calories</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: beige; WIDTH: 46pt; BORDER-TOP: black; BORDER-RIGHT: black 1pt solid" class=xl71 width=61 align=right>1</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: beige; WIDTH: 145pt; BORDER-TOP: black; BORDER-RIGHT: black 1pt solid" class=xl71 width=193>caseybutler@yahoo.com</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: beige; WIDTH: 72pt; BORDER-TOP: black; BORDER-RIGHT: black 1pt solid" class=xl71 width=96>(781) 983-7073</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: beige; WIDTH: 35pt; BORDER-TOP: black; BORDER-RIGHT: #557437 1.5pt solid" class=xl74 width=46>cancel</TD></TR></TBODY></TABLE>

I then run this macro against it.


Option Explicit
Sub Consolidate()
Dim LR As Long, i As Long, MyVal As String
LR = Range("A" & Rows.Count).End(xlUp).Row
Application.ScreenUpdating = False
For i = LR To 2 Step -1
If Cells(i, "A") = Cells(i - 1, "A") Then
If Cells(i, "F") = "" Then
Rows(i).EntireRow.Delete (xlShiftUp)
Else
MyVal = Cells(i, "F").Value
Rows(i).EntireRow.Delete (xlShiftUp)
If Cells(i - 1, "F") = "" Then
Cells(i - 1, "F") = MyVal
Else
Cells(i - 1, "F") = Cells(i - 1, "F").Value & ", " & MyVal
End If
End If
End If
Next i
Columns("F:F").AutoFit
Application.ScreenUpdating = True
[J:J].Delete
[I:I].Delete
[H:H].Delete
[E:E].Delete
[B:B].Delete
End Sub


Which gives me the following results.


<TABLE style="WIDTH: 486pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=648><COLGROUP><COL style="WIDTH: 87pt; mso-width-source: userset; mso-width-alt: 4242" width=116><COL style="WIDTH: 83pt; mso-width-source: userset; mso-width-alt: 4059" width=111><COL style="WIDTH: 78pt; mso-width-source: userset; mso-width-alt: 3803" width=104><COL style="WIDTH: 192pt; mso-width-source: userset; mso-width-alt: 9362" width=256><COL style="WIDTH: 46pt; mso-width-source: userset; mso-width-alt: 2230" width=61><TBODY><TR style="HEIGHT: 16.5pt" height=22><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: #557437 1.5pt solid; BACKGROUND-COLOR: #557437; WIDTH: 87pt; HEIGHT: 16.5pt; BORDER-TOP: #557437 1.5pt solid; BORDER-RIGHT: black 1pt solid" class=xl68 height=22 width=116>Name</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #557437; WIDTH: 83pt; BORDER-TOP: #557437 1.5pt solid; BORDER-RIGHT: black 1pt solid" class=xl69 width=111>OrderedDate</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #557437; WIDTH: 78pt; BORDER-TOP: #557437 1.5pt solid; BORDER-RIGHT: black 1pt solid" class=xl69 width=104>RequestedDate</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #557437; WIDTH: 192pt; BORDER-TOP: #557437 1.5pt solid; BORDER-RIGHT: black 1pt solid" class=xl69 width=256>FoodItem</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #557437; WIDTH: 46pt; BORDER-TOP: #557437 1.5pt solid; BORDER-RIGHT: black 1pt solid" class=xl69 width=61>Quantity</TD></TR><TR style="HEIGHT: 195.75pt" height=261><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: #557437 1.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 87pt; HEIGHT: 195.75pt; BORDER-TOP: black; BORDER-RIGHT: black 1pt solid" class=xl70 height=261 width=116>Ashish Patel</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; WIDTH: 83pt; BORDER-TOP: black; BORDER-RIGHT: black 1pt solid" class=xl66 width=111 align=right>12/28/2010 0:55</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; WIDTH: 78pt; BORDER-TOP: black; BORDER-RIGHT: black 1pt solid" class=xl67 width=104 align=right>1/3/2011</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; WIDTH: 192pt; BORDER-TOP: black; BORDER-RIGHT: black 1pt solid" class=xl65 width=256>Tofu Mozzarella Bake - 500 calories, Tasty Beanloaf & Garlic Mash - 500 calories, Stir Fry Rice Noodles with Garlic Basil Tofu - 500 calories, Protein Pizza Vegetable Bean - 500 calories, Black Bean Burger & Sweet Potato Fries - 500 calories, Fresh Egg Tomato Basil Pizza - 500 calories, Fresh Egg White Breakfast Pizza (Italian Style) - 500 calories, Everything Vegetable Soup - 500 calories, Egg White Empanada - 300 calories, Egg White Empanada - 500 calories</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; WIDTH: 46pt; BORDER-TOP: black; BORDER-RIGHT: black 1pt solid" class=xl65 width=61 align=right>3</TD></TR><TR style="HEIGHT: 90.75pt" height=121><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: #557437 1.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 87pt; HEIGHT: 90.75pt; BORDER-TOP: black; BORDER-RIGHT: black 1pt solid" class=xl70 height=121 width=116>Casey Butler</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; WIDTH: 83pt; BORDER-TOP: black; BORDER-RIGHT: black 1pt solid" class=xl66 width=111 align=right>12/30/2010 15:02</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; WIDTH: 78pt; BORDER-TOP: black; BORDER-RIGHT: black 1pt solid" class=xl67 width=104 align=right>1/3/2011</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; WIDTH: 192pt; BORDER-TOP: black; BORDER-RIGHT: black 1pt solid" class=xl65 width=256>Chicken Mozzarella Bake - 500 calories, Crispy Sesame Chicken - 500 calories, Rice Noodles W/Basil Garlic Meatballs - 500 calories, Turkey Hummus Burger - 500 calories, Hawaiian Barbeque Burger - 500 calories</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; WIDTH: 46pt; BORDER-TOP: black; BORDER-RIGHT: black 1pt solid" class=xl65 width=61 align=right>1</TD></TR></TBODY></TABLE>


I need to edit the macro so it does the following

-each item needs to be listed on a separate line in that row.
-the quantity number needs to show up next to each separate item.
-I would like to have the data go to a new sheet instead of wiping out the original data once macro is run against it.

Thank you very much in advance.
Mark
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
Sort by date Sort by votes
For a result, do you want this...
Excel Workbook
ABCDE
1NameOrderedDateRequestedDateFoodItemQuantity
2Ashish Patel12/28/2010 0:551/3/2011Egg White Empanada - 300 calories5
3Egg White Empanada - 500 calories2
4Casey Butler12/30/2010 15:021/3/2011Chicken Mozzarella Bake - 500 calories1
5Crispy Sesame Chicken - 500 calories1
Result 1



Or this...
Excel Workbook
ABCDEFG
1NameOrderedDateRequestedDateFoodItem1Quantity1FoodItem2Quantity2
2Ashish Patel12/28/2010 0:551/3/2011Egg White Empanada - 300 calories5Egg White Empanada - 500 calories2
3Casey Butler12/30/2010 15:021/3/2011Chicken Mozzarella Bake - 500 calories1Crispy Sesame Chicken - 500 calories1
Result 2
 
Upvote 0
Hi
thanks for the quick response.

I am looking for result 1.
After looking at result 1 it is perfect but just noticed that I do not need the time on the Order Date. Guess I can kill that before running macro?
thanks again
mark

p.s. the data will be used to create labels in mail merge. there is a varied number of people ordering usually then the food is prepared and the labels stuck on the box.
 
Last edited:
Upvote 0
Code: 
Sub Consolidate2()

    Dim LR As Long, i As Long, MyVal As String
    
    ActiveSheet.Copy After:=Sheets(Sheets.Count)
    
    LR = Range("A" & Rows.Count).End(xlUp).Row
    
    Application.ScreenUpdating = False
    
    Columns("F").AutoFit
    Range("B:B, E:E, H:J").Delete
    
    Range("B:B").NumberFormat = "mm/dd/yyyy"
    
    For i = LR To 2 Step -1
    
        If Cells(i, "A") = Cells(i - 1, "A") Then
            If Cells(i, "D") = "" Then
                Rows(i).EntireRow.Delete (xlShiftUp)
            Else
                Range("A" & i).Resize(, 3).ClearContents
            End If
        End If
        
    Next i
    
    Application.ScreenUpdating = True
    
End Sub
 
Upvote 0
Almost working !
If you look at my original results the name along with dates and items are all part of one line.
This is important because I need one row so I can make one label with all the info.
The results from the excellent macro you sent me produced separate rows for everything.

<TABLE style="WIDTH: 486pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=648><COLGROUP><COL style="WIDTH: 87pt; mso-width-source: userset; mso-width-alt: 4242" width=116><COL style="WIDTH: 83pt; mso-width-source: userset; mso-width-alt: 4059" width=111><COL style="WIDTH: 78pt; mso-width-source: userset; mso-width-alt: 3803" width=104><COL style="WIDTH: 192pt; mso-width-source: userset; mso-width-alt: 9362" width=256><COL style="WIDTH: 46pt; mso-width-source: userset; mso-width-alt: 2230" width=61><TBODY><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: #557437 1.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 87pt; HEIGHT: 15.75pt; BORDER-TOP: black 1pt solid; BORDER-RIGHT: black 1pt solid" class=xl69 height=21 width=116>Ashish Patel</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; WIDTH: 83pt; BORDER-TOP: black 1pt solid; BORDER-RIGHT: black 1pt solid" class=xl71 width=111 align=right>12/28/2010</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; WIDTH: 78pt; BORDER-TOP: black 1pt solid; BORDER-RIGHT: black 1pt solid" class=xl65 width=104 align=right>1/3/2011</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; WIDTH: 192pt; BORDER-TOP: black 1pt solid; BORDER-RIGHT: black 1pt solid" class=xl64 width=256>Tofu Mozzarella Bake - 500 calories</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; WIDTH: 46pt; BORDER-TOP: black 1pt solid; BORDER-RIGHT: black 1pt solid" class=xl64 width=61 align=right>3</TD></TR><TR style="HEIGHT: 30.75pt" height=41><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: #557437 1.5pt solid; BACKGROUND-COLOR: beige; WIDTH: 87pt; HEIGHT: 30.75pt; BORDER-TOP: black; BORDER-RIGHT: black 1pt solid" class=xl70 height=41 width=116></TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: beige; WIDTH: 83pt; BORDER-TOP: black; BORDER-RIGHT: black 1pt solid" class=xl72 width=111></TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: beige; WIDTH: 78pt; BORDER-TOP: black; BORDER-RIGHT: black 1pt solid" class=xl68 width=104></TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: beige; WIDTH: 192pt; BORDER-TOP: black; BORDER-RIGHT: black 1pt solid" class=xl67 width=256>Tasty Beanloaf & Garlic Mash - 500 calories</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: beige; WIDTH: 46pt; BORDER-TOP: black; BORDER-RIGHT: black 1pt solid" class=xl67 width=61 align=right>3</TD></TR><TR style="HEIGHT: 30.75pt" height=41><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: #557437 1.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 87pt; HEIGHT: 30.75pt; BORDER-TOP: black; BORDER-RIGHT: black 1pt solid" class=xl69 height=41 width=116></TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; WIDTH: 83pt; BORDER-TOP: black; BORDER-RIGHT: black 1pt solid" class=xl71 width=111></TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; WIDTH: 78pt; BORDER-TOP: black; BORDER-RIGHT: black 1pt solid" class=xl65 width=104></TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; WIDTH: 192pt; BORDER-TOP: black; BORDER-RIGHT: black 1pt solid" class=xl64 width=256>Stir Fry Rice Noodles with Garlic Basil Tofu - 500 calories</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; WIDTH: 46pt; BORDER-TOP: black; BORDER-RIGHT: black 1pt solid" class=xl64 width=61 align=right>2</TD></TR><TR style="HEIGHT: 30.75pt" height=41><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: #557437 1.5pt solid; BACKGROUND-COLOR: beige; WIDTH: 87pt; HEIGHT: 30.75pt; BORDER-TOP: black; BORDER-RIGHT: black 1pt solid" class=xl70 height=41 width=116></TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: beige; WIDTH: 83pt; BORDER-TOP: black; BORDER-RIGHT: black 1pt solid" class=xl72 width=111></TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: beige; WIDTH: 78pt; BORDER-TOP: black; BORDER-RIGHT: black 1pt solid" class=xl68 width=104></TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: beige; WIDTH: 192pt; BORDER-TOP: black; BORDER-RIGHT: black 1pt solid" class=xl67 width=256>Protein Pizza Vegetable Bean - 500 calories</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: beige; WIDTH: 46pt; BORDER-TOP: black; BORDER-RIGHT: black 1pt solid" class=xl67 width=61 align=right>3</TD></TR><TR style="HEIGHT: 30.75pt" height=41><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: #557437 1.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 87pt; HEIGHT: 30.75pt; BORDER-TOP: black; BORDER-RIGHT: black 1pt solid" class=xl69 height=41 width=116></TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; WIDTH: 83pt; BORDER-TOP: black; BORDER-RIGHT: black 1pt solid" class=xl71 width=111></TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; WIDTH: 78pt; BORDER-TOP: black; BORDER-RIGHT: black 1pt solid" class=xl65 width=104></TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; WIDTH: 192pt; BORDER-TOP: black; BORDER-RIGHT: black 1pt solid" class=xl64 width=256>Black Bean Burger & Sweet Potato Fries - 500 calories</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; WIDTH: 46pt; BORDER-TOP: black; BORDER-RIGHT: black 1pt solid" class=xl64 width=61 align=right>2</TD></TR><TR style="HEIGHT: 30.75pt" height=41><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: #557437 1.5pt solid; BACKGROUND-COLOR: beige; WIDTH: 87pt; HEIGHT: 30.75pt; BORDER-TOP: black; BORDER-RIGHT: black 1pt solid" class=xl70 height=41 width=116></TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: beige; WIDTH: 83pt; BORDER-TOP: black; BORDER-RIGHT: black 1pt solid" class=xl72 width=111></TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: beige; WIDTH: 78pt; BORDER-TOP: black; BORDER-RIGHT: black 1pt solid" class=xl68 width=104></TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: beige; WIDTH: 192pt; BORDER-TOP: black; BORDER-RIGHT: black 1pt solid" class=xl67 width=256>Fresh Egg Tomato Basil Pizza - 500 calories</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: beige; WIDTH: 46pt; BORDER-TOP: black; BORDER-RIGHT: black 1pt solid" class=xl67 width=61 align=right>1</TD></TR><TR style="HEIGHT: 30.75pt" height=41><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: #557437 1.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 87pt; HEIGHT: 30.75pt; BORDER-TOP: black; BORDER-RIGHT: black 1pt solid" class=xl69 height=41 width=116></TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; WIDTH: 83pt; BORDER-TOP: black; BORDER-RIGHT: black 1pt solid" class=xl71 width=111></TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; WIDTH: 78pt; BORDER-TOP: black; BORDER-RIGHT: black 1pt solid" class=xl65 width=104></TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; WIDTH: 192pt; BORDER-TOP: black; BORDER-RIGHT: black 1pt solid" class=xl64 width=256>Fresh Egg White Breakfast Pizza (Italian Style) - 500 calories</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; WIDTH: 46pt; BORDER-TOP: black; BORDER-RIGHT: black 1pt solid" class=xl64 width=61 align=right>3</TD></TR><TR style="HEIGHT: 30.75pt" height=41><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: #557437 1.5pt solid; BACKGROUND-COLOR: beige; WIDTH: 87pt; HEIGHT: 30.75pt; BORDER-TOP: black; BORDER-RIGHT: black 1pt solid" class=xl70 height=41 width=116></TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: beige; WIDTH: 83pt; BORDER-TOP: black; BORDER-RIGHT: black 1pt solid" class=xl72 width=111></TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: beige; WIDTH: 78pt; BORDER-TOP: black; BORDER-RIGHT: black 1pt solid" class=xl68 width=104></TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: beige; WIDTH: 192pt; BORDER-TOP: black; BORDER-RIGHT: black 1pt solid" class=xl67 width=256>Everything Vegetable Soup - 500 calories</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: beige; WIDTH: 46pt; BORDER-TOP: black; BORDER-RIGHT: black 1pt solid" class=xl67 width=61 align=right>2</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: #557437 1.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 87pt; HEIGHT: 15.75pt; BORDER-TOP: black; BORDER-RIGHT: black 1pt solid" class=xl69 height=21 width=116></TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; WIDTH: 83pt; BORDER-TOP: black; BORDER-RIGHT: black 1pt solid" class=xl71 width=111></TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; WIDTH: 78pt; BORDER-TOP: black; BORDER-RIGHT: black 1pt solid" class=xl65 width=104></TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; WIDTH: 192pt; BORDER-TOP: black; BORDER-RIGHT: black 1pt solid" class=xl64 width=256>Egg White Empanada - 300 calories</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; WIDTH: 46pt; BORDER-TOP: black; BORDER-RIGHT: black 1pt solid" class=xl64 width=61 align=right>5</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: #557437 1.5pt solid; BACKGROUND-COLOR: beige; WIDTH: 87pt; HEIGHT: 15.75pt; BORDER-TOP: black; BORDER-RIGHT: black 1pt solid" class=xl70 height=21 width=116></TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: beige; WIDTH: 83pt; BORDER-TOP: black; BORDER-RIGHT: black 1pt solid" class=xl72 width=111></TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: beige; WIDTH: 78pt; BORDER-TOP: black; BORDER-RIGHT: black 1pt solid" class=xl68 width=104></TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: beige; WIDTH: 192pt; BORDER-TOP: black; BORDER-RIGHT: black 1pt solid" class=xl67 width=256>Egg White Empanada - 500 calories</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: beige; WIDTH: 46pt; BORDER-TOP: black; BORDER-RIGHT: black 1pt solid" class=xl67 width=61 align=right>2</TD></TR></TBODY></TABLE>


Sooooooo close !
Easy fix?
Maybe it is more of a Result 2 looking answer I am looking for but with the food list looking just like your results.
Thanks big time !
Mark
 
Last edited:
Upvote 0
Excel Workbook
ABCD
1NameOrderedDateRequestedDateFoodItem
2Ashish Patel12/28/20101/3/2011Egg White Empanada - 300 calories - Qty:5, Egg White Empanada - 500 calories - Qty:2
3Casey Butler12/30/20101/3/2011Chicken Mozzarella Bake - 500 calories - Qty:1, Crispy Sesame Chicken - 500 calories - Qty:1
...



Code: 
Sub Consolidate2()

    Dim LR As Long, i As Long, MyVal As String
    
    Application.ScreenUpdating = False
    
    ActiveSheet.Copy After:=Sheets(Sheets.Count)
    
    LR = Range("A" & Rows.Count).End(xlUp).Row
    
    For i = LR To 2 Step -1
    
        If Not IsEmpty(Cells(i, "F")) Then MyVal = Cells(i, "F").Value & " - Qty:" & Cells(i, "G")
    
        If Cells(i, "A") = Cells(i - 1, "A") Then
            Cells(i - 1, "G") = Cells(i - 1, "G") & "," & vbLf & MyVal
            Rows(i).EntireRow.Delete (xlShiftUp)
        Else
            Cells(i, "F") = MyVal
        End If
        
    Next i
    
    Columns("F").ColumnWidth = 100
    Columns("F").AutoFit
    Range("B:B, E:E, G:J").Delete
    
    Range("B:B").NumberFormat = "mm/dd/yyyy"
    
    Application.ScreenUpdating = True
    
End Sub
 
Upvote 0
AlphaFrog !!!!!!
U R the Master !
Perfect Results have been had.
I will do the mail merge test with the data in the morning. My eyeballs are shot for the night.
Speaking of shots, I owe you a shot and a couple beers for all the help !
Thank you big time !
Mark
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
623
smozgur
smozgur
G
  • Question Question
Using Excel formula to create HTML code text referencing specific cells
Replies
0
Views
1K
gvsumich
G
A
  • Question Question
VBA - Automatically Break the Line in HTML format.
Replies
7
Views
2K
Attrazion
A
C
  • Solved
Excel 2016 32bit VBA Pulling info from Website to Sheet
Replies
2
Views
2K
CrashOD
C
E
  • Question Question
VBA for Web Scraping help
Replies
0
Views
921
epactheactor
E

Forum statistics

Threads
1,224,589
Messages
6,179,744
Members
452,940
Latest member
rootytrip

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