Unique Rows

[Partjob]

Hi
I want to be able to count unique rows of data.
I have three columns (A) has a date (B) has the name of a person and (C) has an audit type.

I know I need an array but not sure how to structure it for 3 columns
<TABLE style="WIDTH: 184pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=245 border=0 x:str><COLGROUP><COL style="WIDTH: 53pt; mso-width-source: userset; mso-width-alt: 2596" width=71><COL style="WIDTH: 19pt; mso-width-source: userset; mso-width-alt: 914" width=25><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 16pt; mso-width-source: userset; mso-width-alt: 768" width=21><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #e2e2e2; BORDER-TOP: #e2e2e2; BORDER-LEFT: #e2e2e2; WIDTH: 53pt; BORDER-BOTTOM: #e2e2e2; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=71 height=17>Date</TD><TD style="BORDER-RIGHT: #e2e2e2; BORDER-TOP: #e2e2e2; BORDER-LEFT: #e2e2e2; WIDTH: 19pt; BORDER-BOTTOM: #e2e2e2; BACKGROUND-COLOR: transparent" width=25></TD><TD style="BORDER-RIGHT: #e2e2e2; BORDER-TOP: #e2e2e2; BORDER-LEFT: #e2e2e2; WIDTH: 48pt; BORDER-BOTTOM: #e2e2e2; BACKGROUND-COLOR: transparent" width=64>Name</TD><TD style="BORDER-RIGHT: #e2e2e2; BORDER-TOP: #e2e2e2; BORDER-LEFT: #e2e2e2; WIDTH: 16pt; BORDER-BOTTOM: #e2e2e2; BACKGROUND-COLOR: transparent" width=21></TD><TD style="BORDER-RIGHT: #e2e2e2; BORDER-TOP: #e2e2e2; BORDER-LEFT: #e2e2e2; WIDTH: 48pt; BORDER-BOTTOM: #e2e2e2; BACKGROUND-COLOR: transparent" width=64>Audit type</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #e2e2e2; BORDER-TOP: #e2e2e2; BORDER-LEFT: #e2e2e2; BORDER-BOTTOM: #e2e2e2; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num="39539">01/04/2008</TD><TD class=xl24 style="BORDER-RIGHT: #e2e2e2; BORDER-TOP: #e2e2e2; BORDER-LEFT: #e2e2e2; BORDER-BOTTOM: #e2e2e2; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #e2e2e2; BORDER-TOP: #e2e2e2; BORDER-LEFT: #e2e2e2; BORDER-BOTTOM: #e2e2e2; BACKGROUND-COLOR: transparent">David</TD><TD style="BORDER-RIGHT: #e2e2e2; BORDER-TOP: #e2e2e2; BORDER-LEFT: #e2e2e2; BORDER-BOTTOM: #e2e2e2; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #e2e2e2; BORDER-TOP: #e2e2e2; BORDER-LEFT: #e2e2e2; BORDER-BOTTOM: #e2e2e2; BACKGROUND-COLOR: transparent">CDM</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #e2e2e2; BORDER-TOP: #e2e2e2; BORDER-LEFT: #e2e2e2; BORDER-BOTTOM: #e2e2e2; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num="39539">01/04/2008</TD><TD class=xl24 style="BORDER-RIGHT: #e2e2e2; BORDER-TOP: #e2e2e2; BORDER-LEFT: #e2e2e2; BORDER-BOTTOM: #e2e2e2; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #e2e2e2; BORDER-TOP: #e2e2e2; BORDER-LEFT: #e2e2e2; BORDER-BOTTOM: #e2e2e2; BACKGROUND-COLOR: transparent">John</TD><TD style="BORDER-RIGHT: #e2e2e2; BORDER-TOP: #e2e2e2; BORDER-LEFT: #e2e2e2; BORDER-BOTTOM: #e2e2e2; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #e2e2e2; BORDER-TOP: #e2e2e2; BORDER-LEFT: #e2e2e2; BORDER-BOTTOM: #e2e2e2; BACKGROUND-COLOR: transparent">1 in 10</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #e2e2e2; BORDER-TOP: #e2e2e2; BORDER-LEFT: #e2e2e2; BORDER-BOTTOM: #e2e2e2; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num="39568">30/04/2008</TD><TD class=xl24 style="BORDER-RIGHT: #e2e2e2; BORDER-TOP: #e2e2e2; BORDER-LEFT: #e2e2e2; BORDER-BOTTOM: #e2e2e2; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #e2e2e2; BORDER-TOP: #e2e2e2; BORDER-LEFT: #e2e2e2; BORDER-BOTTOM: #e2e2e2; BACKGROUND-COLOR: transparent">Bill</TD><TD style="BORDER-RIGHT: #e2e2e2; BORDER-TOP: #e2e2e2; BORDER-LEFT: #e2e2e2; BORDER-BOTTOM: #e2e2e2; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #e2e2e2; BORDER-TOP: #e2e2e2; BORDER-LEFT: #e2e2e2; BORDER-BOTTOM: #e2e2e2; BACKGROUND-COLOR: transparent">1 in 10</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #e2e2e2; BORDER-TOP: #e2e2e2; BORDER-LEFT: #e2e2e2; BORDER-BOTTOM: #e2e2e2; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num="39569">01/05/2008</TD><TD class=xl24 style="BORDER-RIGHT: #e2e2e2; BORDER-TOP: #e2e2e2; BORDER-LEFT: #e2e2e2; BORDER-BOTTOM: #e2e2e2; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #e2e2e2; BORDER-TOP: #e2e2e2; BORDER-LEFT: #e2e2e2; BORDER-BOTTOM: #e2e2e2; BACKGROUND-COLOR: transparent">David</TD><TD style="BORDER-RIGHT: #e2e2e2; BORDER-TOP: #e2e2e2; BORDER-LEFT: #e2e2e2; BORDER-BOTTOM: #e2e2e2; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #e2e2e2; BORDER-TOP: #e2e2e2; BORDER-LEFT: #e2e2e2; BORDER-BOTTOM: #e2e2e2; BACKGROUND-COLOR: transparent">Vehicle</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #e2e2e2; BORDER-TOP: #e2e2e2; BORDER-LEFT: #e2e2e2; BORDER-BOTTOM: #e2e2e2; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num="39569">01/05/2008</TD><TD class=xl24 style="BORDER-RIGHT: #e2e2e2; BORDER-TOP: #e2e2e2; BORDER-LEFT: #e2e2e2; BORDER-BOTTOM: #e2e2e2; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #e2e2e2; BORDER-TOP: #e2e2e2; BORDER-LEFT: #e2e2e2; BORDER-BOTTOM: #e2e2e2; BACKGROUND-COLOR: transparent">David</TD><TD style="BORDER-RIGHT: #e2e2e2; BORDER-TOP: #e2e2e2; BORDER-LEFT: #e2e2e2; BORDER-BOTTOM: #e2e2e2; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #e2e2e2; BORDER-TOP: #e2e2e2; BORDER-LEFT: #e2e2e2; BORDER-BOTTOM: #e2e2e2; BACKGROUND-COLOR: transparent">Vehicle</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #e2e2e2; BORDER-TOP: #e2e2e2; BORDER-LEFT: #e2e2e2; BORDER-BOTTOM: #e2e2e2; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num="39569">01/05/2008</TD><TD class=xl24 style="BORDER-RIGHT: #e2e2e2; BORDER-TOP: #e2e2e2; BORDER-LEFT: #e2e2e2; BORDER-BOTTOM: #e2e2e2; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #e2e2e2; BORDER-TOP: #e2e2e2; BORDER-LEFT: #e2e2e2; BORDER-BOTTOM: #e2e2e2; BACKGROUND-COLOR: transparent">John</TD><TD style="BORDER-RIGHT: #e2e2e2; BORDER-TOP: #e2e2e2; BORDER-LEFT: #e2e2e2; BORDER-BOTTOM: #e2e2e2; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #e2e2e2; BORDER-TOP: #e2e2e2; BORDER-LEFT: #e2e2e2; BORDER-BOTTOM: #e2e2e2; BACKGROUND-COLOR: transparent">CDM</TD></TR></TBODY></TABLE>
The answer for these 3 columns is 5. Rows 4 and 5 are the same so are only counted once.
I can get the arrray to count unique items in the whole range and in a column but not for each row.
I have a feeling this is going to obvious but I can't work it out.
Thanks for helping
Partjob

 

