How do I delete unique numbers from a list and save duplicates?
 

I've combined two lists of references from two worksheets and now want to
sort the new list to remove unique items and keep the duplicates. I'm using
Excel 2003.

How do I delete unique numbers from a list and save duplicates?
 

Very simple. Say the combined list is in column A. In B1 enter:
=COUNTIF(A:A,A1) and copy down

Then sort cols A & B by B.
Then delete all rows in which the value in col B equals 1
--
Gary''s Student - gsnu200816


"Kevin Alcock" wrote:

I've combined two lists of references from two worksheets and now want to
sort the new list to remove unique items and keep the duplicates. I'm using
Excel 2003.

How do I delete unique numbers from a list and save duplicates?
 

Hi,

Create a new column
=COUNTIF($A$1:$A$1000,A1)
copy this formula down and turn on AutoFilter, Filter for the new column for
1's. This will be all the unique items. Select the range and press Del.
Turn off AutoFilter and the remaining records are you duplicates.

If this helps, please click the Yes button

Cheers,
Shane Devenshire

"Kevin Alcock" wrote:

I've combined two lists of references from two worksheets and now want to
sort the new list to remove unique items and keep the duplicates. I'm using
Excel 2003.

How do I delete unique numbers from a list and save duplicates?
 

Hi,

I really should add that "Uniques" has two meanings - in the following list

1,2,4,1

There could be 3 unique items or 2 depending on your definition. The
solution I gave was for the second, if you want to do the first then change
the formula to read
=COUNTIF($A$1:$A1,A1) and fill this down and repeat the remaining steps.

If this helps, please click the Yes button

Cheers,
Shane Devenshire


"Kevin Alcock" wrote:

I've combined two lists of references from two worksheets and now want to
sort the new list to remove unique items and keep the duplicates. I'm using
Excel 2003.

How do I delete unique numbers from a list and save duplicates?
 

Let's assume the numbers to be in A1:A100
In B1 (insert ne column if needed - we can delete it alter)
=COUNT$A$1:$A$100,A1)
Copy down the column
Unique items will give result 1; duplicates will give 2, triplicates 3, etc
Sort on column B
Delete rows with B values 1
Delete column B

--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Kevin Alcock" <Kevin wrote in message
...
I've combined two lists of references from two worksheets and now want to
sort the new list to remove unique items and keep the duplicates. I'm
using
Excel 2003.