Combining rows with similar data, not exact. Help!

[Tony2009]

Okay, I've been searching all over for a solution to this and I have come up with nothing that I can get to line up with what I need to do.

Problem:

I have an excel sheet that has around 80,000 employee records in it. The fields in the columns are Employee Number, Person, Assignment, Full Name, Start Date, Position(unique to person), Job (general), Location, Organization, Assignment Start Date, and Assignment End Date.

The bad thing: This data was pulled from some sort of database that we have here. Many of the records are duplicated on every field except for the Assignment Start and End Date. For Example:

11, 102, John Doe, Teacher.1256, Teacher, MN, School X, 7/16/2008, 11/15/2010
11, 102, John Doe, Teacher.1256, Teacher, MN, School X, 11/16/2010, 6/30/2011

The record being repeated is more than likely due to another field in the database that was not recorded such as a supervisor change. There can also be an unlimited number of occurrences. Also, sometimes one of the fields may have changed and so we would like to keep the records separate, for example:

11, 102, John Doe, TeacherAssistant.1256, TA, MN, School X, 7/16/2008, 11/15/2010
11, 102, John Doe, Teacher.1256, Teacher, MN, School X, 11/16/2010, 6/30/2011
11, 102, John Doe, TeacherAssistant.1256, TA, MN, School X, 7/1/2011, 6/30/2012

I'm not sure what this is going to take, but any help is appreciated. I'd imagine it would take some sort of loop to go through the entire table to lookup values over and over, but since I don't know VBA I'm out of luck there.

[wallyeye]

What do you want to accomplish here? Do you want to pull the latest record of each occurrence? Do you want all unique entries?

[Tony2009]

Wallyeye,

I want to combine the duplicate records as indicated in the example. All of the information in the cells match except for the Assignment End Date and Assignment Start date. Where this is the case, I would like to change the start date to the earliest of the two, and the end date to the later in the two entries.

Example.xlsx

[nilem]

try this

Sub ertert()
Dim x, y(), i&, j&, k&, s$, ubx&
x = Range("A2:K" & Cells(Rows.Count, 1).End(xlUp).Row).Value
ubx = UBound(x, 2): ReDim y(1 To UBound(x, 1), 1 To ubx)

With CreateObject("Scripting.Dictionary")
    .CompareMode = 1
    For i = 1 To UBound(x)
        For k = 1 To ubx - 2: s = s & "|" & x(i, k): Next k
        If Not .Exists(s) Then
            j = j + 1: .Item(s) = j
            For k = 1 To ubx: y(j, k) = x(i, k): Next k
        Else
            k = .Item(s)
            If x(i, ubx - 1) < y(k, ubx - 1) Then y(k, ubx - 1) = x(i, ubx - 1)
            If x(i, ubx) > y(k, ubx) Then y(k, ubx) = x(i, ubx)
        End If
        s = vbNullString
    Next i
End With

With Sheets("Sheet2")
    .UsedRange.ClearContents
    .Range("A2").Resize(j, ubx).Value = y(): .Activate
End With
End Sub

[wallyeye]

OK, one more question. In your original post, second sample, you had an employee with a different assignment (2) in between two records (1 and 3) with the same assignment. If you combine 1 and 3 into one record, it will look like the employee was in the 1st/3ird assignment for a period of time that he actually had a different assignment (2nd):

11, 102, John Doe, TeacherAssistant.1256, TA, MN, School X, 7/16/2008, 11/15/2010
11, 102, John Doe, Teacher.1256, Teacher, MN, School X, 11/16/2010, 6/30/2011
11, 102, John Doe, TeacherAssistant.1256, TA, MN, School X, 7/1/2011, 6/30/2012

to

11, 102, John Doe, TeacherAssistant.1256, TA, MN, School X, 7/16/2008, 6/30/2012
11, 102, John Doe, Teacher.1256, Teacher, MN, School X, 11/16/2010, 6/30/2011

Do you want this representation, or would you rather have them sequential (in this case all three records dsiplayed)? Your sample worksheet is a bit cleaner, it has the same person in the same assignment for two consecutive periods of time, it would make more sense to combine these.

[Tony2009]

OK, one more question. In your original post, second sample, you had an employee with a different assignment (2) in between two records (1 and 3) with the same assignment. If you combine 1 and 3 into one record, it will look like the employee was in the 1st/3ird assignment for a period of time that he actually had a different assignment (2nd):

11, 102, John Doe, TeacherAssistant.1256, TA, MN, School X, 7/16/2008, 11/15/2010
11, 102, John Doe, Teacher.1256, Teacher, MN, School X, 11/16/2010, 6/30/2011
11, 102, John Doe, TeacherAssistant.1256, TA, MN, School X, 7/1/2011, 6/30/2012

to

11, 102, John Doe, TeacherAssistant.1256, TA, MN, School X, 7/16/2008, 6/30/2012
11, 102, John Doe, Teacher.1256, Teacher, MN, School X, 11/16/2010, 6/30/2011

Do you want this representation, or would you rather have them sequential (in this case all three records dsiplayed)? Your sample worksheet is a bit cleaner, it has the same person in the same assignment for two consecutive periods of time, it would make more sense to combine these.

Yes, I only want to combine them if the start and end date are one day apart, as such in my example. But, a person may be fired and rehired at the same position like in my first example so I don't want to combine them solely based on the job.

[wallyeye]

See if this does what you want. It will take some tweaking still, but most of it is there.

Book3.zip

There are two worksheets, Mult1 and Mult2. Mult1 is the source data, Mult2 is the result. To run the code, go to the VBA IDE (alt-F11), select module1, put your cursor somewhere in the CompareMultiple code and press F5.