[Shortmeister1]

One way of doing it would be as follows:

1. Copy your data to A2:C7 (A1:C1 is your headings)
2. D2 = Concatenate(A2,B2,C2) {Copy this row down)

3. E2 = COUNTIF(D2:$D$7,D2)
4. F3 = COUNTIF(D3:$D$7,D3)
5. F4 = COUNTIF(D4:$D$7,D4) and so on.
6. Lastly use COUNTIF($E$2:$E$7,"=1") as you want to "drop" anything that has appeared more than once.

HTH
Martin

 

[Lewiy]

Use a fourth column (D) with the formula:
=A2&B2&C2

Then a simple:
=COUNTIF($D$2:D2,D2)
copied down in column E will give you the number of times each row appears exactly the same in the rows above the current row, then you just need to count the number of 1's that appear in this column.

 

[Partjob]

Right I applied what Lewiy posted and joined all 3 columns in to one string in Column D and then used this array to count the unique values
=SUM(1/COUNTIF(D3:D8,D3:D8))
This does get the job done but is there a way to have an array llokat the range and do the same thing.
I am not completely against the helper column but would prefer a soluton without one.
Partjob

 

[Domenic]

Try...

=SUM(IF(FREQUENCY(IF(A2:A7&B2:B7&C2:C7<>"",MATCH("~"&A2:A7&B2:B7&C2:C7,A2:A7&B2:B7&C2:C7&"",0)),ROW(A2:A7)-ROW(A2)+1)>0,1))

...confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

 

[mikerickson]

This should do it.
=SUMPRODUCT(--(ROW(B1:B60) = MATCH(B1:B60&C1:C60, B1:B60&C1:C60, 0)))-1

The -1 is because the SUMPRODUCT is counting the blank rows as a "unique pair"

 

[Partjob]

Dominic
That has got it, it does not look as simple as I asumed it would be but works a treat.

Mikerickson, I'm sorry I could not get your soluton to work, your formula only refered to 2 columns. Not to worry though I do have a solution now.

thanks everyone for their time

Partjob

 

[mikerickson]

=sumproduct(--(row(b1:b60) = match(a1:a60&b1:b60&c1:c60, a1:a60&b1:b60&c1:c60, 0)))-1

 

[Matty]

Hello Domenic,

If you have the time, would you mind explaining how your formula works?

I have seen FREQUENCY used by a few MVPs on here, but I haven't managed to get my head around how it works.

Great solution, btw!

Thanks in advance.

Matty

 

[Domenic]

Hello Domenic,

If you have the time, would you mind explaining how your formula works?

I have seen FREQUENCY used by a few MVPs on here, but I haven't managed to get my head around how it works.

Great solution, btw!

Thanks in advance.

Matty

First, I'd suggest you read about FREQUENCY in Excel's help file. Then, let's assume that A2:A10 contains the following data...

A
B
C
D
A
B
C

C

The following formula, which needs to be confirmed with CONTROL+SHIFT+ENTER, will return 4...

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

Here's how it's evaluated...

=SUM(IF(FREQUENCY(IF({TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;TRUE},{1;2;3;4;1;2;3;8;3}),{1;2;3;4;5;6;7;8;9})>0,1))

=SUM(IF(FREQUENCY({1;2;3;4;1;2;3;FALSE;3},{1;2;3;4;5;6;7;8;9})>0,1))

=SUM(IF({2;2;3;1;0;0;0;0;0;0}>0,1))

=SUM(IF({TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE},1))

=SUM({1;1;1;1;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE})

=4

Hope this helps!