Do not delete last item of each

[Guest]

I have a worksheet with a large list of line items that has many duplicates.
I want to be able to delete some of these line items, but not all of them. I
want code that will protect the last of each duplicate line item, by not
allowing me to delete all of the line items. If you need more detail, let me
know.

Kent

 

[Myrna Larson]

Do you have to have VBA, or will worksheet formulas suffice? If this is a
one-time job, formulas should be OK.

Does your list consist of a single column? Do you determine which rows are
duplicates by looking at just one column or all columns? I suspect the former,
because if the latter were true it wouldn't make any difference which one you
keep.

If the latter, look at Filter/Advanced and select Unique Records Only.

If the former, let's assume that you are interested only in duplication of the
information in column A, the list occupies A1:G500, you have headers in row 1,
and the first data row is 2.

In H2, put this formula

=COUNTIF($A2:$A$500,$A2)

and copy it down.

Then use AutoFilter to display those rows where there's a 1 in column H. Those
are the last entry for the item in column A. You can now copy these rows to
another location.

Or, if you want to go ahead and delete the items, change the filter to display
rows where the value is > 1. Then delete the visible rows.