Comparing two columns of numbers

[jrw]

Hi,

I need to compare numbers in column A with Column B and place the number in column C if it occurs more than once in either column A and B. If no such match can be made, then leave the column blank.

A B C
73 79 79
79 84
76 47
79 79
84 76

Best regards


John

 

[Weaver]

Here's a way to do it in vb.

Assumes the data starts in row 1, but we can change it to allow for headers

Sub test()
    Dim colRange As Range, cel As Range, fr As Long
    fr = 1
    With CreateObject("scripting.dictionary")
        For c = 1 To 2 'you can add more columns if required
            Set colRange = Range(Cells(fr, c), Cells(Rows.Count, c).End(xlUp))
            For Each cel In colRange.Cells
                v = cel.Value
                If WorksheetFunction.CountIf(colRange, v) > 1 Then
                    If Not .exists(v) Then .Add v, 1
                End If
            Next cel
        Next c
        On Error Resume Next
        Cells(fr, c).Resize(.Count) = WorksheetFunction.Transpose(.keys)
    End With
End Sub

 

[Trevor G]

Take a look at this, its done in steps but might help you, sample data

Excel Workbook
	A	B	C	D
1	First	Second		Results
2	73	79
3	79	48
4	67	89
5	12	12
6	234	23
7	34	34
8	87	78
9	10	10
Sheet3

Code

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> checkMatch()<br><SPAN style="color:#00007F">Dim</SPAN> BlankCell <SPAN style="color:#00007F">As</SPAN> Range<br>Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>Range("A2").Activate<br><SPAN style="color:#00007F">Do</SPAN> <SPAN style="color:#00007F">Until</SPAN> ActiveCell.Value = ""<br>ActiveCell.Offset(1, 0).Select<br><SPAN style="color:#00007F">If</SPAN> ActiveCell.Value = ActiveCell.Offset(0, 1).Value <SPAN style="color:#00007F">Then</SPAN><br>    ActiveCell.Offset(0, 3).Value = ActiveCell<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><br><SPAN style="color:#00007F">Loop</SPAN><br>Range("D2").Select<br>   <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> BlankCell <SPAN style="color:#00007F">In</SPAN> Range("D2:D1001")<br>   <SPAN style="color:#00007F">If</SPAN> ActiveCell = "" <SPAN style="color:#00007F">Then</SPAN><br>   ActiveCell.Select<br>    Selection.Delete shift:=xlUp<br><br>   ActiveCell.Offset(1, 0).Select<br>   <SPAN style="color:#00007F">Else</SPAN><br>   <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>   <SPAN style="color:#00007F">Next</SPAN> BlankCell<br>Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br><br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

Results

Excel Workbook
	D
1	Results
2
3	12
4	34
5	10
Sheet3

 

[jrw]

Many thanks guys... Gratefully received...