6 Replies Latest reply on Sep 21, 2016 2:09 PM by rajul Parekh

    Return highest value and all related fields

    rajul Parekh

      Hi -

       

      I have a list of members and jobs (with information including city, industry, # employees, etc.). I want to get an accurate list of all members currently in a city, not if they were previously there. This means, I need to pull each member's latest job number and all the associated account details. For example:

       

      Jane Doe first worked at Company X in Detroit (Job #1) and then moved to Company Y in Denver (Job #2). I don't want her to show up under both Detroit and Denver, but rather ONLY under Denver. I only want to see the company information associated with her Job #2.

       

      I can't do a quick filter however because some members' max job number is 1, some have had 2 jobs, and some have even had 3. So there needs to be a "max" formula or calculation.

       

      I tried writing the formula MAX([JOB NUM]) but it's still showing members under two cities rather than their latest.

       

      Any suggestions would be very helpful!

       

      Thanks so much,

       

      Rajul