I got data in column A and B like see below. As you can see below I
got file paths listed in both columns.

A B……col
C:\David Terry C:\Dean Smith
C:\John Owen C:\Michael Ja
C:\Michael Ja C:\Daivd Terry
C:\Ali Smith C:\John Owen
C:\Karen Seal

I need macro which should sort column B list according to column A
list and results should look like as shown below


A B……col
C:\David Terry C:\David Terry
C:\John Owen C:\John Owen
C:\Michael Ja C:\Michael Ja
C:\Ali Smith
C:\Dean Smith
C:\Karen Seal

Please can any friend can help me on this

On Tue, 27 Oct 2009 09:43:34 -0700 (PDT), K
wrote:

I got data in column A and B like see below. As you can see below I
got file paths listed in both columns.

A B……col
C:\David Terry C:\Dean Smith
C:\John Owen C:\Michael Ja
C:\Michael Ja C:\Daivd Terry
C:\Ali Smith C:\John Owen
C:\Karen Seal

I need macro which should sort column B list according to column A
list and results should look like as shown below


A B……col
C:\David Terry C:\David Terry
C:\John Owen C:\John Owen
C:\Michael Ja C:\Michael Ja
C:\Ali Smith
C:\Dean Smith
C:\Karen Seal

Please can any friend can help me on this


Assuming that there are no gaps in the original tables, try the
following macro

Sub K()
amin = 1
amax = Cells(amin, "A").End(xlDown).Row
bmin = 1
bmax = Cells(bmin, "B").End(xlDown).Row
Dim result() As String
ReDim result(amax + bmax)
For b = bmin To bmax
For a = amin To amax
If Cells(b, "B") = Cells(a, "A") Then
result(a) = Cells(a, "A")
Cells(b, "B") = ""
End If
Next a
Next b
For b = bmin To bmax
If Not Cells(b, "B") = "" Then
amax = amax + 1
result(amax) = Cells(b, "B")
End If
Next b
For b = 1 To amax
Cells(b, "B") = result(b)
Next b
End Sub

Hope this helps / Lars-Åke

Hi Lars thank for replying. your macro work brilliant. i am bit
curious that how you created such a clever macro. Is it possible for
you to explain your macro to me bit in detail just for my knowledge.
many thanks

On Tue, 27 Oct 2009 14:56:13 -0700 (PDT), K
wrote:


Hi Lars thank for replying. your macro work brilliant. i am bit
curious that how you created such a clever macro. Is it possible for
you to explain your macro to me bit in detail just for my knowledge.
many thanks


The macro consists of three parts; I, II, and III

Part I: A double loop where each entry in table b is either
- copied to the result vector and then cleared from table b if it is
also found in table a
- or left in table b if there is no matching entry in table a

Part II: A single loop where the entries of table b that has not been
cleared, ie were not also found in table a is copied to the end of the
result vector. The end of the result vector is gradually increasing.

Part III: A single loop where the result vector is stored back as the
new table b which is the requested result of the macro.


The part II loop could be avoided by adding the corresponding code to
the outer loop of part I and the inner loop of part I could be exited
when a match has been found, but unless the size of the tables are not
tens of thousands of rows there is not much time to be gained by those
optimizations.

That's all there is to it. Hope that makes sence. / Lars-Åke

Thanks lot lars for the explanion. just last question. what changes
can be done in your macro if i have data like below


A B……col
C:\David Terry C:\Dean Smith (MC) - 23
C:\John Owen C:\Michael Ja - 778
C:\Michael Ja C:\Daivd Terry (ds)
C:\Ali Smith C:\John Owen - x23
C:\Karen Seal - (CC)


and i need result like below


A B……col
C:\David Terry C:\David Terry (ds)
C:\John Owen C:\John Owen - x23
C:\Michael Ja C:\Michael Ja - 778
C:\Ali Smith
C:\Dean Smith (MC) - 23
C:\Karen Seal - (CC)


basically same name file path should be in same row

On Thu, 29 Oct 2009 03:51:44 -0700 (PDT), K
wrote:


Thanks lot lars for the explanion. just last question. what changes
can be done in your macro if i have data like below


A B……col
C:\David Terry C:\Dean Smith (MC) - 23
C:\John Owen C:\Michael Ja - 778
C:\Michael Ja C:\Daivd Terry (ds)
C:\Ali Smith C:\John Owen - x23
C:\Karen Seal - (CC)


and i need result like below


A B……col
C:\David Terry C:\David Terry (ds)
C:\John Owen C:\John Owen - x23
C:\Michael Ja C:\Michael Ja - 778
C:\Ali Smith
C:\Dean Smith (MC) - 23
C:\Karen Seal - (CC)


basically same name file path should be in same row


Try changeing these two lines of code

If Cells(b, "B") = Cells(a, "A") Then
result(a) = cells(a,"A")

to these two lines

If InStr(Cells(b, "B"), Cells(a, "A")) 0 Then
result(a) = Cells(b, "B")

The exact comparison, equality, is changed to just see if the entry
in table a is the same as the start/beginning of the entry in table b.
The result is taken from table b. (In the previous version it was not
important from which table the result was taken as the table entries
were equal.)

Hope this helps / Lars-Åke