2 Replies Latest reply: Jul 5, 2018 12:56 PM by Quy Nguyen RSS

    Count if data is present in any of n fields

    Alisdair Wigham

      Hi,

      I have some data where people have qualifications listed in two fields:

       

      Name
      Qualification 1Qualification 2Qualification ......Qualification N
      AliceAB...C
      BrianBC...X
      CharlesCA...Z
      DaveAD...W
      EmilyDF...A
      FrankFX...C
      GaryFZ...J
      HarryGW...K
      IvanWS...R
      JamesHB...Q

       

      Qualifications are added from a set list which is the same regardless of if the data is in "Qualification 1, 2 or N". These fields are only there as qaulifications are added as they are gained in time.

       

      I need to return a count of "how many people have each qualification?", which in the above example for W is 3. (Ivan, Harry & Dave) or for A is 4 (Alice, Charles, Dave & Emily).

       

      Data in each column is unique, ie Emily cannot have qualifications A,B,...,A. (or using this data example, not more than 26 qualifications if the pick list for quals is A to Z).

       

      Help welcome please!