Delete Duplicate Rows, by Date field
 

Hello,

I need some help.
I have a huge spreadsheet containing several columns.
Column A = Names (maybe 2000 unique names out of 6000 rows)
Column B = Meeting or Phone Call
Column C = Date (column B was completed)

So, I have multiple entries for each name. I need to keep only the row with
the latest "Meeting" date and the latest "Phone Call" date. How would I do
this?

Example:
L1 WorkerA Meeting 01/04/2006
L2 WorkerA Meeting 06/23/2007
L3 WorkerB Meeting 05/26/2007
L4 WorkerA Phone 02/04/2006
L5 WorkerA Phone 06/23/2007
L6 WorkerC Phone 07/07/2007
L7 WorkerB Meeting 12/31/2007
L8 WorkerC Meeting 02/02/2008

Report needs to delete lines 1, 3, 4
Do you see the pattern?
Actually, in the end, I only need the most recent "month" not "date" for the
last column.

I hope you can understand what I'm asking and can help me.
Thank you.

Delete Duplicate Rows, by Date field
 

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 2/4/2008 by jwarburg
'

'
ActiveSheet.Cells.Sort _
Key1:=Range("A1"), _
Order1:=xlAscending, _
Key2:=Range("B1"), _
Order2:=xlAscending, _
Key3:=Range("C1"), _
Order3:=xlDescending, _
Header:=xlGuess, _
MatchCase:=False

RowCount = 1
Do While Range("A" & RowCount) < ""
If Range("A" & RowCount) = Range("A" & (RowCount + 1)) And _
Range("B" & RowCount) = Range("B" & (RowCount + 1)) Then

Rows(RowCount + 1).Delete
Else
RowCount = RowCount + 1
End If
Loop
End Sub


"DTTODGG" wrote:

Hello,

I need some help.
I have a huge spreadsheet containing several columns.
Column A = Names (maybe 2000 unique names out of 6000 rows)
Column B = Meeting or Phone Call
Column C = Date (column B was completed)

So, I have multiple entries for each name. I need to keep only the row with
the latest "Meeting" date and the latest "Phone Call" date. How would I do
this?

Example:
L1 WorkerA Meeting 01/04/2006
L2 WorkerA Meeting 06/23/2007
L3 WorkerB Meeting 05/26/2007
L4 WorkerA Phone 02/04/2006
L5 WorkerA Phone 06/23/2007
L6 WorkerC Phone 07/07/2007
L7 WorkerB Meeting 12/31/2007
L8 WorkerC Meeting 02/02/2008

Report needs to delete lines 1, 3, 4
Do you see the pattern?
Actually, in the end, I only need the most recent "month" not "date" for the
last column.

I hope you can understand what I'm asking and can help me.
Thank you.

Delete Duplicate Rows, by Date field
 

Public Sub ProcessData()
Dim i As Long
Dim LastRow As Long
Dim rng As Range

With Application

.ScreenUpdating = False
End With

With ActiveSheet

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

.Columns(4).Insert
.Range("D1").FormulaArray = _
"=C1=MAX(IF(($A$1:$A$" & LastRow & "=A1)*" & _
"($B$1:$B$" & LastRow & "=B1)," & _
"$C$1:$C$" & LastRow & "))"
.Range("d1").AutoFill .Range("D1").Resize(LastRow)
.Rows(1).Insert
.Range("D1").Value = "temp"
.Columns(4).AutoFilter field:=1, Criteria1:="FALSE"
On Error Resume Next
Set rng = .Range("D1").Resize(LastRow +
1).SpecialCells(xlCellTypeVisible)
On Error GoTo 0
If Not rng Is Nothing Then rng.EntireRow.Delete
Set rng = Nothing
.Columns(4).Delete

End With

With Application

.ScreenUpdating = True
End With

End Sub


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"DTTODGG" wrote in message
...
Hello,

I need some help.
I have a huge spreadsheet containing several columns.
Column A = Names (maybe 2000 unique names out of 6000 rows)
Column B = Meeting or Phone Call
Column C = Date (column B was completed)

So, I have multiple entries for each name. I need to keep only the row
with
the latest "Meeting" date and the latest "Phone Call" date. How would I do
this?

Example:
L1 WorkerA Meeting 01/04/2006
L2 WorkerA Meeting 06/23/2007
L3 WorkerB Meeting 05/26/2007
L4 WorkerA Phone 02/04/2006
L5 WorkerA Phone 06/23/2007
L6 WorkerC Phone 07/07/2007
L7 WorkerB Meeting 12/31/2007
L8 WorkerC Meeting 02/02/2008

