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

 

[markmzz]

Maybe this:

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: right;;">11/8/11 04:15:44</td><td style="text-align: right;;"></td><td style="text-align: center;;">Total</td><td style="text-align: center;;">4</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">11/8/11 04:15:44</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">11/8/11 04:15:44</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">12/8/11 15:10:18</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;">14/8/11 18:15:17</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;">14/8/11 19:00:00</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;">14/8/11 19:00:00</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style=";">*****</td><td style=";">*****</td><td style=";">*****</td><td style=";">*****</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet8</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D1</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">--(<font color="Red">FREQUENCY(<font color="Green">A1:A7,A1:A7</font>)>0</font>)</font>)</td></tr></tbody></table></td></tr></table><br />
Markmzz

 

[reaktorblue]

I tried that formula, where my data is entered in the range of C13:C40 however the result I seem to get is 0. Just to recap, I tried =SUMPRODUCT(--(FREQUENCY(C13:C40,C13:C40)>0)) but the only result I was able to get was 0.

By the way, I really like the format you're using to illustrate the table structure and formula. It's made it much easier to understand, although I'm still having trouble with it.

 

[markmzz]

Try this (for TXT):

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">11/08/11 - 4:15:44 AM</td><td style="text-align: right;;"></td><td style="text-align: center;;">Total</td><td style="text-align: center;;">4</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">11/08/11 - 4:15:44 AM</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">11/08/11 - 4:15:44 AM</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">12/08/11 - 3:10:18 PM</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">14/08/11 - 6:15:17 PM</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">14/08/11 - 7:00:00 PM</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style=";">14/08/11 - 7:00:00 PM</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style=";">*****</td><td style=";">*****</td><td style=";">*****</td><td style=";">*****</td></tr></tbody></table><p style="width:4.2em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet9T</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D1</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">--(<font color="Red">FREQUENCY(<font color="Green">MATCH(<font color="Purple">A1:A7,A1:A7,0</font>),MATCH(<font color="Purple">A1:A7,A1:A7,0</font>)</font>)>0</font>)</font>)</td></tr></tbody></table></td></tr></table><br />

Use the links below for helper to post your examples.

Instructions:
http://www.mrexcel.com/forum/showpost.php?p=2545970&postcount=2

File HtmlMaker20101230.zip:
https://skydrive.live.com/?cid=8cffdec0ce27e813&sc=documents&id=8CFFDEC0CE27E813!189

Markmzz

 

[Aladin Akyurek]

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

Control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF(A2:A8<>"",MATCH("~"&A2:A8,A2:A8&"",0)),ROW(A2:A8)-ROW(A2)+1),1))

where A2:A8 houses the sample you provided. If no chars like < can occur around the entries, you can omit the "~"& and &"" bits from the formula.

 

[Yahya]

try this

Excel Workbook
	A	B
2	11/08/11 - 4:15:44 AM	4
3	11/08/11 - 4:15:44 AM
4	11/08/11 - 4:15:44 AM
5	12/08/11 - 3:10:18 PM
6	14/08/11 - 6:15:17 PM
7	14/08/11 - 7:00:00 PM
8	14/08/11 - 7:00:00 PM
Sheet2

Excel 2007

Cell Formulas
Range	Formula
B2	=SUMPRODUCT(1/COUNTIF(A2:A8,A2:A8))

 

[Aladin Akyurek]

try this

Excel Workbook
	A	B
2	11/08/11 - 4:15:44 AM	4
3	11/08/11 - 4:15:44 AM
4	11/08/11 - 4:15:44 AM
5	12/08/11 - 3:10:18 PM
6	14/08/11 - 6:15:17 PM
7	14/08/11 - 7:00:00 PM
8	14/08/11 - 7:00:00 PM
Sheet2

Excel 2007

Cell Formulas
Range	Formula
B2	=SUMPRODUCT(1/COUNTIF(A2:A8,A2:A8))

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

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

 

[taurean]

Just for info (my version is not so good), you can replace SUMPRODUCT in markmzz's formula with following:
=COUNT(IF(FREQUENCY(MATCH(A1:A7,A1:A7,0),MATCH(A1:A7,A1:A7,0))>0,FREQUENCY(MATCH(A1:A7,A1:A7,0),MATCH(A1:A7,A1:A7,0))))

 

[taurean]

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

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

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

 

[Aladin Akyurek]

Just for info (my version is not so good), you can replace SUMPRODUCT in markmzz's formula with following:
=COUNT(IF(FREQUENCY(MATCH(A1:A7,A1:A7,0),MATCH(A1:A7,A1:A7,0))>0,FREQUENCY(MATCH(A1:A7,A1:A7,0),MATCH(A1:A7,A1:A7,0))))

Another inefficent proposal I'm afraid...

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