+ Reply to Thread
Results 1 to 3 of 3

Multiple unique counts depending on other columns

  1. #1
    Registered User
    Join Date
    03-20-2012
    Location
    England
    MS-Off Ver
    Excel 2007
    Posts
    27

    Multiple unique counts depending on other columns

    Hi everyone,

    This is a question about automating a process, rather than an individual formula or macro problem. I hope it’s not too vague.

    Every week I create a report which shows ID’s involved in incidents occurring in certain categories ( see attached spreadsheet for clarification).

    I receive the raw data in the format shown on the “Data “ tab. To create the report shown in columns A,B,C,D on the “Summary” tab I filter on category, copying all of the incidents to the “Unique list” then use the advanced filter to show only unique values. I paste these into column B of the “Summary” tab.

    I then go back to the “Data” tab, filter on category and then filter on incident and copy all the ID’s to column H of the “Summary” tab. Using the formulas in column I & J I get the Count and unique count and I copy these values into the report in columns C & D. Then I repeat the whole process again for the next unique count.

    The formula in Column I is =COUNT(N:N) for count
    The formula in Column J is =SUM(IF(FREQUENCY(N:N,N:N)>0,1)) for unique count

    This takes a long time because there are lots of incident types, categories and hundreds of lines. There must be a more effective way of doing this but I can’t work it out. The criteria is only that I need to keep the “Data” tab as is and produce the report in columns A to D in the “Summary” tab.

    I am using excel 2007. Any help is greatly appreciated.

    Regards,

    Automation test.xlsx

    Ger

  2. #2
    Valued Forum Contributor
    Join Date
    07-17-2005
    Location
    Abergavenny, Wales, UK
    MS-Off Ver
    XL2003, XL2007, XL2010, XL2013, XL2016
    Posts
    608

    Re: Multiple unique counts depending on other columns

    Hi Ger

    Provided you can accept adding another column to your source data, then this can easily be solved with a Pivot Table (see attached workbook).
    Note that this has been saved as an xlsm file, because I have included some event code on the Report sheet so that the Pivot table report refreshes automatically whenever you go to that sheet.

    I made the data into a Table.
    The dimensions of this table will automatically alter as you add more rows of data in columns A,B,C and the formula will automatically copy down column D
    The formula in column D is
    =IF(COUNTIFS($A$2:$A2,A2,$B$2:$B2,B2,$C$2:$C2,C2)>1,"",COUNTIFS($A$2:$A2,A2,$B$2:$B2,B2,$C$2:$C2,C2))
    which provided the unique count of incidents

    The 2 Pivot Tables on the Report sheet then automatically produce the results you are looking for.

    As you say that you have to have your report in columns A:D of your sheet, I have repeated your table on that sheet in row 16, but collect the data from the Pivot Table using the GetPivotData function.
    If you add more Categories and more incidents to your Summary table, just drag the formulae in columns C and D down
    Attached Files Attached Files
    --
    Regards
    Roger Govier
    Microsoft Excel MVP

  3. #3
    Registered User
    Join Date
    03-20-2012
    Location
    England
    MS-Off Ver
    Excel 2007
    Posts
    27

    Re: Multiple unique counts depending on other columns

    Thank you so so much Roger, that is a huge help!

    Regards,

    Ger

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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