MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Matching cells


Posted by D. Godfrey on October 26, 2001 8:44 AM

I have a list of numbers in one column and a list of numbers in another column and want to sort the two columns so that identical numbers are lined up on the same row in column 1 and 2 with blank cells in the non-matching cells.


Posted by Leonard Shackleton on October 27, 2001 8:24 AM


Assuming that :-
1. the numbers are in columns A & B, and
2. the numbers in both columns have been sorted in ascending order, and
3. there are no blank cells

Try this :-

Sub Matching_Cells()
Dim rng As Range, cell As Range
Set rng = Range(Range("A1"), Range("A65536").End(xlUp))
Application.ScreenUpdating = False
For Each cell In rng
With cell
If .Offset(0, 1) = "" Then Exit For
If .Value < .Offset(0, 1).Value Then
.Offset(0, 1).Insert
ElseIf .Value > .Offset(0, 1).Value Then
.Insert
End If
End With
Next
End Sub