# Posts by VBAwant2b

• ## Count Skips Between Last Occurrence

Perfect! Does help.

Once again, thanks for sharing your Excel brilliance with mere mortals like myself.

• ## Count Skips Between Last Occurrence

Hi Carim,

I moved the formula you created for me yesterday to another workbook. When I tried to reopen the workbook this morning, I get a message to update links.

Why can't I use that formula in another workbook without having to link it to the workbook that it was created in?

I made a worksheet reference change in the formula from Data! to DB! which is an exact copy of the Data worksheet in Test SBLO V3.xlsm

Test SBLO V3 - {=COUNTA(Data!\$A:\$A)-MAX(IF(ListN1=A6,ROW(ListN1)-MIN(ROW(ListN1))+1))-1}

New Workbook - {=COUNTA(DB!\$A:\$A)-MAX(IF(ListN1=A6,ROW(ListN1)-MIN(ROW(ListN1))+1))-1}

Does it have anything to do with "ListN1"? I really don't understand that part of the formula. What is ListN1? I'll research ROW function more.

I'd really need to be able to use the formula without having to link it to another workbook.

When I try to break the links, it warns me to save a copy. I'm not sure what is going on. I'll try to research this more myself because I need to understand why I can't use the formula as written.

I wonder if a subroutine is the way to go.

• ## Count Skips Between Last Occurrence

Thank You! Thank You! Thank You!

• ## Count Skips Between Last Occurrence

Yes, I left it empty not realizing it was part of the formula and necessary to do a correct update.

This works!

Wow! You don't know how much I appreciate your efforts!

• ## Count Skips Between Last Occurrence

Almost. Very close.

Your formula result (C25), "7" for #24 is correct. My manual count, "6", was incorrect.

I tried to enter a few numbers for the next drawing to see how the formulas on Sheet SBLO update and they don't exactly.

Enter 39, 40, 41, 42, 43 as the next winning numbers on the Data Sheet and see what results you get for SBLO.

Also, try entering the last winning numbers 2, 6, 11, 13, 31 as the winning numbers for the next drawing to see what you get. The formula update should be "0" for each number. I'm getting "-1" as a result.

Lastly, whenever I enter the winning numbers for the next drawing, all of the results should update with an additional skip.

In other words, if #7 didn't hit on the next drawing and the formula results show it had "1" skip, when I enter the next winning numbers, although "7" wasn't one of the winning numbers, the formula should update to show it's now "2" skips IF it were to hit in the next drawing.

• ## Count Skips Between Last Occurrence

Sorry, I'm at work trying to keep the bosses happy while trying to focus on this as well.

See attached. I made comments in a few of the cells to explain why they don't match.

See if it makes sense to you.

Copy of Test SBLO V2.xlsm

• ## Count Skips Between Last Occurrence

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.

• ## Count Skips Between Last Occurrence

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.

• ## Count Skips Between Last Occurrence

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.

• ## Count Skips Between Last Occurrence

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.

• ## Count Skips Between Last Occurrence

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.

• ## Count Skips Between Last Occurrence

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.

• ## Count Skips Between Last Occurrence

Carim,

See attached.

SBLO.xlsm

• ## Count Skips Between Last Occurrence

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.

• ## Count Skips Between Last Occurrence

Sheet1

 ID Date N1 N2 N3 N4 N5

 847 2/27/2021 10 16 17 24 36 848 2/28/2021 6 9 13 14 22 849 3/1/2021 2 6 18 23 33 850 3/2/2021 9 15 20 27 34 851 3/3/2021 19 22 23 24 35 852 3/4/2021 6 13 20 33 36 853 3/5/2021 3 14 15 20 36 854 3/6/2021 1 12 15 20 43 855 3/7/2021 11 15 16 18 43 856 3/8/2021 16 30 31 39 40 857 3/9/2021 3 20 25 30 43 858 3/10/2021 18 20 25 28 29 859 3/11/2021 2 10 13 29 30 860 3/12/2021 5 18 19 38 39 861 3/13/2021 5 11 20 21 26 862 3/14/2021 7 12 25 28 41 863 3/15/2021 13 37 38 39 43 864 3/16/2021 3 10 11 22 41 865 3/17/2021 15 16 17 19 37 866 3/18/2021 18 24 28 31 39 867 3/19/2021 5 13 22 27 34 868 3/20/2021 15 16 21 24 37 869 3/21/2021 13 14 18 24 29 870 3/22/2021 9 18 32 33 34 871 3/23/2021 1 12 18 32 36 872 3/24/2021 6 8 13 29 36 873 3/25/2021 9 12 16 20 35 874 3/26/2021 6 28 33 36 39 875 3/27/2021 6 7 22 23 32 876 3/28/2021 2 6 11 13 31

Sheet2

 # SBLO 1 5 2 0 3 12 4 47 5 9 6 0 7 1 8 4 9 3 10 12 11 0 12 3 13 0 14 7 15 8 16 3 17 11 18 5 19 11 20 3 21 8 22 1 23 1 24 6 25 14 26 15 27 9 28 2 29 4 30 17 31 0 32 1 33 2 34 6 35 3 36 2 37 8 38 13 39 2 40 20 41 12 42 37 43 13

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.