Removing the need of a helper column

M

Marvo

Board Regular
Joined
Nov 27, 2023
Messages
172
Office Version
  1. 2021
Platform
  1. Windows
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.
 

Attachments

  • Mr Excel.JPG
    Mr Excel.JPG
    79.2 KB · Views: 39
Sort by date Sort by votes
That's it working in Row 4. Attempting Row 5 leads to the resources error.

Cobblers test.xlsm
ABCDEFGHIJKLMNOPQ
1ALL-TIME RECORDS
2CONSECUTIVESWWinsDDrawsLDefeatsGFGA
3TOTALTIMESSTART/END DATETOTALTIMESSTART/END DATETOTALTIMESSTART/END DATETOTALTIMESSTART/END DATETOTALTIMESSTART/END DATE
4ALLALL11107/04/1900621983, 26/02/2011741972, 1985, 1988, 17/10/199228103/10/190829128/12/1965
5H05121/03/1981621985 & 200254128/01/092816113/10/1900
6A622004 & 2015421913 & 199416127/10/190619127/03/195940101/09/1926
Records
Cell Formulas
RangeFormula
C4:K4C4=LET(loc, $B4, src, ALL!$B$2:$M$7000, t, $A$2:$Q$17, a, INDEX(t, , 1), idxCon, MATCH("Consecutives", a, 0), idxLR, MATCH("Longest Runs", a, 0), curridx, ROW() - ROW(INDEX(a, 1)) + 1, dyna, INDEX(a, SEQUENCE(curridx)), mt, INDEX(dyna, XMATCH(TRUE, dyna <> "", 0, -1)), ct, IF(curridx >= idxLR, "Longest Runs", "Consecutives"), oc_input, IF(curridx >= idxLR, INDEX(t, idxLR, COLUMN()), INDEX(t, idxCon, COLUMN())), oc, XLOOKUP(oc_input, {"W","NO L","D","NO D","L","NO W","GF","NO GA","GA","NO GF"}, {"W","L","D","D","L","W","S","S","S","S"}), sect, IFS(OR(oc_input = {"W","D","L","NO L","NO D","NO W"}), 1, OR(oc_input = {"GF","NO GF"}), 2, OR(oc_input = {"GA","NO GA"}), 3), fsrc_cols, CHOOSE(sect, {1,4,9,10}, {1,4,9,11}, {1,4,9,12}), fsrc, FILTER(src, INDEX(src, , 10) <> ""), data, INDEX(fsrc, SEQUENCE(ROWS(fsrc)), fsrc_cols), allr_data, SEQUENCE(ROWS(data), , 1, 0), loc_ary, IF(loc = "All", allr_data, INDEX(data, , 2) = loc), mt_ary, IF(mt = "All", allr_data, INDEX(data, , 3) = mt), fd, FILTER(INDEX(data, SEQUENCE(ROWS(data)), {1,4}), loc_ary * mt_ary), allr_fd, SEQUENCE(ROWS(fd)), oc_col, IFS(sect = 1, INDEX(fd, , 2), OR(sect = 2, sect = 3), IF(INDEX(fd, , 2) > 0, "S", "N")), revfd, IF(SWITCH(ct, "Consecutives", oc_col = oc, "Longest Runs", oc_col <> oc), CHOOSE({1,2}, INDEX(fd, allr_fd, 1), oc_col), {"|","|"}), rstr, CONCAT(INDEX(revfd, , 2)), rary, IFERROR(FILTERXML("<x><y>" & SUBSTITUTE(rstr, "|", "</y><y>") & "</y></x>", "//y"), ""), ls, LEN(rary), maxs, MAX(ls), nmaxs, SUM(--(ls = maxs)), csum_ls, MMULT(--(SEQUENCE(1, ROWS(ls)) <= SEQUENCE(ROWS(ls))), ls), idxs_ls, FILTER(SEQUENCE(ROWS(ls)), ls = maxs), idxs_dts, INDEX(csum_ls, idxs_ls), seds, INDEX(FILTER(INDEX(revfd, , 1), INDEX(revfd, , 1) <> "|"), idxs_dts), dt_comp, TEXTJOIN(", ", , IFERROR(TEXT(INDEX(seds, SEQUENCE(nmaxs - 1)), "yyyy"), ""), TEXT(INDEX(seds, nmaxs), "dd/mm/yyyy")), finres, IF(maxs = 0, {"none","",""}, CHOOSE({1,2,3}, maxs, nmaxs, dt_comp)), finres)
C5C5=LET(loc, $B4, src, ALL!$B$2:$M$7000, t, $A$2:$Q$17, a, INDEX(t, , 1), idxCon, MATCH("Consecutives", a, 0), idxLR, MATCH("Longest Runs", a, 0), curridx, ROW() - ROW(INDEX(a, 1)) + 1, dyna, INDEX(a, SEQUENCE(curridx)), mt, INDEX(dyna, XMATCH(TRUE, dyna <> "", 0, -1)), ct, IF(curridx >= idxLR, "Longest Runs", "Consecutives"), oc_input, IF(curridx >= idxLR, INDEX(t, idxLR, COLUMN()), INDEX(t, idxCon, COLUMN())), oc, XLOOKUP(oc_input, {"W","NO L","D","NO D","L","NO W","GF","NO GA","GA","NO GF"}, {"W","L","D","D","L","W","S","S","S","S"}), sect, IFS(OR(oc_input = {"W","D","L","NO L","NO D","NO W"}), 1, OR(oc_input = {"GF","NO GF"}), 2, OR(oc_input = {"GA","NO GA"}), 3), fsrc_cols, CHOOSE(sect, {1,4,9,10}, {1,4,9,11}, {1,4,9,12}), fsrc, FILTER(src, INDEX(src, , 10) <> ""), data, INDEX(fsrc, SEQUENCE(ROWS(fsrc)), fsrc_cols), allr_data, SEQUENCE(ROWS(data), , 1, 0), loc_ary, IF(loc = "All", allr_data, INDEX(data, , 2) = loc), mt_ary, IF(mt = "All", allr_data, INDEX(data, , 3) = mt), fd, FILTER(INDEX(data, SEQUENCE(ROWS(data)), {1,4}), loc_ary * mt_ary), allr_fd, SEQUENCE(ROWS(fd)), oc_col, IFS(sect = 1, INDEX(fd, , 2), OR(sect = 2, sect = 3), IF(INDEX(fd, , 2) > 0, "S", "N")), revfd, IF(SWITCH(ct, "Consecutives", oc_col = oc, "Longest Runs", oc_col <> oc), CHOOSE({1,2}, INDEX(fd, allr_fd, 1), oc_col), {"|","|"}), rstr, CONCAT(INDEX(revfd, , 2)), rary, IFERROR(FILTERXML("<x><y>" & SUBSTITUTE(rstr, "|", "</y><y>") & "</y></x>", "//y"), ""), ls, LEN(rary), maxs, MAX(ls), nmaxs, SUM(--(ls = maxs)), csum_ls, MMULT(--(SEQUENCE(1, ROWS(ls)) <= SEQUENCE(ROWS(ls))), ls), idxs_ls, FILTER(SEQUENCE(ROWS(ls)), ls = maxs), idxs_dts, INDEX(csum_ls, idxs_ls), seds, INDEX(FILTER(INDEX(revfd, , 1), INDEX(revfd, , 1) <> "|"), idxs_dts), dt_comp, TEXTJOIN(", ", , IFERROR(TEXT(INDEX(seds, SEQUENCE(nmaxs - 1)), "yyyy"), ""), TEXT(INDEX(seds, nmaxs), "dd/mm/yyyy")), finres, IF(maxs = 0, {"none","",""}, CHOOSE({1,2,3}, maxs, nmaxs, dt_comp)), finres)
C6,I6,F6C6=LET(t,FILTER(ALL!$K2:$K7000,ALL!$E2:$E7000=$B$6),MAX(FREQUENCY(IF(t=C2,SEQUENCE(ROWS(t))),IF(t<>C2,SEQUENCE(ROWS(t))))))
F5,I5F5=LET(t,FILTER(ALL!$K2:$K7000,ALL!$E2:$E7000=$B$5),MAX(FREQUENCY(IF(t=F2,SEQUENCE(ROWS(t))),IF(t<>F2,SEQUENCE(ROWS(t))))))
L4L4=LET(t,ALL!L2:L7000,s,SEQUENCE(ROWS(t)),MAX(FREQUENCY(IF(t<>0,s),IF(t=0,s))))
L5L5=LET(t,FILTER(ALL!$L$2:$L$7000,ALL!$E2:$E7000=$B$5),MAX(FREQUENCY(IF(t<>0,SEQUENCE(ROWS(t))),IF(t=0,SEQUENCE(ROWS(t))))))
L6L6=LET(t,FILTER(ALL!$L$2:$L$7000,ALL!$E$2:$E$7000=$B$6),MAX(FREQUENCY(IF(t<>0,SEQUENCE(ROWS(t))),IF(t=0,SEQUENCE(ROWS(t))))))
O4O4=LET(t,ALL!$M2:$M7000,s,SEQUENCE(ROWS(t)),MAX(FREQUENCY(IF(t<>0,s),IF(t=0,s))))
O5O5=LET(t,FILTER(ALL!M2:M7000,ALL!$E2:$E7000=$B$5),MAX(FREQUENCY(IF(t<>0,SEQUENCE(ROWS(t))),IF(t=0,SEQUENCE(ROWS(t))))))
O6O6=LET(t,FILTER(ALL!M2:M7000,ALL!E2:E7000=$B$6),MAX(FREQUENCY(IF(t<>0,SEQUENCE(ROWS(t))),IF(t=0,SEQUENCE(ROWS(t))))))
Dynamic array formulas.
 
Upvote 0

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
The formula should be copied from a working cell (such as C4) and then pasted into C5...or better yet, delete everyhing in C5:C9. Then select C4, and look closely for a small green dot/square in the lower right corner of C4. "Grab" that square by clicking on it and holding down the mouse button and drag the formula down through cells C5:C9. The loc, $B4 reference should automatically udate to $B5, then $B6, etc. And when posted using XL2BB, you wouldn't see two different formulas...just one applied to a larger range.
 
Upvote 0
That's it copied down in the manner you suggested. When moving to F4 got the resources error. The workbook is painfully slow once the formula is entered, just deleting a single cell is taking several seconds.




Cobblers test.xlsm
ABCDEFGHIJK
2CONSECUTIVESWWinsDDrawsLDefeats
3TOTALTIMESSTART/END DATETOTALTIMESSTART/END DATETOTALTIMESSTART/END DATE
4ALLALL11107/04/19000
5H14110/12/1927
6A622004, 16/01/2016
7LeagueALL11107/04/1900
8Homenone
9Awaynone
Records
Cell Formulas
RangeFormula
F4,C4:E9F4=LET(loc, $B4, src, ALL!$B$2:$M$7000, t, $A$2:$Q$17, a, INDEX(t, , 1), idxCon, MATCH("Consecutives", a, 0), idxLR, MATCH("Longest Runs", a, 0), curridx, ROW() - ROW(INDEX(a, 1)) + 1, dyna, INDEX(a, SEQUENCE(curridx)), mt, INDEX(dyna, XMATCH(TRUE, dyna <> "", 0, -1)), ct, IF(curridx >= idxLR, "Longest Runs", "Consecutives"), oc_input, IF(curridx >= idxLR, INDEX(t, idxLR, COLUMN()), INDEX(t, idxCon, COLUMN())), oc, XLOOKUP(oc_input, {"W","NO L","D","NO D","L","NO W","GF","NO GA","GA","NO GF"}, {"W","L","D","D","L","W","S","S","S","S"}), sect, IFS(OR(oc_input = {"W","D","L","NO L","NO D","NO W"}), 1, OR(oc_input = {"GF","NO GF"}), 2, OR(oc_input = {"GA","NO GA"}), 3), fsrc_cols, CHOOSE(sect, {1,4,9,10}, {1,4,9,11}, {1,4,9,12}), fsrc, FILTER(src, INDEX(src, , 10) <> ""), data, INDEX(fsrc, SEQUENCE(ROWS(fsrc)), fsrc_cols), allr_data, SEQUENCE(ROWS(data), , 1, 0), loc_ary, IF(loc = "All", allr_data, INDEX(data, , 2) = loc), mt_ary, IF(mt = "All", allr_data, INDEX(data, , 3) = mt), fd, FILTER(INDEX(data, SEQUENCE(ROWS(data)), {1,4}), loc_ary * mt_ary), allr_fd, SEQUENCE(ROWS(fd)), oc_col, IFS(sect = 1, INDEX(fd, , 2), OR(sect = 2, sect = 3), IF(INDEX(fd, , 2) > 0, "S", "N")), revfd, IF(SWITCH(ct, "Consecutives", oc_col = oc, "Longest Runs", oc_col <> oc), CHOOSE({1,2}, INDEX(fd, allr_fd, 1), oc_col), {"|","|"}), rstr, CONCAT(INDEX(revfd, , 2)), rary, IFERROR(FILTERXML("<x><y>" & SUBSTITUTE(rstr, "|", "</y><y>") & "</y></x>", "//y"), ""), ls, LEN(rary), maxs, MAX(ls), nmaxs, SUM(--(ls = maxs)), csum_ls, MMULT(--(SEQUENCE(1, ROWS(ls)) <= SEQUENCE(ROWS(ls))), ls), idxs_ls, FILTER(SEQUENCE(ROWS(ls)), ls = maxs), idxs_dts, INDEX(csum_ls, idxs_ls), seds, INDEX(FILTER(INDEX(revfd, , 1), INDEX(revfd, , 1) <> "|"), idxs_dts), dt_comp, TEXTJOIN(", ", , IFERROR(TEXT(INDEX(seds, SEQUENCE(nmaxs - 1)), "yyyy"), ""), TEXT(INDEX(seds, nmaxs), "dd/mm/yyyy")), finres, IF(maxs = 0, {"none","",""}, CHOOSE({1,2,3}, maxs, nmaxs, dt_comp)), finres)
 
