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="class: grid, 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!

 

[Fazza]

I think a formula will be slow; and a query faster. With your table named 'MyTable', use query

SELECT COUNT(*) AS [My Block Count]
FROM (SELECT  [Column 1],[Column 2], COUNT(*) AS MyCount
FROM MyTable
GROUP BY [Column 1],[Column 2]
HAVING COUNT(*) > 1)

Refresh as required.

 

[FDibbins]

duplicate thread here
Count unique values, with a twist

 

[Marcelo Branco]

Fazza,

I tried your suggestion. It worked but i had to do a small adjustment since table MyTable was not visible in Microsoft Query even with Show System Tables checked (options). I think a named range would be visible but it does not automatically resize with new data.

Assuming:
File named TestQuery.xlsx in C:\Temp
The table in Sheet1
this worked for me

SELECT COUNT(*) AS [My Block Count]
FROM (SELECT  [Column 1],[Column 2], COUNT(*) AS MyCount
[COLOR=#0000ff]FROM `C:\Temp\TestQuery.xlsx`.`Sheet1$` `Sheet1$`[/COLOR]
GROUP BY [Column 1],[Column 2]
HAVING COUNT(*) > 1)

Query Result (new Workbook)

[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

A​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD]

My Block Count​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD]

3​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


M.

 

[Fazza]

Yes, Marcelo

using a sheet name is better - as you explain, all rows will be picked up and this doesn't happen when rows vary and the basic named range is used.

note you should be able to use
FROM [Sheet1$]

instead of
FROM `C:\Temp\TestQuery.xlsx`.`Sheet1$` `Sheet1$`

I find it easier to explain in forum posts to use defined names but the worksheet name is better - so long as it doesn't change

 

[Marcelo Branco]

Yes, it worked
FROM [Sheet1$]
much simpler

Thank you

M.

 

[Fazza]

some of what I posted is unnecessary. can shorten to

SELECT COUNT(*) AS [My Block Count]
FROM (SELECT [Column 1],[Column 2]
FROM MyTable
GROUP BY [Column 1],[Column 2]
HAVING COUNT(*) > 1)