0

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: enter image description here

Egie Boy Aguspina
  • 29
  • 7
  • Possible duplicate of [Excel formula in counting distance between a specific value in rows](http://stackoverflow.com/questions/36789382/excel-formula-in-counting-distance-between-a-specific-value-in-rows) – RGA Jul 11 '16 at 12:20
  • @RGA i tried this one sir doesn't work on multiple duplicates of 2000 rows and it lags when it goes beyond 1000 rows... i need something like a vba pls... – Egie Boy Aguspina Jul 11 '16 at 12:24

1 Answers1

0

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
user3598756
  • 28,893
  • 4
  • 18
  • 28
  • Thank us so much dude ill tell my friend to rate all ur answers in ur profile.. thats the only think i can do for u now.. – Egie Boy Aguspina Jul 12 '16 at 14:06
  • sir is there a way for u to modify this so it would display from another worksheet? cell.Offset(, .Columns.Count + 1) = rowOffset to worksheets("sheet2").cell.Offset(, .Columns.Count + 1) = rowOffset ?? maybe? – Egie Boy Aguspina Jul 13 '16 at 23:22
  • sure there is. see bottom of edited answer. the "trick" is taking the output cell address in the current sheet (`cell.offset(, .Columns.Count + 1).Address`) and feed it to the `Range` property of the `Worksheet` object referencing the wanted worksheet – user3598756 Jul 14 '16 at 05:49