Sorting Data

[K]

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 (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)


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 (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 I want it to be sorted so same names in file paths should be
in same row
Please can any friend can help me on this

 

[GerryGerry]

Sort col A then Col B
then compare B to A and where ever right(colB, len(colA)) <> colA insert a
cell in colB
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 (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)


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 (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 I want it to be sorted so same names in file paths should be
in same row
Please can any friend can help me on this

 

[Patrick Molloy]

in column C use the match function look up B in A

=MATCH(B1:A:A,False)
then sort B:C based off C

 

[GerryGerry]

Try this:-


Sub sorter()
Dim intI As Integer, strColA As String, strColB As String
Range("A1:A" & Cells(Rows.Count, 1).End(xlUp).Row).Sort Range("A1"),
xlAscending
Range("B2:B" & Cells(Rows.Count, 2).End(xlUp).Row).Sort Range("B1"),
xlAscending
For intI = 1 To Range("A:B").SpecialCells(xlCellTypeLastCell).Row
strColA = Trim(Cells(intI, 1).Value)
strColB = Left(Cells(intI, 2).Value, Len(strColA))
If strColB <> strColA Then
If strColB > strColA Then
Cells(intI, 2).Insert
Else
Cells(intI, 1).Insert
End If
End If
Next intI
End Sub

 

[K]

hi gerry, thanks for replying. i tried your macro but it just add row
in between
the column A and B data instead of putting same name file path in same
row. please help

 

[K]

Hi joel thanks for replying. your macro is not working. please see my
excel file in below link in which i explained every thing.
http://www.mediafire.com/?sharekey=68e40059f0508c1b08f8df73f2072ed6e04e75f6e8ebb871

 

[K]

Thanks lot Joel. i tried your code as well and it works superb. you
are the best

 

[K]

Just last question joel that what kind of code i need after line

..Range("B" & RowCount) = c
in your code that i can have unmatched item get listed on the bottom
of column B of sheet2. Because at the moment your macro only putting
the matched itmes in column B of sheet2 but i need that after putting
matched items then macro should list unmatched items on the bottom.

 

[K]

Thanks joel for you help