Counting given amount of consecutives

[Marvo]

Hi Again, I've constructed a formula

=XLOOKUP("W",K:K,B:B,,0,-1)

That works well, it finds the most recent time there was a "W" in a column.

How could I change that formula so that it would look for a given amount of consecutive "W"s please?

I'm thinking maybe of having it refer to a cell $F$5579 with a number in it, then look for that amount of "W"'s.

So if I put a 3 in the reference cell, the formula would then look for the most recent time there were 3 consecutive "W"'s in column K.

Would that be the way to go?

As always any help appreciated and if there's a better way of achieving the desired result I'm happy to be educated.


/?

 

[Marvo]

Cobblers help.xlsm

 

[Marvo]

I've been playing with this and come up with something interesting though not sure I could make use of it.

SO in cell F2 I have =MATCH(B1&B2&B3,$D$1:$D$26&$D$2:$D$27&$D$3:$D$28,0) which is an array formula and it gives a result of Row 11 which is where you find the first sequence of 3 W's.
Then in Cell F3 I have =MATCH(B1&B2&B3&B4,$D$1:$D$26&$D$2:$D$27&$D$3:$D$28&D4:D29,0) which is an array formula and it gives a result of Row 23 which is where you find the first sequence of 4 W's.

So theoretically I could look down a column and find the first instance of however many sequential W's I want.

Problem 1) I need to look up the column, not down.
Problem 2) It would take a month of Sundays to write all the formula I'd need. On occasions you might be looking for 30 sequential cells (or more)

I thought it was interesting though, maybe a VBA to do the same job?

 

[dermie_72]

I think VBA would be best, I've just started learning that. It seems to work through searches much quicker. Only know the basics though. Managed to do some sorts.

I've still got a lot to work through, will need criteria added to further searches. I cant get that XL2BB to work but I can upload the workbook to the cloud for sharing.

I usually ask one question, then try to apply the answer to several others.

Sorry for the tardy response, been incredibly busy this morning.

do you have any code you've worked on to do this that we can help you with?

 

[Marvo]

No, sorry. I'm new to VBA, the only code I've managed so far is SORT by either high or low, date and Alphabetically. Very useful but limited.

I've come up with a way to get the result I want using formula but it would need loads of helper rows and that's something I wanted to avoid, I really only need this to work a few times a year and the thought of excel going through all the formulations after each game is played fills me with dread.

I suppose I could construct a new workbook just to work out these calculations and only open it when it was needed.

 

[Peter_SSs]

I cant get that XL2BB to work

Look through the suggestions here

Just to clarify, the question is, if a team was to draw their last 5 matches, when was the last time that happened?

That didn't clarify my question at the start of post 6.
In my sample data in post 6, when did the team last win 4 in a row?

1. Is it 20/01/2024? On that date they had won the last 4 games. BUT they had also won the game before those 4, making 5 in a row. So does 20/01/2024 make the last 4 in a row for you?
2. Is it 19/01/2024? On that date they had won 4 in a row after not winning the previous game before the 4. BUT they also won the next game after that making 5 in a row. So does 19/01/2024 make the last 4 in a row for you?
3. Is it 7/01/2024? On that date they completed the last time they won exactly 4 in a row. So does 7/01/2024 make the last 4 in a row for you?

Would it help if I shared the workbook on one drive?

The question seems to have become much more complicated that originally stated so I'm not sure whether I will be able to help or not. However, my preference would be for a smallish, carefully selected set of sample data of say 40 or 50 rows, not years worth and with XL2BB
Some forum helpers choose not to download files from other sources and some are prevented from doing so by workplace security restrictions, so that can reduce your pool of potential helpers.

 

[Marvo]

My apologies.

1) Yes, 20/1/24 would count.
2) Doesn't really matter, Just looking for the general time period.
3) See Above.

If you imagine someone asking you a question. "We have just won 7 games in a row, when did that last happen?" A response of, "it was in December 1987" is more than adequate. It also wouldn't matter if that was indeed 8 games in a row because that fulfils the original question of 7 games in a row.

Having searched the internet for hours yesterday trying to find a solution, I've come to the conclusion you're right, it is too complicated. I did manage to get a formula working out the more simpler equations when you count all the games but as soon as you try and add criteria like "League" or "Home" it all falls apart.

I have two avenues left, I used to have a workbook that did answer via VBA the very questions I'm asking but lost it when a hard drive failed. However I did think last night that maybe I had that workbook on an old hard drive if I can dig it out AND when the original guy did the VBA it was online (many years ago) and its possible it's still there if I can find it.

If I do find a solution I'll get back to you, thanks for the help.

 

[Marvo]

Quick update: I've immediately made a breakthrough this morning, it will take me some time to complete the work but I think I may be getting a lot closer to a solution. I have solved the multiple criteria problem. My formula is pretty basic stuff which I'm sure once completed will be easily "tidied up" by someone more experienced.

 

[Marvo]

I've solved this now but I've got to go out. I'll report back later.

 

[Marvo]

Now I've solved this I would love to share how but despite downloading XL2BB it does nothing. Its not showing in the Add-ins, it just sitting there in the downloads. Click on it opens a spreadsheet but then what? There's nothing there?

 

[Peter_SSs]

despite downloading XL2BB it does nothing. Its not showing in the Add-ins, it just sitting there in the downloads. Click on it opens a spreadsheet but then what? There's nothing there?

You don't click on it to open it.
Sounds like you are not following the Installation instructions here carefully enough.
If, after following those instructions you still have some difficulties, look at the link I provided at the start of post 15.