I want to track count of a number how many times it is skipped i would track a string of 15 numbers at a time. If the number is found then it would get removed and the number next to it would take its place. and the counter would reset and track for the new number.
The image contains a matrix Top of the matrix is the count that is how many times the number of top row is skipped i.e 3 is skipped 25 times, 7 is skipped 36 times and 7 is skipped 19 times. We would track the individual digit columnwise.The middle and bottom row will get new numbers. Till new number is equal to number of the top row the counter would continue to count whenever new number equal to number of top row is found the top row would get replaced by the unique digit immediately below it. I have written code for tracking first column of matrix only but with 11 rows. and it works well till duplicates are found but whenever no duplicate is found it returns an error.
Sub sbFindDuplicatesInColumn()
Dim lastRow As Long
Dim matchFoundIndex As Long
Dim iCntr, RefNo As Long
Dim j, a, countno As Long
lastRow = Range("F500").End(xlUp).Row '<-- To find last row
RefNo = Range("F4").Value
matchFoundIndex = WorksheetFunction.Match(RefNo, Range("F5:F" & lastRow), 0) '<-- To find Count till first duplicate entry
countno = Range("F3").Value
If Application.WorksheetFunction.IsNA(WorksheetFunction.Match(RefNo, Range("F5:F" & lastRow), 0)) Then
countno = 10 + countno
Range("F3").Value = countno
End If
Range("F4").Clear ' <-- To Remove First entry
Range("F" & matchFoundIndex + 4).Clear ' <-- To remove First Duplicate Entry
For iCntr = 4 To matchFoundIndex + 2 '<-- To Start Shifting values up from first entry to duplicate entry
Range("F" & iCntr).Clear
Range("F" & iCntr).Value = Range("F" & iCntr + 1).Value
Next
For iCntr = matchFoundIndex + 3 To lastRow + 1 '<--- To Start Shifting values up from entry next to duplicate entry to last entry
Range("F" & iCntr).Value = Range("F" & iCntr + 2).Value
Next
End Sub
