Pull matching values from two columns in Excel

Question

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?

Answer 1

List from User1 in Column A, List from User2 in Column B, in Column C, start in C2 write the following formula:

=IFERROR(INDEX($B$2:$B$10,SMALL(IF(COUNTIF($A$2:$A$10,$B$2:$B$10)>0,ROW($B$2:$B$10)-1,9999999999),ROW()-1)),"")

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

=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)),"")

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)),"")

Answer 2

A Vlookup (in your matching IDs column) should do the job.

Formula something like this =Vlookup(short list cell,long list,1,FALSE)

False will give NA for no matches, just filter out to get your final list.

Answer 3

VLOOKUP with IFERROR will return values in a new column only when the value exists in both columns. You will then be able to use a data filter to show only the matching. Based on your data, assuming "User 1" in column A and "User 2" in column B:

=IFERROR(VLOOKUP(B2,A:A,1,FALSE),"")

To guarantee results, the table needs to be sorted in ascending order, but since there are only two data points, I only named the table range as consisting of Column A, and the lookup column as 1. Since there is only one data point of lookup, it can deal with the numbers being out of order.

Answer 4

So I'm going to go ahead and answer my own question, since the solution turned out to be as simple as using conditional formatting to highlight duplicates. I had originally wanted to display any matching values in a separate column, but using conditional formatting has ended up being more time-efficient.

Following this tutorial (http://spreadsheetpro.net/comparing-two-columns-unique-values/) allowed me to compare my two columns and apply formatting (in my case, green highlights) to any Visit IDs in the short column that also appeared in the long column.

Answer 5

In case someone looks up this for a similar issue and needs the formulas, you DON'T need array formulas to get the answer as long as you are ok with an extra 2 columns.

1. Make a column that shows if there's a match by a constant indicator. In this example, you could fill in column F or insert a new column G with that matches each E against B exactly. F5, then fill down: =IF(IFERROR(match(E5,B:B,0)),0,1)

2. Make a new column that searches your match column for your indicator. your first number is 1 before your start, such as 0. The formula uses INDIRECT to build the match based on where the previous match stopped, and this formula is repeatable. Example above, filled in J. J4=4, J5 and filldown: =MATCH(1,INDIRECT("Sheet1!F"&F4+1&":$F$30000"),0)+F4

3. Use INDEX to pull the actual data if needed. Example in K5, fill down: =INDEX(E:E,J5)

Clean up the INDEX rows with macros or manually if needed. If you use "1" as the match indicator, macros or a more complex formula can use a SUM to remove the #N/A from excess index lines automatically.