2 Replies Latest reply on Jul 20, 2018 7:30 AM by Doug Spavlik

    Creating a unique list

    Doug Spavlik

      I have been struggling with this for a few days.  I have a table were 3 columns are job roles.  In this case they are UserA, UserB and  User C.  I need to create a calculated field that would search through each column and generate a list.  I am pretty close but what I have throws off the count on the last part of ElseIF. 

       

      AllUniqueUsers Calculated Field:

      If NOT ISNULL([UserA]) Then [UserA]

        ELSEIF NOT ISNULL([UserB]) Then [UserB]

        ELSEIF NOT ISNULL([UserC]) Then [UserC]

      END

       

      Any user can exist in A B or C. The Task is to an "AllUniqueUsers" field and report their counts in each column (A, B, C).  Sample data below.  Any help is greatly appreciated!

       

      FileIDUserAUser BUser CAllUniqueUsers
      23VA00768Geddy Lee Geddy Lee
      23VA01253Geddy Lee Neil Peart
      23VA02458Geddy LeeGeddy Lee Alex Lifeson
      23VA02111Neil Peart Ronnie Dio
      23ND02459Alex LifesonRobert Plant Robert Plant
      23VA02713Steve Howe Al Sterart
      23VA02126Geddy Lee Pink
      23VA02799Geddy Lee Jimmy Page
      23VA02900Geddy Lee Alex LifesonSteve Howe
      23VA01258Robert PlantAl SterartAl SterartJennifer Brown
      23VA00211 PinkPinkTina Turner
      24VA03724 Jimmy PageJohn BohnamPhil Collins
      22VA03726 Steve HoweSteve HoweFlea
      23VA00704 Neil PeartPhil CollinsEric Clapton
      22VA04701 PinkPinkStanly Clark