doughnut Plot by selecting one to many relationship in chart

Dear Excel Experts !

I need some help to plot some data I have , and wondering if somebody can give some idea about it .

The data looks like as below .

Retailer Company name Value in %
A AX 35
AY 25
AZ 25
AA 15
B AB 40
AY 25
AZ 10
AD 12
AI 13


What I want is following :
I want to select for example in dropdown menu , with all retailers name and then let say Select B , so the doughnut chat uses the values from this table and show all 5 companies with their % value . I know about offset function but I am not sure if it will work for such a table as for 1 retailer there could be more values . The number of companies for each retailer is different so for some retailer it is 4 , for some it is 5 , for some it is 2 or for some it is 6 .. I hope there is a way to plot this kind of table for each retailer in Excel .

OPTIONAL :: The % values are changes with date after a year or two .. is it possible to add some kind of criteria or filter such that this table is automatically updated with latest date from another sheet or workbook where the raw data with dates is being saved.


Thanks for the help in advance

Thanks Andy !