Creating macros using VBA code

N

NGNG

New Member
Joined
Aug 9, 2011
Messages
18
Hi,

I need some help with creating a macro using VBA code. Basically I have a spreadsheet that has transactional data and I would like to aggregate it by date and have one row per each date. What I have is below:

Sales Rep ID, Date Entered, Occurence, Total Revenue
32113, 7/1/2011, 1, $1.00
32113, 7/1/2011, 1, $20.00
32113, 7/1/2011, 2, $5.00
32113, 7/1/2011, 2, $0.00
32113, 6/28/2011, 2, $8.00
32113, 6/28/2011, 2, $0.00

AND the result I need to get to with the VBA code is below:

Sales Rep ID, Date Entered, Occurence , Total Revenue
32113, 7/1/2011, 6, $26.00
32113, 6/28/2011, 4, $8.00



Thank You
NG
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
Sort by date Sort by votes
NGNG,

Welcome to the MrExcel forum.


Sample raw data before the macro:


Excel Workbook
ABCDEFGHIJ
1Sales Rep IDDate EnteredOccurenceTotal Revenue
2321137/1/20111$1.00
3321137/1/20111$20.00
4321137/1/20112$5.00
5321137/1/20112$0.00
6321136/28/20112$8.00
7321136/28/20112$0.00
8
Sheet1





After the macro:


Excel Workbook
ABCDEFGHIJ
1Sales Rep IDDate EnteredOccurenceTotal RevenueSales Rep IDDate EnteredOccurenceTotal Revenue
2321137/1/20111$1.00321137/1/20116$26.00
3321137/1/20111$20.00321136/28/20114$8.00
4321137/1/20112$5.00
5321137/1/20112$0.00
6321136/28/20112$8.00
7321136/28/20112$0.00
8
Sheet1





Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.


Code: 
Option Explicit
Sub ReorgData()
' hiker95, 08/09/2011
' http://www.mrexcel.com/forum/showthread.php?t=570494
Dim LR As Long, LR2 As Long
Application.ScreenUpdating = False
Columns("G:J").ClearContents
Columns("A:B").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Columns("G:H"), Unique:=True
Range("I1:J1").Value = Range("C1:D1").Value
LR = Cells(Rows.Count, 1).End(xlUp).Row
LR2 = Cells(Rows.Count, 7).End(xlUp).Row
With Range("I2:I" & LR2)
  .FormulaR1C1 = "=SUMPRODUCT(--(R2C1:R" & LR & "C1=RC7),--(R2C2:R" & LR & "C2=RC8),--(R2C3:R" & LR & "C3))"
  .Value = .Value
End With
With Range("J2:J" & LR2)
  .FormulaR1C1 = "=SUMPRODUCT(--(R2C1:R" & LR & "C1=RC7),--(R2C2:R" & LR & "C2=RC8),--(R2C4:R" & LR & "C4))"
  .NumberFormat = "$#,##0.00"
  .Value = .Value
End With
Columns("G:J").AutoFit
Application.ScreenUpdating = True
End Sub


Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm


Then run the ReorgData macro.
 
Upvote 0
Thanks hiker95 for a fast and great response. I will be working on this tonight and will let you know how it goes.:)
 
Upvote 0
Thanks. That worked perfectly. Have couple more questions?:confused: Some new requirements, see sample raw data and results needed below. Requirements are: <TABLE style="WIDTH: 330pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=440><COLGROUP><COL style="WIDTH: 330pt; mso-width-source: userset; mso-width-alt: 16091" width=440><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 330pt; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl66 height=20 width=440>1. Only look at WorkOrderType = IN or UP
</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 330pt; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl66 height=20 width=440>2. Only look at Total Revenue > 0</TD></TR><TR style="HEIGHT: 30pt" height=40><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 330pt; HEIGHT: 30pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl65 height=40 width=440>3. No. of Errors = Number of accounts per employee per day with positive amount in Total Revenue Column
4. Default for No. of Errors column = 0
5. Need a record for each Sales Rep ID per day
</TD></TR></TBODY></TABLE>

Sample raw data:

