Most reoccurring value that matches a criteria

[Mrbellav]

I am trying to find the carriers used most for each specific customer.

I have data on two tabs.

Tab 1 (Sales Orders)

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]Order#[/TD]
[TD] Transport#[/TD]
[TD]Date[/TD]
[TD]Customer name[/TD]
[TD]Carrier Name[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]T1[/TD]
[TD]9/1/18[/TD]
[TD]A[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]T2[/TD]
[TD]9/1/18[/TD]
[TD]B[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]T3[/TD]
[TD]9/5/18[/TD]
[TD]A[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]T4[/TD]
[TD]9/8/18[/TD]
[TD]A[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]T5[/TD]
[TD]9/11/18[/TD]
[TD]C[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]T6[/TD]
[TD]9/15/18[/TD]
[TD]B[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]T7[/TD]
[TD]9/15/18[/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]T8[/TD]
[TD]9/16/18[/TD]
[TD]A[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]T9[/TD]
[TD]9/18/18[/TD]
[TD]A[/TD]
[TD]C[/TD]
[/TR]
</tbody>[/TABLE]



On tab two (Dash board page)

I have a drop down list of all customers

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Customer[/TD]
[TD][/TD]
[TD]Rank[/TD]
[TD]Carrier[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD][/TD]
[TD]1[/TD]
[TD]A (Used 3 times)[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]2[/TD]
[TD]C (Used 2 times)[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]3[/TD]
[TD]B (Used 1 time)[/TD]
[/TR]
</tbody>[/TABLE]



So if I change the drop down to Customer B, my top carriers would be..

1 b
2
3

Let me know what I can try to do to figure this out.

 

[KUYJS]

Follow below steps:
1. Use concatenate with customer name and carrier in column F
2. Use countif(F:F,F2) and drag it down in column G
3. Use Rank formula in column H with exact match
4. Now in dashboard use combination of Hlookup and Match as below:

Rank 1
Hlookup("Carrier",E:E,Match(1,H:H,0),0)
Rank 2
Hlookup("Carrier",E:E,Match(2,H:H,0),0)
Rank 3
Hlookup("Carrier",E:E,Match(3,H:H,0),0)