Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sum duplicates unique values and delete dupliques | Excel Worksheet Functions | |||
Array Formulas - Unique List from List with Duplicates | Excel Discussion (Misc queries) | |||
How do I delete formulas but save numbers? | Excel Worksheet Functions | |||
How can I create a list of random numbers with no duplicates? | Excel Discussion (Misc queries) | |||
how to extract unique numbers once from a list of repeated numbers? | Excel Discussion (Misc queries) |