Counting the distance between similar values by rows using excel-vba/udf

Question

I am having trouble in counting the distance between values that are similar because there’s no function in excel that could achieve this and I deal with 2000 row of values. I would prefer excel-vba for this, a button perhaps that generates distances like in the example. array formulas lags the excel when there's too many values. Counting them 1 by 1 would be a waste of time. Please I want to have this done. I would truly appreciate it if some genius out there could pull this off.

Example bellow shows how far a specific value from the other:

Answer 1

you could try this

Option Explicit

Sub main()
    Dim cell As Range, f As Range
    Dim rowOffset As Long

    With Worksheets("gaps").Range("A2:F10") '<--| change this to your actual range of interest
        For Each cell In .SpecialCells(xlCellTypeConstants, xlNumbers)
            rowOffset = 1
            Set f = .Find(what:=cell, after:=cell, LookIn:=xlValues, lookat:=xlWhole, searchdirection:=xlPrevious)
            If Not f Is Nothing And f.Row <= cell.Row Then rowOffset = cell.Row - f.Row + 1
            cell.offset(, .Columns.Count + 1) = rowOffset '<--| the "+1" offset results range one column away from values range: adjust it as per your needs
        Next cell
    End With
End Sub

tested on your "Values" it gives back the same "Value row gaps" except cell "K4": I hope it's a miscount on your part...

---

should you ever need to display output in the same "relative" position but on another worksheet (say: "sheet2") then just change

cell.offset(, .Columns.Count + 1) = rowOffset

to

Worksheets("sheet2").Range(cell.offset(, .Columns.Count + 1).Address) = rowOffset