Auto select by most Popular

[poikl]

I would greatly appreciate if you can please HELP me, and am hoping this can be done.
I'd like that when I put in ColA the product#,Excel, will Scan the Previous Shipping mode of THAT Product and Select as Default, the MOST Used Shipper and SHOW FEDEX in Row9 ColB, based on the majority of prior Shippments.
If THIS can't be done, would you kindly show me how it can Scan, Calculate from Page 2 which contains entire PAST Year History? (Page 1 is Current).
Thank you so much in advance.
|||A|||||||||B
----------------------
1||Product#||Shipping
____________________
2||A123||||||Fedex
3||CL01||||||Fedex
4||A096||||||USPS
5||A123||||||DHL
6||4322||||||Usps
7||A123||||||Fedex
8||A096||||||UPS
9||A123

 

[poikl]

Armondo, It's really so nice of you to help me, Thank you. But I still get #N/A, because of the Second test "E1876<>"Internal",E1876<>"Private".
It However DID Resolve when there were BLANKS in ColA