# Solved: Comparing two columns in excel



## nocll (Jun 4, 2007)

Hi,

I have a list of customer IDs in two columns. I want to generate the customer IDs which are missing in either of the two columns in a third column. What formula can be applied here?

Thanks in advance


----------



## Debian (Apr 1, 2008)

=IF(C7<>D7, "Missing", "OK")

C7 Being the First Cell
D7 Being the Missing Cell
Results where your formula is written.

 Adam


----------



## DonChoudhry (Dec 1, 2003)

Thanks but this formula has one drawback. I have both the columns in order. When I put the forumula against the rows in third column, If one row of 1st and 2nd column mismatchs the rest of the rows starts mismatching because the order is distubed. I think a formula is needed that searchs the whole column against each row in the other column. Help


----------



## Keebellah (Mar 27, 2008)

Hi,

I have added an excel sheet as attachment
It contains the following code:

Function ChkMissing()
' trow will be the row in column 3 where the missing value will be placed
trow = 1
' first run through all values in column1 and see if it is present in column 2
' if not (false) then add the value to column 3 and add a row (trow = trow + 1)
Range("C2:C200").ClearContents
For xrow = 2 To Range("A65536").End(xlUp).Row
If Column2(Cells(xrow, 1).Value) = False Then
trow = trow + 1
Cells(trow, 3).Value = Cells(xrow, 1).Value
End If
Next xrow
'secondly run through the list in column2 and see if it is present in column1
' if not (false) then add the value to column 3 and add a row (trow = trow + 1)
For xrow = 2 To Range("B65536").End(xlUp).Row
If Column1(Cells(xrow, 2).Value) = False Then
trow = trow + 1
Cells(trow, 3).Value = Cells(xrow, 2).Value
End If
Next xrow
MsgBox trow - 1 & " values foound"
End Function
Function Column1(tString As Variant) As Boolean
For xrow = 2 To Range("A65536").End(xlUp).Row
If Cells(xrow, 1).Value = tString Then Column1 = True
Next xrow
End Function
Function Column2(tString As Variant) As Boolean
For xrow = 2 To Range("B65536").End(xlUp).Row
If Cells(xrow, 2).Value = tString Then Column2 = True
Next xrow
End Function

The idea is have cloumn1 A filled with values, Comlumn B with values,
Run ChkMissing and you will get a list in column3

Try it, I hope it does what you want

Cheers

Hans


----------



## nocll (Jun 4, 2007)

Worked like a charm. Thanks allot


----------

