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.

Row #	Column 1	Column 2
1	1	A
2	2	B
3	2	B
4	2	B
5	2	C
6	2	C
7	3	A
8	3	A
9	3	A
10	3	B

<tbody>
</tbody>

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)

	A​
1​	My Block Count​
2​	3​
3​

<tbody>
</tbody>

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)