2 Replies Latest reply on May 1, 2012 9:57 AM by reymundolopez

    Using Count Distinct with Data Blending

      I have two spreadsheets that show providers and clients.  My primary spreadsheet has a larger list of providers and clients on what I have called the metro area.  The secondary spreadsheet is more defined and narrows only a small part of the metro area (Inner City).  I have blended the sheets based on the provider id#'s to try and match those providers and clients listed on the secondary list to the providers and clients on the primary list.  The goal is to get a distinct count of providers and clients off the primary list based on what has been identified on the secondary list. 

       

      At the present I am able to get a distinct count of client but not a distinct count of providers on one table.  For example,

      I currently get the following calculation;                                                              I would like to get the calculation below.

      Current Calculation.pngDesired Calculation.png

      Any assistance anyone could provide would be greatly appreciated.  I have also attached my workbook to help with this issue.

       

      Thank you,

       

      Rey

        • 1. Re: Using Count Distinct with Data Blending
          Alex Kerin

          I don't understand the functional difference between the files - is Agency the same as Location - the two provider IDs (MET and IC) the same? Why is data in one file but not the other - that will help me understand what you are trying to do.

          • 2. Re: Using Count Distinct with Data Blending

            Hi Alex,

             

            Sorry this has taken me so long to respond but I have been out on sick leave. I will try and answer your quesions as you have them listed.

             

            1. is Agency the same as Location? No, Agency is the name of the actual agency and location is a geographical location where the agency is found in the city.

            2.  the two provider IDs (MET and IC) the same? Yes, Met -short for Metro will have all the providers listed in the metro area and IC (Inner City) is a subset of Met.

            3. Why is data in one file but not the other?  Good question, as the subsets (IC) are suppose to report what clients they have.  This is part of my dilemma as I am trying match on who what agency has reported to get a client count and who has not reported.  I hope this helps and let me know if you need further information.

             

            Again, I apologize for the delay.

             

            Rey