Marco to compare 2 columns

[rathna_iit]

Hi is there any marco to compare column A data with column B data .

if Column A data does not appear in column B then

1) that data should be displayed in column C

2) and if present should be displayed as "OK"



Ex : Column A Column B Column C

1 1

2 2

3 3 OK

4 4

5 5 OK





Many thanks in advance for helping me out thank you

 

[jbeaucaire]

You don't really need a macro for something as simple as comparing A1 to B1, as your example demonstrates. In C1 enter this formula:

=IF(A1=B1,"OK",A1)

Now, if you mean the value in A1 might be ANYWHERE in column B, not just right next to it, then this in C1:

=IF(COUNTIF(B:B,A1)>0,"OK",A1)

Pick the formula for you, enter it in C1 (or adjust it to start in C2), and then copy it down the whole data set.

 

[AndyY]

Hi,
you might not need a macro for this, if I'm reading your query right you could just enter:
=IF(B2=A2,"OK",B2)
in col 3 & copy and paste.
HTH
Andy

 

[VoG]

Hello and welcome to MrExcel.

Is this what you mean?

Sub Cpr()
Dim LR As Long, i As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = 1 To LR
    With Range("A" & i)
        If IsNumeric(Application.Match(.Value, Columns("B"), 0)) Then
            .Offset(, 2).Value = "OK"
        Else
            .Offset(, 2).Value = .Value
        End If
    End With
Next i
End Sub

After macro:

<b>Sheet3</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td style=" border-width:3px; border-styleutset;border-color:#f0f0f0; ">A</td><td style=" border-width:3px; border-styleutset;border-color:#f0f0f0; ">B</td><td style=" border-width:3px; border-styleutset;border-color:#f0f0f0; ">C</td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >1</td><td style="text-align:right; ">5</td><td style="text-align:right; ">32</td><td >OK</td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >2</td><td style="text-align:right; ">4</td><td style="text-align:right; ">31</td><td style="text-align:right; ">4</td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >3</td><td style="text-align:right; ">30</td><td style="text-align:right; ">25</td><td style="text-align:right; ">30</td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >4</td><td style="text-align:right; ">47</td><td style="text-align:right; ">10</td><td style="text-align:right; ">47</td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >5</td><td style="text-align:right; ">6</td><td style="text-align:right; ">34</td><td style="text-align:right; ">6</td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >6</td><td style="text-align:right; ">26</td><td style="text-align:right; ">40</td><td >OK</td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >7</td><td style="text-align:right; ">46</td><td style="text-align:right; ">41</td><td style="text-align:right; ">46</td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >8</td><td style="text-align:right; ">12</td><td style="text-align:right; ">25</td><td style="text-align:right; ">12</td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >9</td><td style="text-align:right; ">19</td><td style="text-align:right; ">25</td><td style="text-align:right; ">19</td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >10</td><td style="text-align:right; ">21</td><td style="text-align:right; ">26</td><td style="text-align:right; ">21</td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >11</td><td style="text-align:right; ">30</td><td style="text-align:right; ">36</td><td style="text-align:right; ">30</td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >12</td><td style="text-align:right; ">49</td><td style="text-align:right; ">44</td><td >OK</td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >13</td><td style="text-align:right; ">28</td><td style="text-align:right; ">7</td><td style="text-align:right; ">28</td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >14</td><td style="text-align:right; ">47</td><td style="text-align:right; ">48</td><td style="text-align:right; ">47</td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >15</td><td style="text-align:right; ">23</td><td style="text-align:right; ">14</td><td style="text-align:right; ">23</td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >16</td><td style="text-align:right; ">23</td><td style="text-align:right; ">7</td><td style="text-align:right; ">23</td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >17</td><td style="text-align:right; ">27</td><td style="text-align:right; ">39</td><td style="text-align:right; ">27</td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >18</td><td style="text-align:right; ">29</td><td style="text-align:right; ">49</td><td style="text-align:right; ">29</td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >19</td><td style="text-align:right; ">36</td><td style="text-align:right; ">5</td><td >OK</td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >20</td><td style="text-align:right; ">19</td><td style="text-align:right; ">41</td><td style="text-align:right; ">19</td></tr></table> <br />Excel tables to the web - Excel Jeanie Html 4

 

[rathna_iit]

Thanks a lot MR excel Mvp for the Marco for a quick and correct response

if the data in column A is alpha numeric

now if i want to compare a single column A with multiple columns B and C

what changes are to be done in the marco

 

[VoG]

Try this - the number or OK will be placed in column D

Sub Cpr()
Dim LR As Long, i As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = 1 To LR
    With Range("A" & i)
        If IsNumeric(Application.Match(.Value, Columns("B"), 0)) Or _
            IsNumeric(Application.Match(.Value, Columns("C"), 0)) Then
            .Offset(, 3).Value = "OK"
        Else
            .Offset(, 3).Value = .Value
        End If
    End With
Next i
End Sub

 

[rathna_iit]

Thanks a lot Mr MVP have nice day will try the code