Comparing in excel

**mediator** · 02-13-2012, 05:44 AM

Hi,
I am new to this forum.
I want to compare two columns, A and B, and print the values of A which are not existing in B to another column C.
e.g

A B C
1 1 2
2 3 4
3 5
4 7
5 9

As you can see, the values in C consists of values from A which are not there in B. So please help me how to do that in excel.

**maczaq** · 02-13-2012, 05:55 AM

hello,

You do not need macro for that
use this code for set formula in C1 and then copy down as much as you will see data in column a and then use autofilter.

Sub Makro1()
    Range("C1").FormulaR1C1 = "=IF(COUNTIF(C[-1],RC[-2])=0,RC[-2],"""")"
End Sub

**mediator** · 02-13-2012, 06:15 AM

It is not working

**maczaq** · 02-13-2012, 06:48 AM

attach your workbook (with dummy data)

**zbor** · 02-13-2012, 07:03 AM

Maybe this:

**smuzoen** · 02-13-2012, 07:11 AM

You can do this with VBA if you want

Sub recipList()
Dim arr1, arr2, recipArr, x, i As Long
ReDim recipArr(i)
arr1 = Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row).Value
arr2 = Range("B1:B" & Cells(Rows.Count, "B").End(xlUp).Row).Value
For Each x In arr1
    If IsError(Application.Match(x, arr2, 0)) Then
        ReDim Preserve recipArr(i)
        recipArr(i) = x
        i = i + 1
    End If
Next
For k = LBound(recipArr) To UBound(recipArr)
Range("C" & k + 1) = recipArr(k)
Next
Range("C:C").SortSpecial SortMethod:=xlPinYin
End Sub

or
Use the following formula in C1 and copy down
=IF(ISERROR(MATCH(A1,$B$1:$B$5,0)),A1,"")

**mediator** · 02-14-2012, 11:18 PM

@maczaq : I can't attach the real file as it contains private data. Thats why I gave the example.
@zbor : Thank you a lot. That is exactly what I wanted.

But I had created a c++ program to do the same.

@smuzoen : I don't have the VB compiler. But still thank you

Thank you all for your valuable time.

**mediator** · 02-15-2012, 02:59 PM

@zbor : I was thinking that can we have something create something like this

A B C D E F G H
1 Parrot 3 crow 2 Pideon 7 Tiger
2 pidgeon 7 Tiger 4 peacock 11 cat
3 crow 1 Parrot 5 eagle 21 mouse
4 peacock 11 cat 6 vulture
5 eagle 21 mouse
6 vulture

As we can see, we have 8 columns. Columns, A,B,C,D are input columns and the rest are output. Here A and B, C and D are mappings or pairs. Only A and C are to be compared, as I asked in my first post, and entries of A which are unavailable in C are to be output in E. But this time, the corresponding mapping of A i.e values of B are to be output in F also.

Similarly, A and C are compared again and the entries of C which are unavailable in A are to be output in G. Also, the corresponding mapping of C i.e values of D are to be output in H.

Can this be done? Anyone?