– it is only natural to want to skip the repetitive values and just go to the google one next (also see GIF embedded below).<\/p>\n\n\n\n
Because once we see it, we know we can just exclude this data using a regex or a wildcard. However, that next item on the list may be 10K rows later; there is no way to find it unless you skillfully use mouse for scrolling, or use the old-fashioned PageDown brute-force attack.<\/p>\n\n\n\n
Now imagine making the computer working it out for you.<\/p>\n\n\n\n
You could use a Levenshtein distance<\/a> between the current cell value, and the cells below. As long as the calculated distance is low, we could continue to progress. And only stop when the next cell is substantially different from the one that is currently selected:<\/p>\n\n\n\n The core code I used to demo it looks like this:<\/p>\n\n\n\n I borrowed the VBA code for Levenshtein distance from this post<\/a> on Stack Overflow.<\/p>\n\n\n\n And yes, you can embed it in your Excel workbook and assign it to a shortcut.<\/p>\n","protected":false},"excerpt":{"rendered":" This post is a request for Excel devs to implement a novelty data navigation function. I call it a ‘Levenshtein Move’. When we review data, especially in a security field, and in particular sorted data gathered by Threat Hunters, we … Continue reading ![\"\"](\"https:\/\/www.hexacorn.com\/blog\/wp-content\/uploads\/2019\/06\/Levenshtein_distance.gif\")
<\/figure>\n\n\n\nSub Levenshtein_Move_down()\n On Error Resume Next\n c = ActiveCell.Column\n r = ActiveCell.Row\n firstcell = Cells(r, c).Value\n\n Do While True\n r = r + 1\n curcell = ActiveSheet.Cells(r, c)\n If curcell = \"\" Then\n Exit Do\n End If\n l = Levenshtein(firstcell, curcell)\n If l > 3 Then\n Exit Do\n End If\n Loop\n Cells(r, c).Select\nEnd Sub\n\nFunction Levenshtein(ByVal string1 As String, ByVal string2 As String) As Long\n\nDim i As Long, j As Long\nDim string1_length As Long\nDim string2_length As Long\nDim distance() As Long\n\nstring1_length = Len(string1)\nstring2_length = Len(string2)\nReDim distance(string1_length, string2_length)\n\nFor i = 0 To string1_length\n distance(i, 0) = i\nNext\n\nFor j = 0 To string2_length\n distance(0, j) = j\nNext\n\nFor i = 1 To string1_length\n For j = 1 To string2_length\n If Asc(Mid$(string1, i, 1)) = Asc(Mid$(string2, j, 1)) Then\n distance(i, j) = distance(i - 1, j - 1)\n Else\n distance(i, j) = Application.WorksheetFunction.Min _\n (distance(i - 1, j) + 1, _\n distance(i, j - 1) + 1, _\n distance(i - 1, j - 1) + 1)\n End If\n Next\nNext\n\nLevenshtein = distance(string1_length, string2_length)\n\nEnd Function<\/code><\/pre>\n\n\n\n