Return Unique Values with count

[jeffreybrown]

I'm using a macro that works fine to return a unique list from column C based on column D having the word "Major Task". Right now, I also have a formula applied to column H giving the count of "Sub Tasks" under each one of the "Major Tasks".

How can I instead get the result found in column I? Basically the count just to the right of the "Major Task".

Can this be done within the FilterUnique macro or is there another formula I can use? This is done for feeding the DV in M10 and M12 which is used on a larger scale for charting.

[sintek]

As a starter...

Please Login or Register  to view this content.

[jeffreybrown]

I do believe that is going to work just fine. Thank you very much.

[sintek]

.........................................
Thanks.gif

[jeffreybrown]

Hi Sintek,

What if the Major Tasks were not ordinal as in Major Task 1,2,3, etc, but instead, they are like...

C8 = Produces classified letters
C16 = Maintains database servers
C22 = Delivers classified letters

...and so with this structure, can I get the count between C8 and C16, C16 and C22, and then what's left?

[jeffreybrown]

Okay, not sure if this is a good way to go about it, but I modified to this. The Sub Tasks can also be actually task that do not start with Sub.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[sintek]

apologies...had to put the little one down

[jeffreybrown]

No worries, I just had to run out to pick up my grandson.

[sintek]

So are the Headers now gonna be "Major Task*" and the sub headers "Sub*" or something else...

[sintek]

What about....

Please Login or Register  to view this content.

[jeffreybrown]

Sorry about that. Here is an updated file which I should have posted the first time. Rookie mistake.

[sintek]

K...See amended in Post 10

[jeffreybrown]

Hi Sintek,

With a couple of tweaks, this works. Thanks again.

Please Login or Register  to view this content.

[jindon]

Please Login or Register  to view this content.

[jeffreybrown]

Hi Jindon and thank you for this solution.

Column D is the column with "Major Task" and so the title for each "Major Task" I would like to return is from column C.

Please Login or Register  to view this content.

Easy enough to change the range to reference where to find "Major Task", but how to have the code offset that range by -1 columns?

[jindon]

Ahh, I see...

Please Login or Register  to view this content.

[jeffreybrown]

That does it. Thank you Jindon.

[jindon]

You are welcome and thanks for the rep.

[sintek]

Great stuff jindon...Tx for the awesome code...love learning from you...