Hi,

Try this. It assumes there only ONE set of like data i.e. "In the diamond"
does not appear in two separate blocks. If this not the case, the code will
not work correctly!

HTH

Sub MatchData()

Dim ws1 As Worksheet
Dim lastrow As Long, r As Long

Set ws1 = Worksheets("Sheet1")


ws1.Activate
With ws1
lastrow = .Cells(Rows.Count, "A").End(xlUp).Row
Set rng = .Range("A1:A" & lastrow) ' List of column A entries
lastrow = .Cells(Rows.Count, "B").End(xlUp).Row
For r = 1 To lastrow ' loop through column B of Sheet1
res = Application.Match(.Cells(r, "B"), rng, 0) ' find match with
column A
If Not IsError(res) Then ' Match found
If r < res Then
.Cells(res, 2) = .Cells(r, "B") ' place entry in first row for
this match
.Cells(r, "B") = "" ' Clear this entry from column B
End If
End If
Next r
End With

End Sub


"vijaya" wrote:

Hi,
I have some data like this in excel sheet in two columns A and B. I want to
match the items in Column B with column A and if it finds any match has to
place that item in the same column but in the row where it was matched.

A B
In the diamond In the diamond
In the diamond Trench Safety
In the diamond The City
The City
The City
The City
Trench Safety

Result should be like this:

In the diamond In the diamond
In the diamond
In the diamond
The City The City
The City
The City
Trench Safety Trench Safety

It should place in the first cell only though it has multiple matching cells
in the left column as shown above.

Can i do this with some match or sort ..How can i do this