Match and sort

[Guest]

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

 

[Guest]

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

 

[Guest]

Hi,
The data has been clubbed on posting the message.
It should be something liek this:

Original List:
A B
In the diamond In the diamond
In the diamond The City
In the diamond Trench
The City
The City
Trench


Result :

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



Data is in cells of A and B columns

 

[Guest]

Hi,

The following approach is a workaround. It is not elegant enough to move
the items of Column B to appropriate rows in the SAME sheet (you would need a
vba code for that).

The formula suggested here assumes that each group of items (in Column
A)occurs only once as a block (i.e., "In the diamond" will not appear again
somewhere down, say below "The City").
Furthermore, the data should not start at Row 1 (you can have column headers
in Row 1). Let's suppose that your data are in Sheet1 (say in A2:A201 and
B2:B11).

In another worksheet, say Sheet 2,
Copy column A of Sheet 1.
In B2 (of Sheet2), enter the following formula and drag down the formula to
B201.

=IF(OR(A2=A1,A2="",ISERROR(MATCH(A2,Sheet1!$B$2:$B$11,0))),"",A2)

Sheet2 is still linked to Sheet1. So any change in Sheet1 will reflect in
Sheet2 as well. If you want to make Sheet2 independent, select Column B of
Sheet2 --> Copy --> Paste Special -> Values. After this you could even
delete Sheet1.

Regards,
B. R. Ramachandran