I'm attempting to generate a matrix of people who are in common with each other. In one worksheet, I have the matrix itself, and in the other, I have the raw data. The output needed is to put an "x" in the cell if there's a match, or nothing if there isn't a match.
Here is a sample of the matrix worksheet, where the names going down in column A are the same names going across in row 1. Names are in "firstname middlename lastname" format (usually):
A | B | C | D | E
------------------------------------
1| | Name1 | Name2 | Name3 | Name4
2| Name1 | | | |
3| Name2 | | | |
4| Name3 | | | |
5| Name4 | | | |
Here is a sample of the raw data worksheet. Column A is a sorted list of names, and column B is who they are on common with. There are hundreds of names in the list:
A | B
------------------------------------
1| Name1 | Name33
2| Name1 | Name5
3| Name1 | Name21
4| Name2 | Name1
5| Name2 | Name92
...
The formula I need to write goes into the blank cells in the matrix worksheet. Here is an example of the final output (though there are many more names in the actual matrix):
A | B | C | D | E
------------------------------------
1| | Name1 | Name2 | Name3 | Name4
2| Name1 | | | x | x
3| Name2 | x | | x |
4| Name3 | x | x | |
5| Name4 | | | x |
Here's how far I've gotten before hitting a brick wall. I attempted to write a VLOOKUP formula to do this, but it wouldn't work if I just told it to look in the entire range of the raw data worksheet because it would pick up matches from the wrong people. I only want it to match against the part of column B that had the name in Column A for that cell.
So, the closest I could get was to put in the specific range from the raw data file for the name listed on that particular row in the matrix. For instance, I manually looked up Name1 in column A of the raw data file and found it existed in rows 39-62. So, for Name1, if one of the names going across row 1 of the matrix is found between rows 39-62 of column B in Raw_Data, mark an "x":
=IF(IFNA(VLOOKUP(B$1,Raw_Data!$B39:$B62,1,FALSE),"")="","","x")
This would work only for one row in my matrix sheet. Then, when going to the next row, I'd have to manually look up the Raw_Data rows for that person and put it in the formula. Granted, it saved a little time, but not nearly as much as if I could get this formula to work across the whole matrix without any changes.
You could use this COUNTIFS formula in B2 copied across and down
=IF(SUMPRODUCT(COUNTIFS(Raw_Data!$A:$A,IF({1,0},B$1,$A2),Raw_Data!$B:$B,IF({0,1},B$1,$A2))),"X","")
COUNTIFS counts when the B1 name is in col A of Raw_data and A2 name in Col B....or vice versa. If the count if > 0 then there's a match and you get "x"
I'm assuming that you always want the "x"s to appear in both boxes.....but in your example you only show the names in one, e.g. Name2/Name1 is shown with "x" but not the other way round - if that's the way it should be then it's a simpler COUNTIFS like this:
=IF(COUNTIFS(Raw_Data!$A:$A,$A2,Raw_Data!$B:$B,B$1),"X","")