google-sheetsexcel-formulaarray-formulascounting

How to update excel formula to work for all columns continuously to the right and not just the current 2 it's set up for

I have a Group History tab that I keep a record of what group each student gets put in. Screenshot of contents of Group History tab:

screenshot of contents of Group History tab

The other tab named History Matrix I've gotten to successfully count how many times each student has been in a group with another student and I was able to drag the handle to dynamically update it for the other cells. Screenshot of contents of History Matrix tab:

screenshot of contents of History Matrix tab

The problem is, this is currently set up to only work with those 2 columns of dates and I would have to update the formula for future dates and it would become a bothersome. There's also, with the way it's currently written, if I delete any columns before N, then it would throw it off because some of the formula is using INDIRECT which mentions those column names by string text and not actually connecting the the columns to dynamically update with them. Here is the formula currently in cell B3 of the History Matrix:

=IF(B$2=$A3, "x", SUM((--('Group History'!$N3=INDIRECT("Group History!$N"&COLUMN(B$2)+1))) + (--('Group History'!$O3=INDIRECT("Group History!$O"&COLUMN(B$2)+1)))))

Link to view the spreadsheet

Link to make a copy

I've tried ChatGPT premium and it just ran in circles and was no help.

Solution

  • With Microsoft 365, you could try this:

    =LET(
    students, 'Group History'!A3:A28,
    grp_assigned, 'Group History'!B3:AD28,
    num_students, ROWS(students),
    st_indices, SEQUENCE(num_students),
    num_shared, LAMBDA(x, y,
        IF(
            x = y,
            "x",
            SUM(
                --BYCOL(
                    CHOOSEROWS(grp_assigned, x, y),
                    LAMBDA(c, IF(SUM(--(LEN(TRIM(c)) = 0)), 0, ROWS(UNIQUE(c)) = 1))
                )
            )
        )
    ),
    history_matrix, MAKEARRAY(num_students, num_students, num_shared),
    HSTACK(VSTACK("", students), VSTACK(TOROW(students), history_matrix))
)

    Result:

    Result

    Iterating two (student) rows at a time, then by each (date) column:

    Please adjust the range B3:AD28 according to possible future entries.

    Google Sheets

    Seems to work with some adjustments:

    =LET(
    students, 'Group History'!A3:A28,
    grp_assigned, 'Group History'!B3:AD28,
    num_students, ROWS(students),
    st_indices, SEQUENCE(num_students),
    num_shared, LAMBDA(x, y,
        IF(
            x = y,
            "x",
            SUM(
                BYCOL(
                    CHOOSEROWS(grp_assigned, x, y),
                    LAMBDA(c, IF(sum(--byrow(c, lambda(a, len(trim(a)) = 0))), 0, --(ROWS(UNIQUE(c)) = 1)))
                )
            )
        )
    ),
    history_matrix, MAKEARRAY(num_students, num_students, num_shared),
    result, HSTACK(VSTACK("", students), VSTACK(TOROW(students), history_matrix)),
result
)

    Sheets result