Need Excel Macro Help with Data Manipulation

January 17, 2011 at 15:46:01
Specs: Windows XP
Hi

My orginial excel 7 data looks like this.


Name UserID OrderedDate RequestedDate OrderNumber FoodItem Quantity Email Phone


Data deleted by Moderator due to possible plain text email addresses and phone numbers.

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.

Name OrderedDate RequestedDate FoodItem Quantity

Data deleted by Moderator due to possible plain text email addresses and phone numbers.


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. (I know you did this in the macro you sent me but I could not get it to work with this macro).

Thank you very much in advance.

Mark


edited by moderator: email & phone number data deleted


See More: Need Excel Macro Help with Data Manipulation

Report •


#1
January 17, 2011 at 17:54:17
I have deleted the data that you posted because it appeared to contain email addresses and phone numbers. Since I can't tell if these were fake or not, I had to use caution and delete them.

Please try to post generic data that shows your layout without being a possible security risk.

In addition, please refer to the How To in my signature line before you post anymore data or code. The How To will explain the proper way to post data and code to make it easier to read in this forum.

DerbyDad03
Office Forum Moderator

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
January 17, 2011 at 18:45:05
sorry. yes they were fake ones.

Report •
Related Solutions


Ask Question