macro to eliminate repeating account numbers

[Sarah]

Hello -

I have report that shows customer account numbers and the date transactions
occurred for that account. Some customers have more than one row on my
spreadsheet because they have made more than one transaction.

I only want to show the FIRST transaction each customer made and delete each
subsequent one for each customer.

Macros are fairly new to me, but what I want to do is sort the spreadsheet
by account number, then transaction date (I know how to make the macro do
that part) and then program the macro to delete any duplicate transaction
rows per customer, leaving just that original transaction.

I would really appreciate any help in accomplishing this. Thanks!

[mudraker]

SARAH

Try this on a backup copy of your data

It assumes you have titles in row 1, Account numbers in column A, Dates in column B

The sort command was recorded in Excel 2003 and may not work on older versions of Excel.

Sub Macro1()
Dim lRow As Long
Dim DelRows As Integer

Cells.Sort Key1:=Range("A2"), Order1:=xlAscending, Key2:=Range("B2") _
, Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2 _
:=xlSortNormal

For lRow = Cells(Rows.Count, "a").End(xlUp).Row To 2 Step -1
If Cells(lRow, "a").Value = Cells(lRow - 1, "a").Value Then
DelRows = DelRows + 1
Else
If DelRows > 0 Then
Rows(lRow + 1 & ":" & lRow + DelRows).Delete
End If
DelRows = 0
End If
Next lRow
If DelRows > 0 Then
Rows(lRow + 1 & ":" & lRow + DelRows).Delete
End If
End Sub

[Bob Phillips]

Use Autofilter

Assuming your data in column A, add this formula to B1

=COUNTIF($A$1:A1,A1)>1

and copy down.

Then apply a filter to column B (Data>Autofilter). Click the dropdown,
select TRUE, and then delete the visible rows. Remove Autofilter, and it is
done.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Sarah" <[email protected]> wrote in message
news:[email protected]...
> Hello -
>
> I have report that shows customer account numbers and the date
transactions
> occurred for that account. Some customers have more than one row on my
> spreadsheet because they have made more than one transaction.
>
> I only want to show the FIRST transaction each customer made and delete
each
> subsequent one for each customer.
>
> Macros are fairly new to me, but what I want to do is sort the spreadsheet
> by account number, then transaction date (I know how to make the macro do
> that part) and then program the macro to delete any duplicate transaction
> rows per customer, leaving just that original transaction.
>
> I would really appreciate any help in accomplishing this. Thanks!

[Sarah]

That worked perfectly! Thanks so much!

"Sarah" wrote:

> Hello -
>
> I have report that shows customer account numbers and the date transactions
> occurred for that account. Some customers have more than one row on my
> spreadsheet because they have made more than one transaction.
>
> I only want to show the FIRST transaction each customer made and delete each
> subsequent one for each customer.
>
> Macros are fairly new to me, but what I want to do is sort the spreadsheet
> by account number, then transaction date (I know how to make the macro do
> that part) and then program the macro to delete any duplicate transaction
> rows per customer, leaving just that original transaction.
>
> I would really appreciate any help in accomplishing this. Thanks!