Report needs to delete lines 1, 3, 4
Do you see the pattern?
Actually, in the end, I only need the most recent "month" not "date" for
the
last column.

I hope you can understand what I'm asking and can help me.
Thank you.

Delete Duplicate Rows, by Date field
 

Joel,

This is precisely what I wanted! But, the file is huge and the blinking on
the screen is driving me crazy ;-)

Is there a way to show a "progress bar" rather than the actual work being
done?

Also, is there a way, once it determines which row to save, could it convert
the date to Year and Quarter? Or add another column that contains the Year
and Quarter? Eventually, I would like to make a pretty chart showing the name
and the last quarter they were contacted.

Thank you so much!

"Joel" wrote:

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 2/4/2008 by jwarburg
'

'
ActiveSheet.Cells.Sort _
Key1:=Range("A1"), _
Order1:=xlAscending, _
Key2:=Range("B1"), _
Order2:=xlAscending, _
Key3:=Range("C1"), _
Order3:=xlDescending, _
Header:=xlGuess, _
MatchCase:=False

RowCount = 1
Do While Range("A" & RowCount) < ""
If Range("A" & RowCount) = Range("A" & (RowCount + 1)) And _
Range("B" & RowCount) = Range("B" & (RowCount + 1)) Then

Rows(RowCount + 1).Delete
Else
RowCount = RowCount + 1
End If
Loop
End Sub


"DTTODGG" wrote:

Hello,

I need some help.
I have a huge spreadsheet containing several columns.
Column A = Names (maybe 2000 unique names out of 6000 rows)
Column B = Meeting or Phone Call
Column C = Date (column B was completed)

So, I have multiple entries for each name. I need to keep only the row with
the latest "Meeting" date and the latest "Phone Call" date. How would I do
this?

Example:
L1 WorkerA Meeting 01/04/2006
L2 WorkerA Meeting 06/23/2007
L3 WorkerB Meeting 05/26/2007
L4 WorkerA Phone 02/04/2006
L5 WorkerA Phone 06/23/2007
L6 WorkerC Phone 07/07/2007
L7 WorkerB Meeting 12/31/2007
L8 WorkerC Meeting 02/02/2008

Report needs to delete lines 1, 3, 4
Do you see the pattern?
Actually, in the end, I only need the most recent "month" not "date" for the
last column.

I hope you can understand what I'm asking and can help me.
Thank you.

Delete Duplicate Rows, by Date field
 

Try my solution, no blinking

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"DTTODGG" wrote in message
...
Joel,

This is precisely what I wanted! But, the file is huge and the blinking on
the screen is driving me crazy ;-)

Is there a way to show a "progress bar" rather than the actual work being
done?

Also, is there a way, once it determines which row to save, could it
convert
the date to Year and Quarter? Or add another column that contains the Year
and Quarter? Eventually, I would like to make a pretty chart showing the
name
and the last quarter they were contacted.

Thank you so much!

"Joel" wrote:

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 2/4/2008 by jwarburg
'

'
ActiveSheet.Cells.Sort _
Key1:=Range("A1"), _
Order1:=xlAscending, _
Key2:=Range("B1"), _
Order2:=xlAscending, _
Key3:=Range("C1"), _
Order3:=xlDescending, _
Header:=xlGuess, _
MatchCase:=False

RowCount = 1
Do While Range("A" & RowCount) < ""
If Range("A" & RowCount) = Range("A" & (RowCount + 1)) And _
Range("B" & RowCount) = Range("B" & (RowCount + 1)) Then

Rows(RowCount + 1).Delete
Else
RowCount = RowCount + 1
End If
Loop
End Sub


"DTTODGG" wrote:

Hello,

I need some help.
I have a huge spreadsheet containing several columns.
Column A = Names (maybe 2000 unique names out of 6000 rows)
Column B = Meeting or Phone Call
Column C = Date (column B was completed)

So, I have multiple entries for each name. I need to keep only the row
with
the latest "Meeting" date and the latest "Phone Call" date. How would I
do
this?

Example:
L1 WorkerA Meeting 01/04/2006
L2 WorkerA Meeting 06/23/2007
L3 WorkerB Meeting 05/26/2007
L4 WorkerA Phone 02/04/2006
L5 WorkerA Phone 06/23/2007
L6 WorkerC Phone 07/07/2007
L7 WorkerB Meeting 12/31/2007
L8 WorkerC Meeting 02/02/2008

Report needs to delete lines 1, 3, 4
Do you see the pattern?
Actually, in the end, I only need the most recent "month" not "date"
for the
last column.

I hope you can understand what I'm asking and can help me.
Thank you.