<TABLE style="WIDTH: 336pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=446><COLGROUP><COL style="WIDTH: 62pt; mso-width-source: userset; mso-width-alt: 2998" width=82><COL style="WIDTH: 75pt; mso-width-source: userset; mso-width-alt: 3657" width=100><COL style="WIDTH: 77pt; mso-width-source: userset; mso-width-alt: 3730" width=102><COL style="WIDTH: 58pt; mso-width-source: userset; mso-width-alt: 2816" width=77><COL style="WIDTH: 64pt; mso-width-source: userset; mso-width-alt: 3108" width=85><TBODY><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black 1pt solid; BACKGROUND-COLOR: #cfe0f1; WIDTH: 62pt; HEIGHT: 12pt; BORDER-TOP: #dddddd 0.5pt solid; BORDER-RIGHT: black 1pt solid" dir=ltr class=xl70 height=16 width=82>Sales Rep ID</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #cfe0f1; WIDTH: 75pt; BORDER-TOP: #dddddd 0.5pt solid; BORDER-RIGHT: black 1pt solid" dir=ltr class=xl70 width=100>Account Number</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #cfe0f1; WIDTH: 77pt; BORDER-TOP: #dddddd 0.5pt solid; BORDER-RIGHT: black 1pt solid" dir=ltr class=xl70 width=102>Work Order Type</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #cfe0f1; WIDTH: 58pt; BORDER-TOP: #dddddd 0.5pt solid; BORDER-RIGHT: black 1pt solid" dir=ltr class=xl70 width=77>Date Entered</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #cfe0f1; WIDTH: 64pt; BORDER-TOP: #dddddd 0.5pt solid; BORDER-RIGHT: black 1pt solid" dir=ltr class=xl70 width=85>Total Revenue</TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: #cccccc 1pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #f7f7f7; WIDTH: 62pt; HEIGHT: 12pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #cccccc 1pt solid" dir=ltr class=xl66 height=16 width=82>32113</TD><TD style="BORDER-BOTTOM: #cccccc 1pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #f7f7f7; WIDTH: 75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #cccccc 1pt solid" dir=ltr class=xl66 width=100>572367701</TD><TD style="BORDER-BOTTOM: #cccccc 1pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #f7f7f7; WIDTH: 77pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #cccccc 1pt solid" dir=ltr class=xl67 width=102>IN</TD><TD style="BORDER-BOTTOM: #cccccc 1pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #f7f7f7; WIDTH: 58pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #cccccc 1pt solid" dir=ltr class=xl68 width=77>6/28/2011</TD><TD style="BORDER-BOTTOM: #cccccc 1pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #f7f7f7; WIDTH: 64pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #cccccc 1pt solid" dir=ltr class=xl69 width=85>$0.00 </TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: #cccccc 1pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 62pt; HEIGHT: 12pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #cccccc 1pt solid" dir=ltr class=xl71 height=16 width=82>32113</TD><TD style="BORDER-BOTTOM: #cccccc 1pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #cccccc 1pt solid" dir=ltr class=xl71 width=100>572367701</TD><TD style="BORDER-BOTTOM: #cccccc 1pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 77pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #cccccc 1pt solid" dir=ltr class=xl72 width=102>DW</TD><TD style="BORDER-BOTTOM: #cccccc 1pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 58pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #cccccc 1pt solid" dir=ltr class=xl73 width=77>6/28/2011</TD><TD style="BORDER-BOTTOM: #cccccc 1pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 64pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #cccccc 1pt solid" dir=ltr class=xl74 width=85>$3.00 </TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: #cccccc 1pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 62pt; HEIGHT: 12pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #cccccc 1pt solid" dir=ltr class=xl71 height=16 width=82>32113</TD><TD style="BORDER-BOTTOM: #cccccc 1pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #cccccc 1pt solid" dir=ltr class=xl71 width=100>572367701</TD><TD style="BORDER-BOTTOM: #cccccc 1pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 77pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #cccccc 1pt solid" dir=ltr class=xl72 width=102>UP</TD><TD style="BORDER-BOTTOM: #cccccc 1pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 58pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #cccccc 1pt solid" dir=ltr class=xl73 width=77>6/28/2011</TD><TD style="BORDER-BOTTOM: #cccccc 1pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 64pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #cccccc 1pt solid" dir=ltr class=xl74 width=85>$2.00 </TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: #cccccc 1pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 62pt; HEIGHT: 12pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #cccccc 1pt solid" dir=ltr class=xl71 height=16 width=82>32113</TD><TD style="BORDER-BOTTOM: #cccccc 1pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #cccccc 1pt solid" dir=ltr class=xl71 width=100>572367701</TD><TD style="BORDER-BOTTOM: #cccccc 1pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 77pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #cccccc 1pt solid" dir=ltr class=xl72 width=102>UP</TD><TD style="BORDER-BOTTOM: #cccccc 1pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 58pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #cccccc 1pt solid" dir=ltr class=xl73 width=77>6/29/2011</TD><TD style="BORDER-BOTTOM: #cccccc 1pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 64pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #cccccc 1pt solid" dir=ltr class=xl74 width=85>$8.00 </TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: #cccccc 1pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 62pt; HEIGHT: 12pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #cccccc 1pt solid" dir=ltr class=xl71 height=16 width=82>32113</TD><TD style="BORDER-BOTTOM: #cccccc 1pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #cccccc 1pt solid" dir=ltr class=xl71 width=100>866934801</TD><TD style="BORDER-BOTTOM: #cccccc 1pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 77pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #cccccc 1pt solid" dir=ltr class=xl72 width=102>DW</TD><TD style="BORDER-BOTTOM: #cccccc 1pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 58pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #cccccc 1pt solid" dir=ltr class=xl73 width=77>6/28/2011</TD><TD style="BORDER-BOTTOM: #cccccc 1pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 64pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #cccccc 1pt solid" dir=ltr class=xl74 width=85>$4.00 </TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: #cccccc 1pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 62pt; HEIGHT: 12pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #cccccc 1pt solid" dir=ltr class=xl71 height=16 width=82>32113</TD><TD style="BORDER-BOTTOM: #cccccc 1pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #cccccc 1pt solid" dir=ltr class=xl71 width=100>866934801</TD><TD style="BORDER-BOTTOM: #cccccc 1pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 77pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #cccccc 1pt solid" dir=ltr class=xl72 width=102>IN</TD><TD style="BORDER-BOTTOM: #cccccc 1pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 58pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #cccccc 1pt solid" dir=ltr class=xl73 width=77>6/28/2011</TD><TD style="BORDER-BOTTOM: #cccccc 1pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 64pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #cccccc 1pt solid" dir=ltr class=xl74 width=85>$4.00 </TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: #cccccc 1pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 62pt; HEIGHT: 12pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #cccccc 1pt solid" dir=ltr class=xl71 height=16 width=82>32113</TD><TD style="BORDER-BOTTOM: #cccccc 1pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #cccccc 1pt solid" dir=ltr class=xl71 width=100>355005901</TD><TD style="BORDER-BOTTOM: #cccccc 1pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 77pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #cccccc 1pt solid" dir=ltr class=xl72 width=102>IN</TD><TD style="BORDER-BOTTOM: #cccccc 1pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 58pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #cccccc 1pt solid" dir=ltr class=xl73 width=77>7/1/2011</TD><TD style="BORDER-BOTTOM: #cccccc 1pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 64pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #cccccc 1pt solid" dir=ltr class=xl74 width=85>$0.00 </TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: #cccccc 1pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 62pt; HEIGHT: 12pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #cccccc 1pt solid" dir=ltr class=xl71 height=16 width=82>82663</TD><TD style="BORDER-BOTTOM: #cccccc 1pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #cccccc 1pt solid" dir=ltr class=xl71 width=100>922005904</TD><TD style="BORDER-BOTTOM: #cccccc 1pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 77pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #cccccc 1pt solid" dir=ltr class=xl72 width=102>UP</TD><TD style="BORDER-BOTTOM: #cccccc 1pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 58pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #cccccc 1pt solid" dir=ltr class=xl73 width=77>6/21/2011</TD><TD style="BORDER-BOTTOM: #cccccc 1pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 64pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #cccccc 1pt solid" dir=ltr class=xl74 width=85>$4.00 </TD></TR></TBODY></TABLE>

