I am trying to find the MAX SF(Ship Frequencies) from VP (VendorPlant) based on the Route

[timotei]

Hi all,
I will appreciate some help with below issue I have.
I am trying to find the MAX SF(Ship Frequencies) from VP (VendorPlant) based on the Route
I have different SF on the same VP on the same route
eg. VP (A426MEQNXA) Route (TF155A) SF 21 and 27 - so MAX will be 27

but if I have more routes and one of them contain the text *SX* pick the max SF from that route
eg. VP (B40FAEQNXA) Route (SX162A with SF 22 and 28) and Route (TF158A with SF 21 and 27) - because one of the route contain *SX* MAX should be 28

I have attached my spreadsheet and in column D and E I tried two types of formulas but seem to not work.

thank you in advance

[AliGW]

Welcome to the forum.

Is this what you want (attached)?

I moved SF to the VALUES field in the PT and then chose MAX for its value calculation.

Administrative Note:

We would very much like to help you with your query, however the thread title does not really convey what your request is about. Tell us what you are trying to do, not how you think it should be done.

Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

(Note: this change is not optional. As you are new here I have done it for you this time.)

[Glenn Kennedy]

I2:
=IF(COUNTIFS(A:A,H2,B:B,"*SX*")>0,AGGREGATE(14,6,$C$2:$C$1214/(($A$2:$A$1214=H2)*(ISNUMBER(SEARCH("SX",$B$2:$B$1214)))),1),"No SX")

J2:
=IF(I2="No SX",AGGREGATE(14,6,$C$2:$C$1214/($A$2:$A$1214=H2),1),"SX route")

[AliGW]

Have either of the solutions offered helped at all? Some feedback would be appreciated. Thanks.

[timotei]

Hi Glenn,

thank you so much for your help. it's exactly what I was after.

Amazing.
thank you so much

[AliGW]

Administrative Note

... please don't ignore contributors to your thread - acknowledge all solutions offered, even if they don't meet your requirements. Thanks.

[Glenn Kennedy]

You're welcome.

---

It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.

Finally, if that takes care of your original question, please select "Thread Tools" from the menu link (just above the first post in the thread) and mark this thread as SOLVED.