Removing the need of a helper column

[Marvo]

Good morning, yesterday I posed a question regarding the attached image and Stephen Crump kindly gave me the perfect solution.

=COUNTIFS(A2:A34,"League",B2:B34,"H",D2:D34,E14)

What has got me thinking is the solution doesn't reference column C at all and as column D is a helper column that refers to Column C, is it possible to "cut out the middle man" completely, meaning I could dispense with the helper column? It would be a great boon if so as the several helper columns (60 in all) really slow the workbook down.

The formula in D3 is =IF(($A3="League")*($B3="H"),IF($C3="W",SUM(1,D2),0),N(D2))

Copied down

Many thanks for any help offered and if it can't be done, so be it. Just wondered.

 

[Cubist]

Nevermind. LET and FILTER were available in 2021.

 

[Marvo]

So far so good, I've amended the formula for the large workbook, its now

=IF(C25="","",LET(str,CONCAT(FILTER(ALL!$K$2:$K$5558,(ALL!$J$2:$J$5558="League")*(ALL!$E$2:$E$5558="H"))),Wstr,FILTERXML("<x><y>"&SUBSTITUTE(SUBSTITUTE(str,"L","</y><y>"),"D","</y><y>")&"</y></x>","//y"),NconW,IFERROR(LEN(Wstr),0),SUM(--(NconW>=C25))))

and that works fine. However (there's always a however isn't there?) I forgot that in the adjacent cell, I have a formula

=IF(C25="","",XLOOKUP(C25,ALL!$BB$2:$BB$5558,ALL!$B$2:$B$5558,,0,-1))

which returns the date 9 (Column B on the ALL worksheet) of the most recent streak. That too references the helper column BB so I might have flummoxed myself as the long term plan was to eliminate the 60 helper columns slowing down the workbook.

****.

 

[Marvo]

I've just thought, as the values in the helper columns will not change unless they are in the last few rows of the 5,558, so maybe 50 to be on the safe side, I suppose I could copy the cells, paste them as values back into the helper columns. I guess that would reduce the size of the workbook by quite a chunk.

 

[Marvo]

I've posted an image so you can see what I'm doing.

Your Formula adapted to the workbook is in D25,

=IF(C25="","",LET(str,CONCAT(FILTER(ALL!$K$2:$K$5558,(ALL!$J$2:$J$5558=A25)*(ALL!$E$2:$E$5558=B25))),Wstr,FILTERXML("<x><y>"&SUBSTITUTE(SUBSTITUTE(str,"L","</y><y>"),"D","</y><y>")&"</y></x>","//y"),NconW,IFERROR(LEN(Wstr),0),SUM(--(NconW>=C25))))

In D25 is

=IF(C25="","",XLOOKUP(C25,ALL!$BB$2:$BB$5558,ALL!$B$2:$B$5558,,0,-1))

I've put an 11 into C25 and the formula is now telling me the team has won 11 games consecutively on 6 occasions, the most recent being 31/01/1976.

 

[KRice]

I’m away from my computer for a while, but will look when I return.

 

[Marvo]

That's fine.

I've just checked the result from my previous post and I'm afraid your formula has reverted to give a count of every 11 in the column, some in the same streak and that was the initial problem Stephen Crump solved.

It seems solving one problem leads to another.

 

[KRice]

I don’t understand what you mean.

 

[Marvo]

You're okay, that was my mistake, it is giving the correct result. My apologies.

I think if I keep the helper columns but just paste in the result (remove the formula) that might work. I'll make a copy, test it, see how much the workbook is reduced and if it quickens up.

 

[Marvo]

So I've copied cells AH2:C05458 (the 60 helper columns) and then pasted them back in just as the value. (I've left the latest 100 rows with formula).

That has reduced the workbook from 5512KB down to 3025KB and when I closed it it was much quicker.

That's given me a bit of leeway.

 

[Marvo]

So to conclude this thread, there doesn't appear to be a way to remove the 60 helper columns but by copying and pasting them as values instead of formula, the need to remove them is alleviated.

Thanks to everybody that helped.