Count unique entries

[reaktorblue]

Hiyas,

I have a spreadsheet where I'm trying to calculate the amount of unique entries in such that it would mean the amount of transactions.

Since each line has a cell which contains the date, many lines with the same date (down to the second) would indicate 1 transaction. This means the following is one transaction.

11/08/11 - 4:15:44 AM
11/08/11 - 4:15:44 AM
11/08/11 - 4:15:44 AM

As an FYI, these dates are in a UK format and may look confusing without mentioning this. What I need to accomplish is tallying up the number of transactions, so in this example, if I were to have something along the lines of the following:

11/08/11 - 4:15:44 AM
11/08/11 - 4:15:44 AM
11/08/11 - 4:15:44 AM
12/08/11 - 3:10:18 PM
14/08/11 - 6:15:17 PM
14/08/11 - 7:00:00 PM
14/08/11 - 7:00:00 PM

This would be 4 unique values and as such, 4 separate transactions. After much research I thought I would be able to accomplish this using the =FREQUENCY function but I cannot seem to figure this out.

Could anyone give me a hand and perhaps shed some light on my problem? Thank you very much for all of the help everyone has given me over the years. I greatly appreciate it!!



Warmest Regards,

RB

 

[taurean]

Thank you for the valuable analysis. I guess I have possibly created the worst of the lot!

I am not good at prophecies, but this one turned true. See post #10

I was nearly sure about it! See edit...

 

[Yahya]

It's less efficent than one with FREQUENCY...

http://www.mrexcel.com/forum/showthread.php?t=292473

thank you aladin, i will study it, i want to ask what is the code which calculate the formula speed

 

[Aladin Akyurek]

thank you aladin, i will study it, i want to ask what is the code which calculate the formula speed

I often use FastExcel from http://www.decisionmodels.com/.

A note though. Temporal profiling is not that straightforward.

 

[T. Valko]

my data is entered in the range of C13:C40

For a relatively small range like that use this SIMPLE formula:

=SUMPRODUCT((C13:C40<>"")/COUNTIF(C13:C40,C13:C40&""))

 

[T. Valko]

i want to ask what is the code which calculate the formula speed

There is calculation timer code here:

http://msdn2.microsoft.com/en-us/library/aa730921.aspx