All geek questions in one place

Excel - need to find if any of column A is found in column B

Basically, I have 2 lists of email addresses in Excell ...

Column A = Holds over 2051 rows of email addresses Column B = Holds about 1994 rows of email addresses

I need to find out which email addresses in column A are not found in column B, and it is desirable to output the results to a new sheet or column C.

Then after that I need to find which email addresses in column B are not found in column A (if any) and display this list in a new sheet or column D.

How can i do this?

+4
source share
3 answers

In a new sheet or column C, use a combination of VLOOKUP () and IFERROR () and drag this formula for each row A.

=IF(ISERROR(VLOOKUP(A1, $B$1:$B$1995, 1, 0)), A1 & " NOT FOUND IN COLUMN B", "FOUND IN B")

This will return two different messages, depending on whether an email was found in B.

+6
source

Why not copy the data from column B to the end of column A? Then set the conditional formatting for the column to select all elements whose count exceeds one. Use this formula " =countif($A$1:A1,A1)>1 " without quotes. Make sure that the entire column is selected.

Another way to maintain data sharing. In column C, use a formula like =IF(ISERROR(VLOOKUP(A1,$B$1:$B$100,1,0)),A1,"") ; change the ranges to fit the data ranges. Then fill in the formula to the end of the data in column A. To fill out, select the desired range and press "Cntl + D". Repeat this for column D, but replace the links A and B in the formula and fill them to the bottom of the data in column B. This will result in the data in columns C and D that enumerate unique values. Copy and paste these values, be sure to paste as values ​​if the default paste is used. Excel inserts formulas, not data, into another set of columns (E and F) or the same columns, and then sorts each column to exclude spaces.

+1
source

You can use this Sub ..

 Sub CrossCheck() Dim LastA, LastB, r As Range Dim x, Cn, Dn As Long Set LastA = Range("A65536").End(xlUp) Set LastB = Range("B65536").End(xlUp) Cn = 1 Dn = 1 For x = 1 To LastB.Row Set r = Columns("B").Find(Cells(x, 1), , xlValues, xlWhole) If r Is Nothing Then Cells(Cn, 3) = Cells(x, 1) Cn = Cn + 1 End If Next For x = 1 To LastA.Row Set r = Columns("A").Find(Cells(x, 2), , xlValues, xlWhole) If r Is Nothing Then Cells(Dn, 4) = Cells(x, 2) Dn = Dn + 1 End If Next End Sub

Hope this help!

0
source

Source: https://habr.com/ru/post/1487933/

More articles:


All Articles