Count how many IDs have used Different providers:

[Leicester City Fox]

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:

ID	Providers
1	A
2	B
3	C
1	E
2	G
1	A
1	Z

Looking for Result:

ID	Count of different providers used
1	3
2	2
3	1
4	0

Thanks The Leicester Fox

 

[Chihiro]

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.

 

[Chihiro]

If you have access to UNIQUE function (Office 365 Targeted).

You can use...
=COUNTA(UNIQUE(IF(Table1[ID]=[@ID],Table1[Providers])))-1
Where ID 1 to 4 is listed in separate table.

For more traditional approach. See links.
https://www.extendoffice.com/documents/excel/3403-excel-count-unique-based-on-another-column.html
https://exceljet.net/formula/count-unique-text-values-with-criteria

 

[bosco_yip]

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

 

[Fluff13]

Cross posted

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 might be the best and...

www.mrexcel.com

 

[vletm]

Leicester City Fox
You should reread Forum Rules:

Site Rules - New Users - Please Read

Hi all, Welcome to the Chandoo.org Forums. Posting Rules & Etiquette The Chandoo.org Forums is a collaborative and happy place to learn and expand your Excel knowledge. The Chandoo.org Forums consist of several Sub-Forums based on the type of question/area of Excel you are interested in...

chandoo.org

and
after that
... follow those, please.