I have a list of numbers in a column that can repeat. I want to delete all
rows if that number is repeated.

Sort the spreadsheet by the column with duplicates in ascending order
In a helper column type-
=IF(A2=A1,1,0)
and copy to the last occupied row
Change the formula to numbers. To do this -

Highlight the helper column
Click on <Edit<Copy<Edit<Paste special<Values<OK
Sort on the helper column
Delete all rows where value in the helper column is 1

Regards.

Bill Ridgeway
Computer Solutions

"PPete" wrote in message
...
I have a list of numbers in a column that can repeat. I want to delete all
rows if that number is repeated.

The easiest way is to highlight all the data columns, Sort by your number
list, and delete the duplicates. Or Autofilter for each value, right click
and delete the duplicates. If this is too large of a list to do manually,
you can create a fiormula in a new column:
1. After sorting, in the 2nd row that contains data in the new column,
create the follwoing formula:
=IF(A2=A3,"Y","N")
2. You iwll have to change "A" to the column letter containing your list.
The numbers in this formula assumes that Row1 is the header, and Row 2 starts
the data. Once entered, drag down to fill the formula in on the remaining
rows.
3. Autofilter the sheet by the "new column" you created for "Y".
Highlight all the "Y" rows, right click and delete.
4. Remove the filter and delete the formula column.

HTH


"PPete" wrote:

I have a list of numbers in a column that can repeat. I want to delete all
rows if that number is repeated.

The real easiest way is to use DataFilterAdvanced Filter.

Check Unique records only and Copy to another location.

Needs no sorting, helper column, formula copying or deletion of columns.


Gord Dibben MS Excel MVP

On Thu, 13 Jul 2006 12:52:02 -0700, Wiley
wrote:

The easiest way is to highlight all the data columns, Sort by your number
list, and delete the duplicates. Or Autofilter for each value, right click
and delete the duplicates. If this is too large of a list to do manually,
you can create a fiormula in a new column:
1. After sorting, in the 2nd row that contains data in the new column,
create the follwoing formula:
=IF(A2=A3,"Y","N")
2. You iwll have to change "A" to the column letter containing your list.
The numbers in this formula assumes that Row1 is the header, and Row 2 starts
the data. Once entered, drag down to fill the formula in on the remaining
rows.
3. Autofilter the sheet by the "new column" you created for "Y".
Highlight all the "Y" rows, right click and delete.
4. Remove the filter and delete the formula column.

HTH


"PPete" wrote:

I have a list of numbers in a column that can repeat. I want to delete all
rows if that number is repeated.

Gord,

I agree. I like your solution better. I forgot about that feature.


"Gord Dibben" wrote:

The real easiest way is to use DataFilterAdvanced Filter.

Check Unique records only and Copy to another location.

Needs no sorting, helper column, formula copying or deletion of columns.


Gord Dibben MS Excel MVP

On Thu, 13 Jul 2006 12:52:02 -0700, Wiley
wrote:

The easiest way is to highlight all the data columns, Sort by your number
list, and delete the duplicates. Or Autofilter for each value, right click
and delete the duplicates. If this is too large of a list to do manually,
you can create a fiormula in a new column:
1. After sorting, in the 2nd row that contains data in the new column,
create the follwoing formula:
=IF(A2=A3,"Y","N")
2. You iwll have to change "A" to the column letter containing your list.
The numbers in this formula assumes that Row1 is the header, and Row 2 starts
the data. Once entered, drag down to fill the formula in on the remaining
rows.
3. Autofilter the sheet by the "new column" you created for "Y".
Highlight all the "Y" rows, right click and delete.
4. Remove the filter and delete the formula column.

HTH


"PPete" wrote:

I have a list of numbers in a column that can repeat. I want to delete all
rows if that number is repeated.

Thanks Wiley.

Many ways to disrobe the feline.


Gord

On Fri, 14 Jul 2006 05:58:02 -0700, Wiley
wrote:

Gord,

I agree. I like your solution better. I forgot about that feature.


"Gord Dibben" wrote:

The real easiest way is to use DataFilterAdvanced Filter.

Check Unique records only and Copy to another location.

Needs no sorting, helper column, formula copying or deletion of columns.


Gord Dibben MS Excel MVP

On Thu, 13 Jul 2006 12:52:02 -0700, Wiley
wrote:

The easiest way is to highlight all the data columns, Sort by your number
list, and delete the duplicates. Or Autofilter for each value, right click
and delete the duplicates. If this is too large of a list to do manually,
you can create a fiormula in a new column:
1. After sorting, in the 2nd row that contains data in the new column,
create the follwoing formula:
=IF(A2=A3,"Y","N")
2. You iwll have to change "A" to the column letter containing your list.
The numbers in this formula assumes that Row1 is the header, and Row 2 starts
the data. Once entered, drag down to fill the formula in on the remaining
rows.
3. Autofilter the sheet by the "new column" you created for "Y".
Highlight all the "Y" rows, right click and delete.
4. Remove the filter and delete the formula column.

HTH


"PPete" wrote:

I have a list of numbers in a column that can repeat. I want to delete all
rows if that number is repeated.