count pairings of strings per-row across multiple columns, no fixed order
I work for a school in the UK and we're trying to resolve some of our elective subject pools for the next school year based on student choices.
We're trying to make a table of subject pairings to help us decide which subjects need to be kept separate in these pools. The attached spreadsheet shows more clearly what is needed.
There's no fixed order the subjects will appear in each column.
Any help on this would be greatly appreciated.. it's beyond my casual knowledge!
Hi,
Can you clarify your expected result for cell K4? I see 5 combinations for History/Geography (2 with History in Column A and Geography in column B, 2 with History in Column A and Geography in column C and 1 with Geography in Column A and History in column B).
This formula in H2, perhaps:
=REPT(SUM(N(MMULT(COUNTIF($G2,$A$2:$D$27)+COUNTIF(H$1,$A$2:$D$27),{1;1;1;1})=2)),COLUMNS($G2:H2)>ROWS(H$1:H2))
and copied right and down to fill the entire table.
Note that the part
{1;1;1;1}
is static, and dependent upon the number of columns being queried (4 in this case). If this number should vary, I can replace the above with a dynamic equivalent if you wish.
Regards
Hi
You are quite right with your observation of K4, I got it wrong - I changed the table on the left part-way through making the example. My apologies.
Secondly.. that's definitely the most crazy excel formula I've ever seen.. and it works! I'm going to do do a few manual spot checks on it but looks good from a cursory glance - I updated the references to work in the full dataset and it's giving me some very realistic totals!
Thank you so much for this, saves HOURS of trying to manually count this stuff up! I knew there'd be a way to make Excel do it, but that's way beyond me.
Regards
You're welcome!
Get back to me if you need any further amendments.
Cheers
Hi,
I think I spoke to soon - it *almost* works perfectly..
One column (Health & Social Care) does not work - it shows all zeroes.. perhaps to do with the ampersand? Can this be rectified or would it be simpler to change the field to not include the &? I don't want to take up too much of your time..
Last edited by summat; 04-02-2020 at 08:38 AM.
Not the ampersand, no, but the fact that you've spelt it Heath & Social Care in cell R1!
Regards
Ok that's embarrassing.. thankfully not a table I came up with though!
I bow to your wisdom (and observational skills) once again!
Thank you!
You're welcome again. Stay safe.
Regards
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks