I have two lists of people who attended a certain event. These two lists were generated by two different users. Each visitor has a unique Visitor ID, so I've extracted the Visitor ID column from each list and pasted them side by side in a new sheet. What I need to do is use some sort of formula that will display any Visitor IDs that appear in both columns in a third column. In short, I need to know how many of the same visitors were logged by the two users.
One list is also significantly longer than the other, in case that matters. The IDs in each list also aren't in any sort of numerical order.
Basically, I want to see something like this:
List from User 1 List from User 2 Matching IDs
1129 1781 1129
1200 1743 1776
1525 1129 1200
1695 1350 1525
1928 1776
1972 1644
1776 1200
1297 1525
1980 1153
I've looked up several tutorials but none of them are in the context I need. What's the correct syntax for a formula that would perform this function?
Best Answer
List from User1 in Column A, List from User2 in Column B, in Column C, start in C2 write the following formula:
Array Formula press Ctrl+Shift+Enter at the same time
Drag the formula down it will return the ID if matching (the matching will follow and later the empty) or empty
A2:A10 is the Data in column A without the header,(List user1) change it to your Data references
B2:B10 is the Data in column B without header,(List user2) change it to correspond your Data references
Keep the $ for fixed references
Countif will find the matching ID
If will return the Row number when matching
Index will return the ID corresponding to the smallest row() when dragging the formula
Update corresponding to your references
Because your data start in row 5, I assume your formula will start also in row 5
I changed the formula to correspond to this
=IFERROR(INDEX($E$5:$E$898,SMALL(IF(COUNTIF($C$5:$C$247,$E$5:$E$898)>0,ROW($E$5:$E$898)-4,999999999),ROW(A5)-4)),"")