PDA

View Full Version : How do i match up two columns of data?



ads_3131
10-21-2008, 05:08 AM
Hello, i have tried to do this myself and having no luck at all. I have tried filtering, sorting etc.. I have two columns of data, each cell within a column has some words and then a email address. I am wanting to pair up the email address to each other, can anyone help me with this, thanks

georgiboy
10-21-2008, 08:29 AM
Can you post an example spreadsheet?

ads_3131
10-22-2008, 12:52 AM
I Have now uploaded a example of which i need help with, thanks

mdmackillop
10-22-2008, 04:44 AM
This assumes there is a pair for every item.

Sub PairUp()
Dim Log1 As Range, Log2 As Range, cel As Range
Dim Txt As String
Columns("B:B").Insert
Set Log1 = Range(Cells(5, 1), Cells(5, 1).End(xlDown))
Set Log2 = Log1.Offset(, 2)
For Each cel In Log1
cel.Offset(, 1) = Trim(Split(cel, ":")(UBound(Split(cel, ":"))))
Next
For Each cel In Log2
Txt = Trim(Split(cel, "=")(UBound(Split(cel, "="))))
cel.Offset(, 1) = Trim(Application.WorksheetFunction.Substitute(Txt, "'", ""))
Next
Log1.Resize(, 2).Sort Key1:=Range("B5"), Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Log2.Resize(, 2).Sort Key1:=Range("D5"), Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Columns("B:B").Delete
Columns("C:C").Delete
End Sub