Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Fomula
I am currrently working on a project that includes over 30000 entries. The
thing is, I do not need to do anything with any entry that occurs once. What I am looking for is a fomula that will take a colum and sort it by entries that occur more that once. For ex. If I had 5 5 5 6 7 7 8 9 9 9 I want in order of most to least, everything that occurs more than once. If this works right everything that occurs once will be at the bottom. It is basically a sort that does not include the once occuring entries. any help is welcome |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Fomula
The easiest way to get frequency of occurrence or duplicate status on a
single column of items is to create a pivot table. 1. Make sure the column has a header cell 2. Select the column 3. Pull-down: Data Pivot Table to start the wizard 4. On the layout tab, drag the header cell into both the Row area and the Data area. 5. The Data area should show Count of header 6. Click OK The resulting table will show each item in the column and the number of times it occurs. If you ignore the items with a count of one, you are done. -- Gary''s Student "Steve" wrote: I am currrently working on a project that includes over 30000 entries. The thing is, I do not need to do anything with any entry that occurs once. What I am looking for is a fomula that will take a colum and sort it by entries that occur more that once. For ex. If I had 5 5 5 6 7 7 8 9 9 9 I want in order of most to least, everything that occurs more than once. If this works right everything that occurs once will be at the bottom. It is basically a sort that does not include the once occuring entries. any help is welcome |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Fomula
use a formula such as
=COUNTIF($A$1:$A$30000,A1) and fill this down a help column then sort the two columns by the second column 30000 is a large selection it may pay to take a back up first -- Greetings from New Zealand Bill K "Steve" wrote in message ... I am currrently working on a project that includes over 30000 entries. The thing is, I do not need to do anything with any entry that occurs once. What I am looking for is a fomula that will take a colum and sort it by entries that occur more that once. For ex. If I had 5 5 5 6 7 7 8 9 9 9 I want in order of most to least, everything that occurs more than once. If this works right everything that occurs once will be at the bottom. It is basically a sort that does not include the once occuring entries. any help is welcome |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I use fill hand to copy the fomula in this case? | Excel Discussion (Misc queries) | |||
To copy fomula | Excel Worksheet Functions | |||
Viewing the whole path and not the fomula | Excel Discussion (Misc queries) | |||
How to get cell value with the ID from a fomula? | Excel Discussion (Misc queries) | |||
Scurve fomula in Excel | Excel Worksheet Functions |