Need to get to the following result:

<TABLE style="WIDTH: 214pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=284><COLGROUP><COL style="WIDTH: 62pt; mso-width-source: userset; mso-width-alt: 2998" width=82><COL style="WIDTH: 75pt; mso-width-source: userset; mso-width-alt: 3657" width=100><COL style="WIDTH: 77pt; mso-width-source: userset; mso-width-alt: 3730" width=102><TBODY><TR style="HEIGHT: 23.25pt" height=31><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black 1pt solid; BACKGROUND-COLOR: #cfe0f1; WIDTH: 62pt; HEIGHT: 23.25pt; BORDER-TOP: #dddddd 0.5pt solid; BORDER-RIGHT: black 1pt solid" dir=ltr class=xl68 height=31 width=82>Sales Rep ID</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #cfe0f1; WIDTH: 75pt; BORDER-TOP: #dddddd 0.5pt solid; BORDER-RIGHT: black 1pt solid" dir=ltr class=xl68 width=100>Date</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #cfe0f1; WIDTH: 77pt; BORDER-TOP: #dddddd 0.5pt solid; BORDER-RIGHT: black 1pt solid" dir=ltr class=xl68 width=102>Number of Errors</TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: #cccccc 1pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #f7f7f7; WIDTH: 62pt; HEIGHT: 12pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #cccccc 1pt solid" dir=ltr class=xl66 height=16 width=82>32113</TD><TD style="BORDER-BOTTOM: #cccccc 1pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #f7f7f7; WIDTH: 75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #cccccc 1pt solid" dir=ltr class=xl71 width=100>6/28/2011</TD><TD style="BORDER-BOTTOM: #cccccc 1pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #f7f7f7; WIDTH: 77pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #cccccc 1pt solid" dir=ltr class=xl67 width=102>2</TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: #cccccc 1pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 62pt; HEIGHT: 12pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #cccccc 1pt solid" dir=ltr class=xl69 height=16 width=82>32113</TD><TD style="BORDER-BOTTOM: #cccccc 1pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #cccccc 1pt solid" dir=ltr class=xl72 width=100>6/29/2011</TD><TD style="BORDER-BOTTOM: #cccccc 1pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 77pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #cccccc 1pt solid" dir=ltr class=xl70 width=102>1</TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: #cccccc 1pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 62pt; HEIGHT: 12pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #cccccc 1pt solid" dir=ltr class=xl69 height=16 width=82>32113</TD><TD style="BORDER-BOTTOM: #cccccc 1pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #cccccc 1pt solid" dir=ltr class=xl72 width=100>7/1/2011</TD><TD style="BORDER-BOTTOM: #cccccc 1pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 77pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #cccccc 1pt solid" dir=ltr class=xl70 width=102>0</TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: #cccccc 1pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 62pt; HEIGHT: 12pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #cccccc 1pt solid" dir=ltr class=xl69 height=16 width=82>82663</TD><TD style="BORDER-BOTTOM: #cccccc 1pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #cccccc 1pt solid" dir=ltr class=xl72 width=100>6/21/2011</TD><TD style="BORDER-BOTTOM: #cccccc 1pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 77pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #cccccc 1pt solid" dir=ltr class=xl70 width=102>1</TD></TR></TBODY></TABLE>
 
Upvote 0
NGNG,


Sample data before the macro:


Excel Workbook
ABCDEFGHIJ
1Sales Rep IDAccount NumberWork Order TypeDate EnteredTotal Revenue
232113572367701IN6/28/2011$0.00
332113572367701DW6/28/2011$3.00
432113572367701UP6/28/2011$2.00
532113572367701UP6/29/2011$8.00
632113866934801DW6/28/2011$4.00
732113866934801IN6/28/2011$4.00
832113355005901IN7/1/2011$0.00
982663922005904UP6/21/2011$4.00
10
Sheet1





After the macro:


Excel Workbook
ABCDEFGHIJ
1Sales Rep IDAccount NumberWork Order TypeDate EnteredTotal RevenueSales Rep IDDateNumber of Errors
232113572367701IN6/28/2011$0.00321136/28/20112
332113572367701DW6/28/2011$3.00321136/29/20111
432113572367701UP6/28/2011$2.00321137/1/20110
532113572367701UP6/29/2011$8.00826636/21/20111
632113866934801DW6/28/2011$4.00
732113866934801IN6/28/2011$4.00
832113355005901IN7/1/2011$0.00
982663922005904UP6/21/2011$4.00
10
Sheet1





Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.


Code: 
Option Explicit
Sub ReorgDataV2()
' hiker95, 08/10/2011
' http://www.mrexcel.com/forum/showthread.php?t=570494
Dim LR As Long, LR2 As Long
Application.ScreenUpdating = False
Columns("H:J").ClearContents
LR = Cells(Rows.Count, 1).End(xlUp).Row
Range("A1:A" & LR).Copy Range("H1")
Range("D1:D" & LR).Copy Range("I1")
Columns("H:I").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Columns("J:K"), Unique:=True
Columns("H:I").Delete
With Range("I1:J1")
  .Value = [{"Date","Number of Errors"}]
  .Interior.ColorIndex = 24
  .Font.Bold = True
End With
LR2 = Cells(Rows.Count, 8).End(xlUp).Row
With Range("J2:J" & LR2)
  .FormulaR1C1 = "=SUMPRODUCT((R2C1:R" & LR & "C1=RC8)*(R2C4:R" & LR & "C4=RC9)*(R2C3:R9C3={""IN"",""UP""})*(R2C5:R" & LR & "C5>0))"
  .Value = .Value
End With
Range("H1:J" & LR2).Columns.AutoFit
Application.ScreenUpdating = True
End Sub


Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm


Then run the ReorgDataV2 macro.
 
Upvote 0
Thanks hiker95, this is perfect. One quick question - what if i want the result set in the new sheet instead of the same sheet on the right, how do I modify the code to show the results in a separate sheet?
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Solved
VBA Code To Loop Through One Worksheet and Paste Certain Values Into Another
Replies
5
Views
172
Peter_SSs
Peter_SSs
J
  • Solved
Using LEFT Function On a Range Instead of A Single Cell Value
Replies
4
Views
124
Fluff
Fluff
K
  • Question Question
Copy data from rows with the dates (Monday to Sunday)
Replies
1
Views
69
K_Stevs1
K
C
  • Question Question
Searching using TextBox and ListBox
Replies
1
Views
267
campbell1093
C
F
  • Question Question
VBA Macro Issue: Replacing Simple Formula with Complex Formula Causes Error
Replies
2
Views
107
flashgordie
F

Forum statistics

Threads
1,224,609
Messages
6,179,876
Members
452,949
Latest member
Dupuhini

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