Prev: How to re-enable events after a VB error in Excel?
Next: Item Count vs Item Cost
From: K on 29 Oct 2009 05:06
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
From: GerryGerry on 29 Oct 2009 06:01
Sort col A then Col B
then compare B to A and where ever right(colB, len(colA)) <> colA insert a
cell in colB
"K" <kamranr1982(a)yahoo.co.uk> wrote in message
news:41209adf-0834-4615-bf84-6eac0a17a4f6(a)v30g2000yqm.googlegroups.com...
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


From: Patrick Molloy on 29 Oct 2009 06:14
in column C use the match function look up B in A

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



"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 (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
> .
>
From: GerryGerry on 29 Oct 2009 08:49
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

"GerryGerry" <Gerry(a)Gerry.anon> wrote in message
news:zJdGm.6331$k31.1844(a)newsfe04.ams2...
> Sort col A then Col B
> then compare B to A and where ever right(colB, len(colA)) <> colA insert a
> cell in colB
> "K" <kamranr1982(a)yahoo.co.uk> wrote in message
> news:41209adf-0834-4615-bf84-6eac0a17a4f6(a)v30g2000yqm.googlegroups.com...
> 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
>


From: K on 30 Oct 2009 04:11
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
 |  Next  |  Last
Pages: 1 2 3
Prev: How to re-enable events after a VB error in Excel?
Next: Item Count vs Item Cost