Hi experts,
The code below groups data by agent name and the total revenue generated by each agent. However I need to include a date column in this table and use this column in my group condition as well, so as result and should have the table 2:
Could someone help me to include the second condition in my inverse loop.
Sub DataGrouping()
'Declaring variables
Dim Rng As Range
Dim LngRow As Long, LngLastRow, i As Long
Application.ScreenUpdating = False
'Getting row number of last cell
LngLastRow = Cells(Rows.Count, 1).End(xlUp).Row
'Initializing the first row
i = 2
'Looping until blank cell is encountered in first column
While Not Cells(i, 1).Value = ""
'Initializing range object
Set Rng = Cells(i, 2)
'Looping from last row to specified first row
For LngRow = LngLastRow To (i + 1) Step -1
Checking whether value in the cell is equal to specified cell
If Cells(LngRow, 1).Value = Rng.Value Then
Rng.Offset(0, 2).Value = Rng.Offset(0, 2).Value + Cells(LngRow, 3).Value
Rows(LngRow).Delete
End If
Next LngRow
i = i + 1
Wend
Application.ScreenUpdating = True
End Sub
Display More
[TABLE="border: 1, cellpadding: 1, width: 500"]
Table 1
Before running the macro: [TABLE="border: 0, cellpadding: 0, cellspacing: 0, width: 319"]
Name
[/td][td]Phone
[/td][td]Sales/Call
[/td][/tr][tr][td]Guy
[/td][td]1-565-498-6512
[/td][td]$1.00
[/td][/tr][tr][td]John
[/td][td]1-565-498-6513
[/td][td]$1.00
[/td][/tr][tr][td]Mary
[/td][td]1-565-498-6514
[/td][td]$1.00
[/td][/tr][tr][td]Ted
[/td][td]1-565-498-6515
[/td][td]$1.00
[/td][/tr][tr][td]Guy
[/td][td]1-565-498-6512
[/td][td]$1.00
[/td][/tr][tr][td]John
[/td][td]1-565-498-6513
[/td][td]$1.00
[/td][/tr][tr][td]Mary
[/td][td]1-565-498-6514
[/td][td]$1.00
[/td][/tr][tr][td]Ted
[/td][td]1-565-498-6515
[/td][td]$1.00
[/td][/tr][tr][td]Guy
[/td][td]1-565-498-6512
[/td][td]$1.00
[/td][/tr][tr][td]John
[/td][td]1-565-498-6513
[/td][td]$1.00
[/td][/tr][tr][td]Mary
[/td][td]1-565-498-6514
[/td][td]$1.00
[/td][/tr][tr][td]Ted
[/td][td]1-565-498-6515
[/td][td]$1.00
[/td][/tr][tr][td]Guy
[/td][td]1-565-498-6512
[/td][td]$1.00
[/td][/tr][tr][td]John
[/td][td]1-565-498-6513
[/td][td]$1.00
[/td][/tr][tr][td]Mary
[/td][td]1-565-498-6514
[/td][td]$1.00
[/td][/tr][tr][td]Ted
[/td][td]1-565-498-6515
[/td][td]$1.00
[/td][/tr]
[/TABLE]
[TABLE="border: 1, cellpadding: 0, cellspacing: 0"]
[tr][td]Table 1
After running the macro: [TABLE="border: 0, cellpadding: 0, cellspacing: 0, width: 319"]
Name
[/td][td]Phone
[/td][td]Sales/Call
[/td][/tr][tr][td]Guy
[/td][td]1-565-498-6512
[/td][td]$4.00
[/td][/tr][tr][td]John
[/td][td]1-565-498-6513
[/td][td]$4.00
[/td][/tr][tr][td]Mary
[/td][td]1-565-498-6514
[/td][td]$4.00
[/td][/tr][tr][td]Ted
[/td][td]1-565-498-6515
[/td][td]$4.00
[/td][/tr]
[/TABLE]
[/TABLE]
Table 2
Before running the macro: [TABLE="border: 0, cellpadding: 0, cellspacing: 0, width: 397"]
Name
[/td][td]Date
[/td][td]Phone
[/td][td]Sales/Call
[/td][/tr][tr][td]Guy
[/td][td]7/05/2018
[/td][td]1-565-498-6512
[/td][td]$1.00
[/td][/tr][tr][td]John
[/td][td]7/05/2018
[/td][td]1-565-498-6513
[/td][td]$1.00
[/td][/tr][tr][td]Mary
[/td][td]7/05/2018
[/td][td]1-565-498-6514
[/td][td]$1.00
[/td][/tr][tr][td]Ted
[/td][td]7/05/2018
[/td][td]1-565-498-6515
[/td][td]$1.00
[/td][/tr][tr][td]Guy
[/td][td]7/05/2018
[/td][td]1-565-498-6512
[/td][td]$1.00
[/td][/tr][tr][td]John
[/td][td]7/05/2018
[/td][td]1-565-498-6513
[/td][td]$1.00
[/td][/tr][tr][td]Mary
[/td][td]7/05/2018
[/td][td]1-565-498-6514
[/td][td]$1.00
[/td][/tr][tr][td]Ted
[/td][td]7/05/2018
[/td][td]1-565-498-6515
[/td][td]$1.00
[/td][/tr][tr][td]Guy
[/td][td]8/05/2018
[/td][td]1-565-498-6512
[/td][td]$1.00
[/td][/tr][tr][td]John
[/td][td]8/05/2018
[/td][td]1-565-498-6513
[/td][td]$1.00
[/td][/tr][tr][td]Mary
[/td][td]8/05/2018
[/td][td]1-565-498-6514
[/td][td]$1.00
[/td][/tr][tr][td]Ted
[/td][td]8/05/2018
[/td][td]1-565-498-6515
[/td][td]$1.00
[/td][/tr][tr][td]Guy
[/td][td]9/05/2018
[/td][td]1-565-498-6512
[/td][td]$1.00
[/td][/tr][tr][td]John
[/td][td]9/05/2018
[/td][td]1-565-498-6513
[/td][td]$1.00
[/td][/tr][tr][td]Mary
[/td][td]9/05/2018
[/td][td]1-565-498-6514
[/td][td]$1.00
[/td][/tr][tr][td]Ted
[/td][td]9/05/2018
[/td][td]1-565-498-6515
[/td][td]$1.00
[/td][/tr]
[/TABLE]
Table 2
After running the macro: [TABLE="border: 0, cellpadding: 0, cellspacing: 0, width: 345"]
Name
[/td][td]Date
[/td][td]Phone
[/td][td]Sales/Call
[/td][/tr][tr][td]Guy
[/td][td]7/05/2018
[/td][td]1-565-498-6512
[/td][td]$2.00
[/td][/tr][tr][td]John
[/td][td]7/05/2018
[/td][td]1-565-498-6513
[/td][td]$2.00
[/td][/tr][tr][td]Mary
[/td][td]7/05/2018
[/td][td]1-565-498-6514
[/td][td]$2.00
[/td][/tr][tr][td]Ted
[/td][td]7/05/2018
[/td][td]1-565-498-6515
[/td][td]$2.00
[/td][/tr][tr][td]Guy
[/td][td]8/05/2018
[/td][td]1-565-498-6512
[/td][td]$1.00
[/td][/tr][tr][td]John
[/td][td]8/05/2018
[/td][td]1-565-498-6513
[/td][td]$1.00
[/td][/tr][tr][td]Mary
[/td][td]8/05/2018
[/td][td]1-565-498-6514
[/td][td]$1.00
[/td][/tr][tr][td]Ted
[/td][td]8/05/2018
[/td][td]1-565-498-6515
[/td][td]$1.00
[/td][/tr][tr][td]Guy
[/td][td]9/05/2018
[/td][td]1-565-498-6512
[/td][td]$1.00
[/td][/tr][tr][td]John
[/td][td]9/05/2018
[/td][td]1-565-498-6513
[/td][td]$1.00
[/td][/tr][tr][td]Mary
[/td][td]9/05/2018
[/td][td]1-565-498-6514
[/td][td]$1.00
[/td][/tr][tr][td]Ted
[/td][td]9/05/2018
[/td][td]1-565-498-6515
[/td][td]$1.00
[/td][/tr]
[/TABLE]
[/TABLE]
Cheers,
Gilly