Count the # of unique items in a column, excluding blanks

Excel Formula to count the # of unique items in a column, excluding blanks

 

=SUMPRODUCT((B21:B1421<>"")*(1/COUNTIF(B21:B1421,B21:B1421 &"")))

Excel "IF~OR~AND" Formula

Excel Formula to check if B1 or C1 or D1 contain a value of "1" AND if the E1 = "1".  If TRUE, then A1 = "1", otherwise, A1 = "0".

Place the following formula in cell A1:

=IF(AND(OR($B$1=1,$C$1=1,$D$1=1),$E$1=1),1,0)

Excel - Search Cell for Matching Value

Excel Formula to search a cell's contents for the value within quotes and determine if it contains a match.  If True, it returns the cell's contents.  If False, it leaves it blank.

=IF(ISNUMBER(SEARCH("Ticket",A2)),A2,"")     

Parse Cell String Contents (VBA)

Thanks to my buddy Smitty for help on this one...

The VBA Module below will search a column of data (in this case:  Col G, Row 2 down) and take a cell's contents and convert it to a string.  It will then search the String for 2 key words (specified in the code below) and return the text value that sits 'in between'.  If the code encounters a duplicate within the cell (aka: string), it will ignore it and only return the unique value(s) between the 2 key words.  With this specific routine, it finds the instance(s) of "Component: " and "; Outcome" and counts backwards 11 spaces to return the value in between.  When re-using this code, please remember to modify the "Range" values, Component / Outcome key words (in quotes), and finally the # of characters with which to Start and End the parsed value.



Sub GetValues()

    Dim rngCell As Range
    Dim strName As String
    Dim Component As Integer
    Dim Outcome As Integer
    Dim CompleteResult As String
    Dim CurrentResult As String
    Dim ComponentCnt As Integer
    Dim RowCnt As Integer

    RowCnt = Range("G1").End(xlDown).Row
    Range("H2:H" + Trim(str(RowCnt - 1))).Select
    Selection.ClearContents

    For Each rngCell In Range("G2", Range("G1").End(xlDown))
        CompleteResult = ""
        strName = rngCell.Value

' Change the keyword values below to accomodate your "in between" criteria.
        Component = InStr(1, strName, "Component: ")
        Outcome = InStr(1, strName, "; Outcome")
        While Component > 0
            If CompleteResult <> "" Then
                CompleteResult = CompleteResult + vbLf
            End If
           
            ' Prevent Duplicates
            ' The number values in the line immediately following = spaces left and right to start the string start and finish points.
            CurrentResult = Mid(strName, Component + 11, Outcome - Component - 11)
            If InStr(CompleteResult, CurrentResult) = 0 Then
                CompleteResult = CompleteResult + CurrentResult
            End If
           
            Component = InStr(Outcome + 1, strName, "Component: ")
            Outcome = InStr(Outcome + 1, strName, "; Outcome")
        Wend
       
        rngCell.Offset(0, 1).Value = CompleteResult
    Next rngCell

    MsgBox ("DONE")
End Sub

Excel Sort Function

The Excel Formula below is an array formula that will sort a column and bring the contents to the top of the list.  It’s particularly useful if you have blanks in your data that’s intermittently placed throughout a column.  If you want the data to display at the bottom (instead of the top of the list), just swap out the word “LARGE” below with “SMALL”.  Enter the formula and drag down adjacent to the list that you want evaluated.  Remember to press SHIFT + CTRL + ENTER on the first cell to make it an array formula…then drag down from there. 

{=INDEX($L$131:$L$190,MATCH(LARGE(COUNTIF($L$131:$L$190,"<"&$L$131:$L$190),ROW(1:1)),COUNTIF($L$131:$L$190,"<"&$L$131:$L$190),0))}