Count unique values, with a twist

[brainiack189]

Hi all,

I'm trying to use a formula to count the number of unique "blocks" of values. Below is an example.

[TABLE="width: 300"]
<tbody>[TR]
[TD]Row #[/TD]
[TD]Column 1[/TD]
[TD]Column 2[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]2[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]2[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]2[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]2[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]2[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]3[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]3[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]3[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]3[/TD]
[TD]B[/TD]
[/TR]
</tbody>[/TABLE]

In this example there are 3 blocks: Block one is rows 2-4, Block two is rows 5-6, Block 3 is rows 7-9. Basically whenever Columns 1 and 2 match and have more than 1 match counts as a block. So rows 1 and 10 do not count as blocks because there is no other row that matches them.

Is there a way to use a formula to do this? I have to do this for about 300,000 rows and it'd be nice to have the formula refresh when the number of rows changes. Thank you!

 

[gruntingmonkey]

So I have found a way of doing this I think.

In column 3 use the formula: =CONCATENATE(A1,B1)
In column 4 use the formula: =MATCH(C1,$C$1:$C$10,0)

Then select the cells in Column 4 and conditional format only duplicate values. This will highlight all of the blocks you are on about in one colour. The different blocks will be identifiable as they will have different numbers (start row).

Remember that if you include a new row of data, you will need to make sure the formula is copied.