• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Count how many IDs have used Different providers:

Hi There

I have a list of Id numbers in a Column they appear several times these Ids may use different companies from list of 30 providers. I want to count how many times a unique id has used a different company and count the, the list I have is very long.

Any ideas what the best and simplest way might be to do this?

Small Example sample:
IDProviders
1A
2B
3C
1E
2G
1A
1Z




Looking for Result:
IDCount of different providers used
13
22
31
40


Thanks The Leicester Fox
 
If you have Excel 2013 and up... I'd just use OLAP based Pivot.

Convert your data to table. And also create another table listing all unique IDs.

Go to data tab. Create relationship between two tables. Using data table's ID as foreign and ID lists ID column as primary.

Then insert Pivot Table -> From Data Model.

Add ID list's ID column as row label. Add Count of Providers as Value. Then click on Count of Provider and go to Value Field Settings.
Choose Distinct Count.

By default Pivot Table will hide ID# 4 as there is no data. But if you want to show it, you can set pivot option to "Show items with no data on rows".

See attached sample.
 

Attachments

  • DataRelationship_DistinctCount.xlsx
    115.6 KB · Views: 4
64857

In E2, array (CSE) formula copied down :

=COUNT(1/(MATCH(D2&B$2:B$8,A$2:A$8&B$2:B$8,0)=ROW($1:$7)))

Regards
Bosco
 
Cross posted
 
Leicester City Fox
You should reread Forum Rules:
and
after that
... follow those, please.
 
Back
Top