Finding how many blocks of unique data exist in a sheet

[akedm]

UNIQUE works to find how many unique items are in a list.
FILTER can help this by further narrowing a unique list based on if this equals that, as an example.
COUNTA relays the number of results.

I'm trying to use these to find how many unique blocks of data fall within a filtered service date. But I only get so far using the UNIQUE(FILTER(...)) formula I'm used to. It's like I need to use a version of UNIQUE(FILTER(UNIQUE(...))) in order to narrow down my results further to unique blocks. But I dont think UNIQUE(FILTER(UNIQUE(...))) is a thing. So maybe I need a helper column.

I've attached an animation and a sample sheet for your use. I'm using autofilter in the example only to demonstrate a result I see, that I need Excel to "see".

(Sorry for the misspellings)

Animation.gif

[FlameRetired]

It's clear you have a workbook with sample data in front of you. Please upload a sample workbook. The instructions are in the 'gold' banner at the top of the page.

Thanks.

[akedm]

hmmm. thought I had.

Trying again. And a better annotated screenshot too...

[FlameRetired]

Here's one way.

Please Login or Register  to view this content.

[FlameRetired]

Another way.

Please Login or Register  to view this content.

[sandy666]

something like this?

Service Date	Sum
14/10/2020	3
06/10/2020	1
30/09/2020	1
22/09/2020	2
18/09/2020	2
16/09/2020	2
11/09/2020	2
09/09/2020	2
04/09/2020	2
31/08/2020	2
26/08/2020	1

[akedm]

@FlameRetired

OMG

=SUMPRODUCT((D6:D160=D2)/COUNTIFS(C6:C160,C6:C160,D6:D160,D6:D160,E6:E160,E6:E160))

That is amazing.

It took me a good while to wrap my head around this, and even now I can barely explain it to anyone. And how you thought that would work - what a crazy direction. But it works beautifully.

To isolate TRUE values in an array so each can be divided into a number of equal percentages of 1, then added to get a total. Like I said I can barely explain it. But ... bravo! SUMPRODUCT is a formula I really need to look at more.

[akedm]

@Sandy

Thanks. I like the UNIQUE + the formula summary you provide. It's so satisfying when a ton of data is drilled down into a simple summary.

[sandy666]

I like the UNIQUE + the formula summary you provide. It's so satisfying when a ton of data is drilled down into a simple summary.

this is Power Query not a formula

[FlameRetired]

Glad to help. Good to hear you learned something helpful here. That's music to our ears.

Thank you for the feedback, added rep and marking your thread Solved.