Hi all,
I have a tight deadline and was wondering if anyone knows how to write a forumula for me.


I have a spreadsheet containing 62,293 lines of data. In column A I have the names of servers. I need a count of these servers, but some are duplicates. Obviously I don't want to count those. I tried the “Remove Duplicates” under the Data Tab but it is greyed out. Why? Is there a simple way to do this?

I've only recently graduated from Excel 2003 to 2010, so I haven't yet tried the "remove duplicates" feature. But the way I'd get a count is this:

1) Sort on the server name.

2) In a column somewhere to the right—say X—make a simple formula: "A2=A1".

That's assuming the server name is in column A; use whichever column is appropriate. It's also assuming that there's one header row at the top, that the server names start in row 2. Now column X has a series of True and False values; it's False wherever the server name is not identical to the one above it (in other words this is a new server), and True if it's the same. All the Trues are duplicate, you see. So:

3) At the bottom of column X, use a COUNTIF function to count the False values, something like "=COUNTIF(X2:X62294,FALSE)".

How's that?

Please do not cross post without links. You have the same topic at Excelforum. Please read this message to cross-posters

__________________
Using O365 v2503 - Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

Pecoflyer:
I went and read the post and was not aware of this. I apologize. I was in a hurry to get an answer, as a lot of us are, and wasn't aware of the etiquette. I will keep this in mind going forward.