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.

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.

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" <(E-Mail Removed)> wrote in message
news:ACF7F9BB-6210-4976-83B5-(E-Mail Removed)...
> 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.

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.

Try my solution, no blinking

--
---
HTH

Bob


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



"DTTODGG" <(E-Mail Removed)> wrote in message
news:35CC60ED-4ADA-4F06-A404-(E-Mail Removed)...
> 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.