In Excel: Compare two columns and create a new column for the difference

Question

I tried a few solutions from search but nothing conclusive.

I tried this IF statement but when I press 'Enter' nothing happens and the cell is blank...

=IF(ISNA(VLOOKUP(A2,$B$2:$B$4770,1,FALSE)),"not in B","")

This was supposed to use A2 as a reference for looking for a match in Column B, if it is also found in column B it will display the contents in A2 into column C, if not it will display "Not in B".

Attached is a sample of the data. In the real file, Column A has 4,867 items and Column B has 4,770 items. I mirrored this by making the sample data set as a staggered quantity.

Data Sample Set

My List Coworker's List

srbprda4520 PRA00A0182

srbprda4575 PRA00A0186

srbprda4576 PRA01A0067

srbprda4577 PRA01A0137

SRPPRDA4476 bdsrbvraa3509

SRPPRDA4476 pcevoxz002g001

IB1PYOR003A pcevoxz002g003

IB1PYOR003B pcevoxz002g258

IB1PYOR004 pcevoxz002g259

VRA00A0669 wbsvi2410

VRA00A0670 wbsvi2454

VRA96A0122 wbszi2671

VRA96A0123 VRA00A0670

VRA99A0123 PRA00A0048

VRA99A0124 PRA00A0180

PRA00A0031 PRA00A0181

PRD00A0028 WPAAA02A0014

PRA00A0040 WPPRA00A0176

srbprda4576 srbprda4576

PRA00A0180 srbprda4577

PRA00A0181

PRA00A0182

PRA00A0186

PRA01A0067

PRA01A0137

Total in my List: 4876

Total in coworker's list: 4770

Can you guys help me find a method (i.e. vba, =IF, VLOOKUP, etc.) to help me compare these two columns?

The actual file, even if I organize by A-Z, the items do not line up. I tried removing duplicates but that won't work as the rows do not match up.

Thank you in advance.

Answer 1

Your formula doesn't return any result if a match is found. Try this one:

=IF(ISNA(VLOOKUP(A2,$B$2:$B$4770,1,FALSE)),"not in B",VLOOKUP(A2,$B$2:$B$4770,1,FALSE))

Answer 2

I would use an COUNTIF function embedded in an IF function to get your value in column C:

=If(Countif(B:B,A2)>0,A2,"Not in B")

Answer 3

Add a couple of columns (I chose c and m), create a PivotTable from multiple consolidation ranges (as shown here) - I used whole columns:

and you will have a much better overview of the two sets of data than from looking at either alone.