Upvote 0
The words "Longest Runs" still need to be present in the first column of your summary table so the formula "knows" where each half of the table is located. Also, check your location column B entries...they need to match the results in the large data table...so is it "H" or "Home", "A" or "Away"?
MrExcel_20240321 (version 1) (version 1).xlsx
ABCDEFGHIJK
1
2CONSECUTIVESWWinsDDrawsLDefeats
3TOTALTIMESSTART/END DATETOTALTIMESSTART/END DATETOTALTIMESSTART/END DATE
4ALLALL7110/03/20242114/02/2024
5H522024, 29/02/20241113/02/2024
6A6110/03/2024132024, 2024, 27/02/2024
7LeagueALL5126/02/20242114/02/2024
8H6104/03/20241113/02/2024
9A3126/02/2024132024, 2024, 27/02/2024
10LONGEST RUNS
Sheet6
Cell Formulas
RangeFormula
C4:H9C4=LET(loc, $B4, src, All!$B$2:$M$7000, t, $A$2:$Q$17, a, INDEX(t, , 1), idxCon, MATCH("Consecutives", a, 0), idxLR, MATCH("Longest Runs", a, 0), curridx, ROW() - ROW(INDEX(a, 1)) + 1, dyna, INDEX(a, SEQUENCE(curridx)), mt, INDEX(dyna, XMATCH(TRUE, dyna <> "", 0, -1)), ct, IF(curridx >= idxLR, "Longest Runs", "Consecutives"), oc_input, IF(curridx >= idxLR, INDEX(t, idxLR, COLUMN()), INDEX(t, idxCon, COLUMN())), oc, XLOOKUP(oc_input, {"W","NO L","D","NO D","L","NO W","GF","NO GA","GA","NO GF"}, {"W","L","D","D","L","W","S","S","S","S"}), sect, IFS(OR(oc_input = {"W","D","L","NO L","NO D","NO W"}), 1, OR(oc_input = {"GF","NO GF"}), 2, OR(oc_input = {"GA","NO GA"}), 3), fsrc_cols, CHOOSE(sect, {1,4,9,10}, {1,4,9,11}, {1,4,9,12}), fsrc, FILTER(src, INDEX(src, , 10) <> ""), data, INDEX(fsrc, SEQUENCE(ROWS(fsrc)), fsrc_cols), allr_data, SEQUENCE(ROWS(data), , 1, 0), loc_ary, IF(loc = "All", allr_data, INDEX(data, , 2) = loc), mt_ary, IF(mt = "All", allr_data, INDEX(data, , 3) = mt), fd, FILTER(INDEX(data, SEQUENCE(ROWS(data)), {1,4}), loc_ary * mt_ary), allr_fd, SEQUENCE(ROWS(fd)), oc_col, IFS(sect = 1, INDEX(fd, , 2), OR(sect = 2, sect = 3), IF(INDEX(fd, , 2) > 0, "S", "N")), revfd, IF(SWITCH(ct, "Consecutives", oc_col = oc, "Longest Runs", oc_col <> oc), CHOOSE({1,2}, INDEX(fd, allr_fd, 1), oc_col), {"|","|"}), rstr, CONCAT(INDEX(revfd, , 2)), rary, IFERROR(FILTERXML("<x><y>" & SUBSTITUTE(rstr, "|", "</y><y>") & "</y></x>", "//y"), ""), ls, LEN(rary), maxs, MAX(ls), nmaxs, SUM(--(ls = maxs)), csum_ls, MMULT(--(SEQUENCE(1, ROWS(ls)) <= SEQUENCE(ROWS(ls))), ls), idxs_ls, FILTER(SEQUENCE(ROWS(ls)), ls = maxs), idxs_dts, INDEX(csum_ls, idxs_ls), seds, INDEX(FILTER(INDEX(revfd, , 1), INDEX(revfd, , 1) <> "|"), idxs_dts), dt_comp, TEXTJOIN(", ", , IFERROR(TEXT(INDEX(seds, SEQUENCE(nmaxs - 1)), "yyyy"), ""), TEXT(INDEX(seds, nmaxs), "dd/mm/yyyy")), finres, IF(maxs = 0, {"none","",""}, CHOOSE({1,2,3}, maxs, nmaxs, dt_comp)), finres)
Dynamic array formulas.
 
Upvote 0
I can't upload a mini-sheet on here now as it says its too big. However your formula works. Cells C4 through to K17 all give correct results.

The only problem is at every stage I am getting the resources error.

Note: In my formula I was only using a single instance of W, D, L, H, A, League, (the ones in pink). All formulas pointed to these whatever row they were in.
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Solved
Finding a unique value in a range and returning the value in another column of the same row, without offset / slow formulae.
Replies
8
Views
2K
AaronPMH
A
G
  • Question Question
Find Values that Match Criteria from Different Sheet and Copy over to Another
Replies
0
Views
319
glaccounting
G
S
  • Question Question
Need code that adds multiple new rows to table to include a check box in column "L" for each new row
Replies
0
Views
533
sspatriots
S
A
  • Question Question
create a macro that will replicate the output of my helper columns
Replies
2
Views
608
ajm
A
A
  • Solved
Using IF STATEMENTS, CONDITIONAL FORMATS and VALIDATE to track tasks. HOW? PT. 2
Replies
5
Views
680
fjns
F

Forum statistics

Threads
1,223,227
Messages
6,170,853
Members
452,361
Latest member
d3ad3y3

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top