Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
List Duplicates
Column A1 through A100 contains a list of names, some of which are
duplicated. what is the formula in Column B that will list only the names in column A, but only list them once if they are duplicated and also names that are not duplicate TIA -- Thank You cheers, francis |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
List Duplicates
If you are using 2007 you can use the menu function 'Remove duplicates'.
Sorry that I am not sure whether this is available in previous versions. Another way is Sort Column A in ascending order. Apply thiis formula in B2 and then copy that upto B100. This will return the distinct values in Column B. Copy the entire column and Paste special there itself and sort to get the unique list. =IF(A3<A2,A2,"") If this post helps click Yes --------------- Jacob Skaria "Francis" wrote: Column A1 through A100 contains a list of names, some of which are duplicated. what is the formula in Column B that will list only the names in column A, but only list them once if they are duplicated and also names that are not duplicate TIA -- Thank You cheers, francis |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
List Duplicates
If you are using 2007 you can use the menu function
'Remove duplicates'. Sorry that I am not sure whether this is available in previous versions. It is not. Another way, and probably the easiest, is to use Advanced filter to extract the unique values. Let's assume your data is in the range A1:A10. Select the range A1:A10 Goto the menu DataFilterAdvanced filter Answer the prompt about using a column header. Select Copy to another location Copy to: B1 Select: Unique records only OK -- Biff Microsoft Excel MVP "Jacob Skaria" wrote in message ... If you are using 2007 you can use the menu function 'Remove duplicates'. Sorry that I am not sure whether this is available in previous versions. Another way is Sort Column A in ascending order. Apply thiis formula in B2 and then copy that upto B100. This will return the distinct values in Column B. Copy the entire column and Paste special there itself and sort to get the unique list. =IF(A3<A2,A2,"") If this post helps click Yes --------------- Jacob Skaria "Francis" wrote: Column A1 through A100 contains a list of names, some of which are duplicated. what is the formula in Column B that will list only the names in column A, but only list them once if they are duplicated and also names that are not duplicate TIA -- Thank You cheers, francis |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
List Duplicates
Hi Francis,
I don't believe that Remove duplicate is available in 2003. There is an add-in but I know you have to buy it. There is also an alternative. You can use Advanced Filter. Go to Data, Advanced Filter. On the action, you can either filter the list, in place OR Copy to another location. If you plan to paste it onto another place, just use Copy to another location as it will save you from having to copy-paste it yourself. If you do select this, you will enable the "Copy to:" option. On the list range, select your cells, which is A1:A100. On the Copy to range, point it to wherever you want it to copy to (i.e. B1) Then make sure you tick the box "Unique records only". Then hit OK. Storm "Francis" wrote: Column A1 through A100 contains a list of names, some of which are duplicated. what is the formula in Column B that will list only the names in column A, but only list them once if they are duplicated and also names that are not duplicate TIA -- Thank You cheers, francis |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
List Duplicates
Hello,
Another option ist my UDF Pfreq which lists how often an item appears: for example, array-enter into B1:C20 =Pfreq(A1:A100) My UDF you can find he http://www.sulprobil.com/html/pfreq.html Regards, Bernd |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
list duplicates | Excel Discussion (Misc queries) | |||
Duplicates in a list | Excel Discussion (Misc queries) | |||
Condensing a list with duplicates to a list with non-duplicates | Excel Worksheet Functions | |||
Array Formulas - Unique List from List with Duplicates | Excel Discussion (Misc queries) | |||
How to remove duplicates from a list and copy new list to new colu | Excel Worksheet Functions |