• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

The equation to obtain a constant value from several values for the same vehicle

Hany ali

Active Member
hello my Dear ,I want You Helping in this problem ,i Need any Formula to get the Same Result in Coulmn B ,in the Same Row for Number in Column C ...AS Following :
**If i have multiple Regions in Column A for the same Vehicle Record Nr from Column D , You should choose the farthest area ...Foe Example in my file for this Vehicle Record 460461 .it should be to choose between (MAK,SAF)I WILL CHOOSE "SAF"Because it's far destination
In general, it is required to find the farthest place in the same Vehicle ,if i have (SAF,MAK,SAHL,HUR)..I CHOOSE SAF
*In anothe case if i have (MAK,SAHL,HUR)..I CHOOSE MAK
*If i have (SAHL,HUR)In the same Vehicle .. i will choose SAHL
*If i have in the same Vehicle just (HUR).. I will choose it & This is generally for the entire area of the same car ,I mean else if i found onlt (SAHL OR MAK OR SAF) I will choose the region
thanks alot for all
 

Attachments

  • 1.png
    1.png
    43.5 KB · Views: 9
  • Region.xls
    64.5 KB · Views: 4
Please try

=IF(D2=D1,"",INDEX({"SAF","MAK","SAHL","HUR"},MIN(MATCH(A2:INDEX(A2:A9,MATCH(2,1/(D2:D9=D2))),{"SAF","MAK","SAHL","HUR"},))))

Ctrl+Shift+Enter
 

Attachments

  • Region.xls
    84 KB · Views: 8
Or, In B2, non-array formula copied down :

=IF(D2=D1,"",LOOKUP(1,0/MATCH({"HUR";"SAHL";"MAK";"SAF"},OFFSET(A2,,,COUNTIF(D:D,D2)),0),{"HUR";"SAHL";"MAK";"SAF"}))

Regards
 
Another one, with array calculation but no need CTRL+Shift+Enter
B2

=IF(D2=D1,"",INDEX({"HUR","SAHL","MAK","SAF"},MAX(INDEX(MATCH(A2:A9,{"HUR","SAHL","MAK","SAF",0},)*(D2:D9=D2),))))
 
Back
Top