Let's say that I have 4 colums, and two of them contain mostly the same information. For instance:

Name____Received___Name____Sent
A_______04/01/08____C_____04/05/08
B_______04/02/08____E_____04/05/08
C_______04/02/08____A_____04/02/08
D_______04/03/08
E_______04/09/08

The first two columns are inported from a list, and the last two columns are imported from a different list. Usually, the first list will contain more information.

What I'd like to be able to do is sort the list so that all the names in the first column match the names in the second columns, and the received and sent dates match accordingly. (The ultimate goal is to calculate the days between sent and received which isnt a problem once I can get it sorted).

Essentually, the first row would show as:
A____04/01/08____A_____04/02/08
and so forth with the names matching and the dates following.

If I sort numerically, since there is such a large amount of names, the names will not match up since one column has more names then the other.

How can I sort the colums so that the names coincide with each other and the dates follow the correct name?

Thanks for all your help.

Use VBA to:

1 - Sort both Columns
2 - Check the first value in the shorter Name column to see if it matches the value from the longer Name column in the same row.
3 - If it doesn't match, insert a cell to move it down and check again. (Use an If-Then Loop)
4 - Keep inserting cells until the Names match.
5 - Jump out of the loop and check the next value doing the same thing.

Eventually you will have inserted cells between the values in the short list so that all the Names line up.

QED

What I said earlier, but in a different language...

Sub SortMatch()
'Sort Columns A & B then Sort Columns C & D
Columns("A:B").Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlYes
Columns("C:D").Sort Key1:=Range("C1"), Order1:=xlAscending, Header:=xlYes
'Determine how long the Long List is
LastRow = Range("A" & Rows.Count).End(xlUp).Row
'Loop through the short list
'Compare the value in C to the value in A
'If they don't match, insert cells in Columns C & D
'When they match, stop checking
For NxtName = 2 To LastRow
If Range("C" & NxtName) <> Range("A" & NxtName) Then
Range("C" & NxtName & ":D" & NxtName).Insert shift:=xlDown
Else: End If
Next
End Sub

This is a job for a database application.
First data file 'sent' indexed by name. Second data file 'received' indexed by name. Using name in second file find name in first file. Find date difference of first date minus second date. You don't have to insert any filler to align the data in the rows.

Of course, there are a myriad of ways to accomplish your goal.

If all you really want it to do is calculate the difference in the dates, and not really match everything up as you requested, it can be done with a formula, leaving the data right where it is.

Assuming the columns in your example are A:D, put this in E2 and drag it down. (Make sure column E is formatted as Number)

=VLOOKUP(C2,$A$2:$B$6,2,0)-D2

This will lookup the values from your short Name column in the long Name column and subtract corresponding dates.

To get even fancier, add text to the formula:

="Received " &VLOOKUP(C2,$A$2:$B$6,2,0)-D2& " days after it was sent"

BTW...did you know that 2 out of your 3 items were received before they were sent?

Thanks for all the great help.

I will be giving it a try once I have access to the data again.

Sorry for the differences in the data date. I made a quick example list and didn't check it.

Thanks again.

I wanted to thank everyone for their responses. It has helped alot. I am not too familar with VBA scripting yet, but I will def be giving it a try.

Here is what I went with:

=IF((NETWORKDAYS(VLOOKUP(E2,$B$2:$C$2426,2,0),F2,$M$3:$M$13)-1)<0,0,NETWORKDAYS(VLOOKUP(E2,$B$2:$C$2426,2,0),F2,$M$3:$M$13)-1)

This is going to calculate my network days so that weekends and holidays aren't included. My holidays are listed in M3:M13.

Column B is the name of the received and column C is the date that it was received.

Column E is the name of the sent and Column F is the date that it was sent.
(Think of a bill that was received, and then a payment was sent).

The IF statement is there incase there was a data error to make sure there were no negatives.

Thanks again for the help. Hopefully I can get the VBA to work :)

I have a similar question.

using the example table SumBlndDude gave in his first post, I need name "C" to sort itself and the column beside it to sort itself next to name "C" in the other column. But, I'm having the same problem SumBlndDude was initially having, given that there are fewer names in my second set of data than in my first.

Manually inserting cells until they match up isn't a practical option given the huge number of cells I would have to insert.

What should i do???