Forum Discussion
Counting instances of specific data pairs across entire array
My source table looks like this:
| Court 1 | Court 1 | Court 1 | Court 1 | |
| WEEK 1 | 1 | 6 | 2 | 7 |
| WEEK 2 | 5 | 9 | 1 | 7 |
| WEEK 3 | 3 | 10 | 1 | 5 |
| WEEK 4 | 6 | 10 | 4 | 5 |
| WEEK 5 | 8 | 10 | 7 | 3 |
| WEEK 6 | 1 | 4 | 9 | 5 |
| WEEK 7 | 6 | 2 | 1 | 5 |
| WEEK 8 | 8 | 1 | 7 | 3 |
| WEEK 9 | 10 | 2 | 9 | 6 |
| WEEK 10 | 4 | 2 | 5 | 7 |
| WEEK 11 | 1 | 2 | 3 | 6 |
I want to use that data to populate the table below with the total number of times in the entire table any 2 numbers occur in a single row. So, for example, the number 3 would go in the circled cell because the combination 1 and 2 occurs in three of the rows in the source table (Weeks 1, 7 and 11). Any help is appreciated.
=IF(C$15=$B16,"-----",SUMPRODUCT((N(MMULT(N($B16=$B$2:$E$12),ROW($1:$4))>0))*(N(MMULT(N(C$15=$B$2:$E$12),ROW($1:$4))>0))))
This works in my sheet if i correctly understand what you are looking for.
- What do you do with combinations like 9 and 2 vs 10 and 1 vs 3 and 8, all pairs equalling 11, an all occurring in your sample source table?
What, if I may be so bold as to ask, is the point of this exercise? Or is the fact that 1 plus 2 equals 3 as well as occurring three times, coincidental (and confusing) in your example?
Are you also wanting the combinations (however we're to end up defining them) to be accounted for below that diagonal as well as above it?- Yes, please be bold! I'm making a round robin schedule that takes place over many weeks, where there are 4 people assigned to a court and they decide with whom to play. I'm trying to avoid having any 2 people always on the court every week. (I haven't found any on-line schedulers that produce anything viable.)
Any combinations below the diagonal will have already been counted and recorded above the diagonal.
In my example, the circled cell, which is the number of times (or weeks) that 1 and 2 are together , should contain "3". As you point out, the fact that the answer to my example is "3" AND 1+2 equals 3 is merely coincidental (*and* confusing).
As for different pairs summing to the same number (i.e., 9 and 2 and 10 and 1), I am not interested in the sums; I am instead interested in pair itself.
=IF(C$15=$B16,"-----",SUMPRODUCT((N(MMULT(N($B16=$B$2:$E$12),ROW($1:$4))>0))*(N(MMULT(N(C$15=$B$2:$E$12),ROW($1:$4))>0))))
This works in my sheet if i correctly understand what you are looking for.
OliverScheurich - Thanks so much for your solution! I've applied it to my much larger array, done a few spot checks and it looks great!
This solution is for Excel 365. It will produce the entire combination matrix labels and all.
=LET( k, MAX(grid), seq, SEQUENCE(k), CountCombin, LAMBDA(r, c, LET( i, INDEX(seq, r - 1), j, INDEX(seq, c - 1), check_weeks, BYROW(grid, LAMBDA(each_week, AND(OR(each_week = i), OR(each_week = j)))), IF( AND(r = 1, c = 1), "", IF(r = c, "X", IF(AND(r > 1, c = 1), i, IF(AND(r = 1, c > 1), j, SUM(N(check_weeks))))) ) ) ), MAKEARRAY(k + 1, k + 1, CountCombin) )- Wow! I'm beginning to not feel so bad that I couldn't figure it out myself!
Thanks Patrick!
karyn90 Just for fun, here's another possibility using the new PIVOTBY function:
=LET( data, A2:E12, nums, DROP(data,,1), UNPVT, LAMBDA(area,TOCOL(IFS(nums<>"",area),2)), weeks, UNPVT(TAKE(data,,1)), pivot, DROP(PIVOTBY(weeks,UNPVT(nums),weeks,ROWS,0,0,,0),,1), items, TAKE(pivot,1), vals, IFERROR(--DROP(pivot,1),0), pairs, IF(items<>TOCOL(items),MMULT(TRANSPOSE(vals),vals),""), VSTACK(HSTACK("Pairs",items),HSTACK(TOCOL(items),pairs)) )If you don't have PIVOTBY yet, it should be coming very soon...
Yet another approach! I used the FREQUENCY function to assign the numbers to the appropriate bin and an outer product to identify pairs of values.
PAIRSλ = LAMBDA(rowϑ, LET( bins, SEQUENCE(9), freq, FREQUENCY(rowϑ(), bins), SIGN(freq * TOROW(freq)) )); THUNK = LAMBDA(x, LAMBDA(x));The function generates a 10x10 array showing the pairings for week 1. Note that the row data is passed as a thunk. This allows REDUCE to pass the data rows directly, rather than passing the week number and looking up the row (either approach is OK).
= LET( rowsϑ, BYROW(data, THUNK), init, MUNIT(10), result, REDUCE(init, rowsϑ, LAMBDA(acc,rϑ, acc + PAIRSλ(rϑ))), IF(init, "", result) )
