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]

Someone, Please help me,Re: Default- Most Popular

I am sorry for asking again, but you always have come thru for me in the past and here nobody replied and I really would appreciate since it's getting urgent. Thank you
Sheet1 is my current sheet where data is enetered daily as the product is Shipped. Product Code in ColA and Shipper in ColB.
Is it possible to put a Function in ColB that it would enter in DEFAULT the Shipper for THAT Product based on the MOST previous Shipper for the SAME product?
So in Row9 CoB it would automaticcaly enter FEDEX (this based since Fedex was used for #A123 TWICE(Rows2&7), while DHL was only used Once (Row5)?
If this isn't workable, would you be able to please provide me with a Function that would base the similiar Calculation from Sheet2 which contains Last year's History report(below)?
Sheet1 - Current
|||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



Sheet 2 (History)
|||A|||||||||B
----------------------
1||Product#||Shipping
____________________
2||F763||||||Fedex
3||CL01||||||Fedex
4||F763||||||USPS
5||A123||||||DHL
6||CL01||||||Fedex
7||A123||||||Fedex
8||A096||||||UPS
9||A123||||||Fedex
10||CL01||||||Fedex
11||A096||||||USPS
12||A123||||||DHL
13||F763||||||Fedex
14||A123||||||Fedex
15||A096||||||UPS
16||A123||||||Fedex
17||CL01||||||Fedex
18||A096||||||USPS
19||A123||||||DHL
20||F763||||||Ups
21||A123||||||Fedex
22||A096||||||UPS
23||A123||||||Fedex
24||CL01||||||Fedex
25||A096||||||USPS
26||A123||||||UPS
27||4322||||||Usps
28||A123||||||Fedex
29||A096||||||UPS

 

[poikl]

Please help!

 

[Armando Montes]

Try this in B9:
=INDEX(B2:B8,MODE(IF(A2:A8=A10,MATCH(B2:B8,B2:B8,0))))
Array enter: Control+Shift+Enter

 

[poikl]

Armondo, Thank you for answering, BUT I only get #N/A after putting in your formula with Ctrl,Shft,Ent.
Please see if you can correct this,if it's not too a big bother. Thank you

 

[Armando Montes]

Sorry, change A10 to A9

 

[poikl]

Armondo, First, you have no idea how GREATFULL I am for you replying.
But, I had tried that on my own, before I sent the reply and it still showed #N/A.
Did it work when you tried it?
What am I doing wrong?

 

[poikl]

Armondo,
So sorry, I tried it again and it WORKS!
Thank you very much

 

[poikl]

Would you please help me make this Modification work (to Avoid #N/A).
I had entered w/ Ctrl Shft Ent, but its worded wrong andwill not accept
=IF(and(OR(C1876="",(E1876<>"Internal",E1876<>"Private"),"")),INDEX(M$2:M1875,MODE(IF(C$2:C1875=C1876,MATCH(M$2:M11875,M$2:M1875,0)))))

 

[Armando Montes]

Maybe like this:
=IF(COUNTIF(A2:A8,A10),INDEX(B2:B8,MODE(IF(A2:A8=A10,MATCH(B2:B8,B2:B8,0)))),"no")
Instead of "no" you can use any other word or simply ""
Adjust ranges tu suit.