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).
This is what it tested:
But the #VALUE! is ugly. If we convert them to 0s,
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:
This is the result we get from the multiplication.
(Imagine that this result is just "pushing" all 1's to the left into the same column.)
So this result represents the comparison:
Line 5:
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.)
Line 7:
I begin by removing the (temporary) commas. Again, nothing new here. 0 represents unmatched. But #'s>0 represent matched.
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.
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.
- 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.)
- Filter out duplicates:
- Convert the array to a string.
- But if there are no matches, you will get an error:
So just add IFFERROR, and say whatever you want it to say if there are no matches
And so therefore, the formula can be shortened to:
).
