+ Reply to Thread
Results 1 to 4 of 4

Count unique occurrences: Pivot and Index/Match – how to make it dynamic?!

  1. #1
    Forum Contributor
    Join Date
    04-30-2014
    Location
    Vancouver, Canada
    MS-Off Ver
    Excel 2010
    Posts
    113

    Count unique occurrences: Pivot and Index/Match – how to make it dynamic?!

    I am hoping you guys can help. I've been stuck with formulas to count the number of unique occurrences that are way too resource intensive. So far the closest solution I've found is using pivot tables, but I can’t seem to get it completely right.

    I am using this formula to pull unique counts from a pivot table:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    I just can't figure out how to make its references to the pivot table dynamic!?
    I don't know why, it looks like it should be simple enough…

    Can you please help?

    Here is more information about my set up. The sample file is also attached here (Attachment: uniquecount-corrected-3.xlsx)

    This count of unique occurrences is a small part of a large macro.

    The pivot table is set up this way:
    Row labels: names
    Column labels: days
    Values: count of days

    The data that is processed by my macro looks like this:
    Column 1: names
    Column 2: date range (FROM)
    Column 3: date range (TO)
    Column 4: unique count formula (as below and same as above)
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    How can I replace these with dynamic named ranges:
    '$5:$9'
    '$A$5:$A$9'
    '$4:$4'

    I have tried but I either obtained '#VALUE' or 'REF!' results.

    Here is my dynamic range formula for:
    '$4:$'4 ->
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    '$A$5:$A$9' ->
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    '$5:$9' -> ???
    I tried this but it seems wrong ->
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,899

    Re: Count unique occurrences: Pivot and Index/Match – how to make it dynamic?!

    2 suggestions...

    1. try not using full row/column ranges
    2. upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Forum Contributor
    Join Date
    04-30-2014
    Location
    Vancouver, Canada
    MS-Off Ver
    Excel 2010
    Posts
    113

    Re: Count unique occurrences: Pivot and Index/Match – how to make it dynamic?!

    Thanks Ford.

    Your 1st suggestion is precisely why I came here for help. I am trying to use dynamic ranges instead of absolute ranges that cover entire rows and columns.

    As for your second suggestion, I'm not sure why it came out as a picture. I clearly tried to upload an excel file. I am attaching it again here: uniquecount3.xlsx.

    Let me know what you think.

  4. #4
    Forum Contributor
    Join Date
    04-30-2014
    Location
    Vancouver, Canada
    MS-Off Ver
    Excel 2010
    Posts
    113

    Re: Count unique occurrences: Pivot and Index/Match – how to make it dynamic?!

    Any thoughts?

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Make an index and match function dynamic
    By concatch in forum Excel General
    Replies: 3
    Last Post: 08-18-2014, 02:35 PM
  2. Count unique occurrences with sumproduct
    By dana26 in forum Excel Formulas & Functions
    Replies: 18
    Last Post: 02-03-2013, 11:55 PM
  3. Replies: 2
    Last Post: 11-05-2011, 03:26 PM
  4. Count unique occurrences with criteria
    By Alaina Readman in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 11-01-2007, 04:41 PM
  5. [SOLVED] Count unique occurrences of name
    By jhicsupt in forum Excel General
    Replies: 4
    Last Post: 10-05-2005, 01:05 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1