Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
COUNTIF forumla help
I'll do my best on explaining this, it might be a little complicated...
I have a spreadsheet of 21,000 entries for machine names with a certain application installed and all reported versions of this application. I've used the following formula to show me duplicate occurances of machine names where the system has multiple versions loaded. COUNTIF($A$2:$A2,A2)1 Now that formula works fine for showing me the 2nd, 3rd or more occurance of a machine name. But now I need to provide a list with all machine names that appear on the list more than once. I am not sure how I can get the COUNTIF statement to identify all occurances, especially the 1st occurance of a machine name. I really don't want to scroll through the list to delete non-duplicate entries and this will be a weekly task for the next few weeks. If I was not clear in my question, please let me now and I'll do my best to explain it more thoroughly. Thanks, Zeke |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
COUNTIF forumla help
=COUNTIF(A:A,A2)1
-- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Zeke009" wrote in message ... I'll do my best on explaining this, it might be a little complicated... I have a spreadsheet of 21,000 entries for machine names with a certain application installed and all reported versions of this application. I've used the following formula to show me duplicate occurances of machine names where the system has multiple versions loaded. COUNTIF($A$2:$A2,A2)1 Now that formula works fine for showing me the 2nd, 3rd or more occurance of a machine name. But now I need to provide a list with all machine names that appear on the list more than once. I am not sure how I can get the COUNTIF statement to identify all occurances, especially the 1st occurance of a machine name. I really don't want to scroll through the list to delete non-duplicate entries and this will be a weekly task for the next few weeks. If I was not clear in my question, please let me now and I'll do my best to explain it more thoroughly. Thanks, Zeke |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
COUNTIF forumla help
Assume your data at A1 looks like this:
ListMa L C J I B F H D J B B J D H B J D C and you want to generate this list: ListMa C J B H D Create these 2 cells at B1 XYZZ FALSE B1 can be any text but ListMa, or leave blank. B2 has the formula =COUNTIF($A$2:$A$19,A2)1 Data Filter Advanced Filter Copy to another location List Range: $A$1:$A$19 Criteria Range: $B$1:$B$2 Copy to: $C$1 Check Unique Records only There are many ways to make mistakes here. The safer, more flexible, updatable way is with Pivot Table. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
is it possible for Vlookup and CountIf function in the same forumla? | Excel Discussion (Misc queries) | |||
Add forumla | Excel Discussion (Misc queries) | |||
Forumla Help | Excel Discussion (Misc queries) | |||
Forumla Help!countif?IF?If(AND? if(OR? | Excel Discussion (Misc queries) | |||
Forumla Help | Excel Discussion (Misc queries) |