Excel: Summarise

Sometimes you need to summarise a bunch of cells based on some kind of priority. For instance, you might need to know which is the highest verification method across your verification program. Or you might need to know the highest risk factor. Doing this in Excel would require a very complicated formula with nested IFs. A VBA formula is a lot easier and far less error prone (just imagine checking every formula in every cell if there's 6 nested if statements!)

Public Function VMSummarise(ParamArray Text1() As Variant) As String
    Dim RangeArea As Variant
    Dim Cell As Range
    VMSummarise = ""
    Dim CONCAT As String
   
  For Each RangeArea In Text1
    If TypeName(RangeArea) = "Range" Then
      For Each Cell In RangeArea
        If Len(Cell.Value) <> 0 Then
          CONCAT = CONCAT & Cell.Value
        End If
      Next Cell
    Else
      'Text String was Entered
        CONCAT = CONCAT & RangeArea
    End If
  Next RangeArea
   
    If InStr(CONCAT, "Analysis") Then VMSummarise = "Analysis"
    If InStr(CONCAT, "Inspection") Then VMSummarise = "Inspection"
    If InStr(CONCAT, "Demonstration") Then VMSummarise = "Demonstration"
    If InStr(CONCAT, "Test") Then VMSummarise = "Test"
    If InStr(CONCAT, "Audit") Then VMSummarise = "Audit"
       
End Function

Example: