How do I consoldiate a List of numbers to show each number once ?

[Guest]

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

 

[Bill Ridgeway]

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

 

[Guest]

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

 

[Gord Dibben]

The real easiest way is to use Data>Filter>Advanced 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

 

[Guest]

Gord,

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

 

[Gord Dibben]

Thanks Wiley.

Many ways to disrobe the feline.


Gord