Max Occurances

[dgaller]

Max Occurances
<HR style="BACKGROUND-COLOR: #ebebeb; COLOR: #ebebeb" SIZE=1><!-- / icon and title --><!-- message --><!-- BEGIN TEMPLATE: ad_showthread_firstpost_start --><!-- END TEMPLATE: ad_showthread_firstpost_start -->I have a pivot table that has 3 columns the first is state the second is vendor and the third is a count of locations the vendor has in that state. I would like to then just show only the vendor with the greated number of locations in each state?

Example:

CT Vendor1 10
CT Vendor2 5
IL Vendor1 4
Il Vendor2 20

ETC.

I want to either have a formula or pivot to narrow the list like below.

CT Vendor1 10
IL Vendor2 20
Etc
<!-- / message --><!-- BEGIN TEMPLATE: ad_showthread_firstpost_sig --><!-- END TEMPLATE: ad_showthread_firstpost_sig -->

<!-- controls -->

 

[Robert Mika]

If that will be any help for you.

If your data are in A2:C5:

From column A copy and paste only unique to column E starts from E2 then in F1 copy down:

=INDEX($A$2:$C$5,MATCH(E2&MAX(IF($A$2:$A$5=E2,$C$2:$C$5)),$A$2:$A$5&$C$2:$C$5,0),2)

in G1:
=INDEX($A$2:$C$5,MATCH(E2&F2,$A$2:$A$5&$B$2:$B$5,0),3)

Both Arrays formulas(confirm CTRL+SHIFT+ENTER)

 

[dgaller]

Thanks for your assistance. I am having a hard time getting this to work. From what I understand I need to past the array formula into cell F1 and copy it down?

 

[Robert Mika]

Exactly.
Copy, paste, confirmed with CTRL+SHIFT+ENTER.
What kind of troble are you having?

 

[dgaller]

I am getting #N/A error. I even tried just using exactly what I used for the example.

 

[Robert Mika]

Example:

https://rapidshare.com/files/2338482813/Book1.xlsx