First of all, the LET function is like coding, where you can compute something
once and assign it to a variable. So the LET function allows this. So the outer skeleton of the formula is of the form:
LET(
____variableName , variableValue,
____variableName , variableValue,
____variableName , variableValue,
____.
____.
____.
____variableName , variableValue,
result
)
And the order in which you substitute variableNames matters, so if one variable will use another variable, define the variables that are used in other variables before the variables that use them.
Line 1:
Excel Formula:
k_1,MID(A2,SEQUENCE(LEN(A2)),1),k_2,MID(B2,SEQUENCE(LEN(B2)),1),k_3,MID(C2,SEQUENCE(LEN(C2)),1),
First of call, SEQUENCE is going to give you an array of numbers, for example. (You don't have to fill it down. Excel "spills" it for you.)
I'm sure you know about the string functions MID, LEFT, and RIGHT. So
MID(A2,SEQUENCE(LEN(A2)),1) will just split up all characters of a cell into an array of characters. (LEN(A2) = number of characters in the cell). For example,
| Book1.xlsx |
|---|
|
|---|
| F | G |
|---|
| 2 | abc 123 | a |
|---|
| 3 | | b |
|---|
| 4 | | c |
|---|
| 5 | | |
|---|
| 6 | | 1 |
|---|
| 7 | | 2 |
|---|
| 8 | | 3 |
|---|
|
|---|
Excel Formula:
k1k2,IFERROR(MMULT(IFERROR(SEARCH(k_1,TRANSPOSE(k_2)),0),0*SEQUENCE(ROWS(k_2))+1),0),
k2k3,IFERROR(MMULT(IFERROR(SEARCH(k_2,TRANSPOSE(k_3)),0),0*SEQUENCE(ROWS(k_3))+1),0),
k3k1,IFERROR(MMULT(IFERROR(SEARCH(k_3,TRANSPOSE(k_1)),0),0*SEQUENCE(ROWS(k_1))+1),0),
Begin with the inner part,
SEARCH(k_1,TRANSPOSE(k_2)),0). It gives a 2D array which you read as "see what characters of k_1 are in k_2 (if any).
View attachment 75172
This is what it tested:
View attachment 75173
But the #VALUE! is ugly. If we convert them to 0s,
View attachment 75174
But this is a 2D matrix. We want to just get a 1D array that says if each of the characters of k_1 (
abc in this case) match any of the characters of k_2. We can use multiplication of matrices to do this:
View attachment 75175
This is the result we get from the multiplication.
View attachment 75176
(Imagine that this result is just "pushing" all 1's to the left into the same column.)
View attachment 75177
So this result represents the comparison:
View attachment 75178
Line 5:
View attachment 75179
This just collects all of those 1D arrays and puts them into one (horizontal) string. I put a (temporary) comma to separate the character from the 0 or 1. Just for visual purposes. 1 means there was a match, 0 means there wasn't a match.
Line 6:
This step was easy. I just converted the above string into an array, because I want to
remove the characters that didn't match. (In the next step/line.)
View attachment 75181
Line 7:
I begin by removing the (temporary) commas. Again, nothing new here. 0 represents unmatched. But #'s>0 represent matched.
View attachment 75182
But if they are > 0, I want to put the actual character (see
Line 6's image), but if they are not a match, I want to make them a blank.
View attachment 75183
Line 8:
Excel Formula:
IFERROR(CONCAT(UNIQUE(TRANSPOSE(FILTER(incommon,incommon<>"")))),"nothing in common")
This one is self-explantory. Starting from the inner part of the line, we:
- Filter out blanks.
View attachment 75184
- I transposed it, because for UNIQUE to delete duplicates. And UNIQUE doesn't remove duplicates for this horizontal array unless you pass 1 as a second parameter. UNIQUE(arr,1). (I should have did this to shorten the formula, but I just transposed it instead.)
View attachment 75187
- Filter out duplicates:
View attachment 75188
- Convert the array to a string.
View attachment 75189
- But if there are no matches, you will get an error:
View attachment 75190
So just add IFFERROR, and say whatever you want it to say if there are no matches
View attachment 75191
And so therefore, the formula can be shortened to:
