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)

Order#	Transport#	Date	Customer name	Carrier Name
1	T1	9/1/18	A	A
2	T2	9/1/18	B	B
3	T3	9/5/18	A	A
4	T4	9/8/18	A	C
5	T5	9/11/18	C	A
6	T6	9/15/18	B	B
7	T7	9/15/18	A	B
8	T8	9/16/18	A	A
9	T9	9/18/18	A	C

<tbody>
</tbody>

On tab two (Dash board page)

I have a drop down list of all customers

Customer		Rank	Carrier
A		1	A (Used 3 times)
		2	C (Used 2 times)
		3	B (Used 1 time)

<tbody>
</tbody>

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)