Excel – Listing of different values

Task:

  1. In a cell in MS Excel a specific value is defined.
  2. A range of values is defined in a different place. We need to obtain values which are different from the value in the specific cell.
  3. Solution using VBA macro.

Related aspects:

  • In the defined area there can be more values which are different from the value in the defined cell.
  • Values like “0” and ” ” (empty cell) are ignored.
  • Values aren’t written into one cell because it’s needed to work with these different values separately.
  • A third added parameter – “order” – defines the rank of the specific value that is needed.

Solution:

  • Macro script in VBA:

Function diff_value(compare_cell As Range, searched_area As Range, order As Byte)

  Dim items() As Variant
  Dim compare_value As Variant
  Dim array_items As Object
  Dim cell As Range

  Set array_items = CreateObject("Scripting.Dictionary")
  compare_value = compare_cell.Cells(1, 1).Value

  For Each cell In searched_area
    If (cell.Value <> compare_value And cell.Value <> 0 And Trim(cell.Value) <> "") Then
      array_items(cell.Value) = cell.Value
    End If
  Next

  items = array_items.keys
  If (order > array_items.Count Or order < 1) Then
    diff_value = ""
  Else
    diff_value = items(order - 1)
  End If

End Function

  • For testing and better understanding you can download the file with an example of usage: diff_value.xlsm

Share the post "Excel – Listing of different values"

  • Facebook
  • Twitter