Count unique data entires

[sgoldbe2]

I have an excel spreadsheet which contains an enormous amount of data( I mean truly enormous, its actually 40 files). One of the columns is ip addressees, each row is a unique event, but many of the ip addressees are the same. I want to be able to count the number of unique ip address. Any suggestions?

[Trebor76]

sgoldbe2,

The following array formula will work but based on the large dataset you allude to, it will slow things down. For illustration purposes I've assumed that data goes down to row 1,000 - change this as required. To create the formula, follow these three steps

1. Enter (or copy and paste) the following formula in your destination cell:
=SUM(IF(LEN(P1:P1000),1/COUNTIF(P1:P1000,P1:P1000)))

2. From the keyboard, press F2 and then Ctrl+Shift+Enter together

3. You should now notice that the formula from Step 1 now has {} brackets around it, indicating that's an array formula. Note you must do step two whenever you change the formula.

HTH

Robert

[sgoldbe2]

thanks that worked perfectly.