Count Skips Between Last Occurrence

Sheet1

Sheet2

Sheet1 contains a list of drawings. The numbers are in Columns C through G. One row for each drawing.

Sheet2 contains numbers 1-43 of which 5 will be selected for the next drawing.

In Sheet2 Column B (SBLO) is where I want to place the number of drawings Skipped Between the Last Occurance for each number in Column A (#).

The Sheet2 example has the results I'm seeking in Column B (SBLO) according to the latest drawings in Sheet1.

[IMPORTANT] I didn't template the full history of drawings in my Sheet1 example. The #4 on Sheet2 shows 47 skips between last occurance but to save space I didn't list the drawings that far back.

Ex. 2-6-11-13-31 was the last drawing on Sheet1 so they will have 0 skips between last occurance on Sheet2. The drawing before that on Sheet1 was 6-7-22-23-32. Numbers# 7, 22, 23, 32 on Sheet2 will have 1 skip between last occurance, 6 already has 0, etc. etc.

Lame attempt below. This is stuff I mashed together from my little subroutine library but it definitely has big holes in it. I'm far from being VBA proficient. I only know enough to be dangerous and always frustrated.

Thanks in advance to anyone willing to help.

Sub Count_SBLO()
Dim myRange As Range
Dim c As Range
Set drwgRange = Sheet1.Range("C3", Range("G1048576").End(xlUp))
For Each c In myRange
For i = c.Row - 2 To 1 Step -1
If Not IsError(Application.Match(c, drwgRange.Rows(i), 0)) Then
c.Offset(, 1) = c.Row - 1 - i
Exit For
Else
c.Offset(, 1) = 0
End If
Next i
Next c
End Sub

Thanks Carim! I'll attach a .xlsm that gives a sample of what I'm seeking Monday. Everything is on my work PC. Also, I have horrendous rural internet service (500kbps) at home.

Carim,

See attached.

I'm grateful for your assistance.

SBLO.xlsm

If you look on Sheet2, Column B, it shows the results I'm looking for. These were manually counted and entered.

Moving forward, as I enter the latest drawing on Sheet1, the results will change on Sheet2.

Let me add this.

Imagine on the next drawing, instead of selecting 5 numbers out of 43 numbers, they drew all 43 numbers. Just for giggles.

I want to know how many skips between the last occurrence for each number (all 43 numbers).

I'd like to see that result in Column B on Sheet2.

Hope that helps make it more clear.

1. B7 (5), 5 drawings since the #1 (A7) was hit/picked/selected. See Sheet1 C872.

If you entered 1, in C878 of Sheet1 and counted the number of drawings between the last time 1 hit (Sheet1 C872), it would be 5 drawings.

2. B8 (0), the number 2 (A8) was picked/hit/selected in the previous drawing on (Sheet1 C877), so, there would be no skips/drawings between it IF #2 was picked/hit/selected for the next drawing.

3. B9 (12), 12 is the number of drawings between the next drawing that would be entered in one of the cells (C878:G878) IF 3 were picked/hit/selected in the next drawing.

Five numbers out of 43 will be entered as the next drawing in Sheet1 C878:G878. I don't know which five out of the 43 it will be but I'd like to know how many skips it would be between drawings for each of the 43 numbers that could be drawn with the count placed in Column B.

If I were to enter 1, 2, 3, 4, 5 in Sheet1(2) C878:G878 and click on the "Test SBLO" command button it would give me the result of 5, 0 , 12, 47, 9 in H878:L878.

The result of 5 in H878 is number of drawings since the #1 occurred in a previous drawing. The result of 0 would be the number of skips for the #2 in the previous drawing. The #3 had 12 skips since it last occurred in a previous drawing. Etc. for the #4, 47 skips since it last occurred in a previous drawing. Nine skips for the #5 since it last occurred in a previous drawing.

If I entered 6, 7, 8, 9, 10 in Sheet1(2) C878:G878 and click on the "Test SBLO" command button it would give me the result of 0, 1, 4, 3, 12 in H878:L878.

Instead of entering 1, 2, 3, 4, 5 and clicking the command button, get the results, then entering 6, 7, 8, 9, 10, get the results, etc. etc. through 39, 40, 41, 42, 43, I'd like to modify the Sub attached to the "Test SBLO" command button to count the number of skips between the last occurrence for all 43 numbers of which 5 will be picked/hit/selected. Place the count of skips in Column B of Sheet2 for each number in Column A.

Sheet1(2) is just an example of code that counts the number of skips between hits for winning numbers. I only included it to show the subrountine used to find that.

What I was hoping to find was a modification of that subroutine that counted the number of skips for ALL 43 numbers with the results displayed in Sheet2 Column B.

Let me review Test SBLO V2.

Just looking at it, I don't need Columns C-G.

I think Column A may be correct and what I need.

The need for a subroutine was to get results without filling a sheet with formulas in each cell but I can always Copy/Past Value. This is just one part of many different calculations I'll be doing.

I appreciate your efforts. Let me review.

Sorry, I meant to say, Column B may be correct and what I need.

After spot reviewing it. It is correct, although there are no formulas in any of the cells in Column B.