Combining Rows - Keeping Unique and "New" Values & Merging Duplicate Values

[kimbekaw]

I am in need of quite a bit of help. I do not know VBA, and I don't think I'll be able to figure out what I need to do on my own.

I have a huge spreadsheet that is temporarily storing "member" information. I have quite a bit of semi-duplicate data.

Example
123456 | jane.smith@site.com | Jane | Smith | | Single <-- Older Record
258369 | jane.smith@site.net | Jane | Smith | Houston | Married <-- Newer Record

So, I need these rows combined: keeping the duplicate data and updating with the "newer" data (on the bottom row), but keeping the USERID with the smaller number.

Result
123456 | jane.smith@site.net | Jane | Smith | Houston | Married

Is this even possible? I am assuming if it is that I would need VBA code or a macro.
Otherwise, I have to do this all by hand - which is currently 600+ rows of data to fix. Out of 11,000+ rows, it's not horrible, but who wants to do this by hand?

Can anyone help me out?

[JBeaucaire]

How will this new macro determine rows that are truly a match? Certainly in 11,000 rows of data there could be two Jane Smith's that weren't the same person. If the macro is merging new data up into old rows except for just the first and last name fields, I'd wonder if real individuals are disappearing in the process.

[kimbekaw]

How will this new macro determine rows that are truly a match? Certainly in 11,000 rows of data there could be two Jane Smith's that weren't the same person. If the macro is merging new data up into old rows except for just the first and last name fields, I'd wonder if real individuals are disappearing in the process.

Certainly that is a concern. I have the data filtered to show my duplicates - that's where the 600+ number comes from. Most of these will be duplicates. However, it is occasionally difficult to determine if a record is actually a new record with the same name or just a duplicate. The only way to ensure no loss of data is to through by hand a make judgement calls...which will undoubtedly result in the loss of some data.

I have already found and merged duplicates (by hand) based on emails. It's just come down to the records in which emails have been changed and our old system & DBMS did not handle updating records very well. So, I'm trying to clean the data up. But the task is proving unwieldy at the moment.

[JBeaucaire]

Can you provide a sample workbook with the exact data layout and 10-20 rows of sample data to test a suggested solution on?

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[kimbekaw]

Here's a sample of what I need to clean up: problemsample2.xlsx

[JBeaucaire]

This should do it:

Option Explicit

Sub MergeUP()
'Jerry Beaucaire   5/20/14
'Merges text data upward based on text match in the given column.
Dim vCOL As Long, RW As Long, LR As Long, Col As Long, LC As Long, DelRNG As Range

vCOL = 3                                            'this is the column we will match by
LR = Range("A" & Rows.Count).End(xlUp).Row          'last row of data
LC = Cells(1, Columns.Count).End(xlToLeft).Column   'last column

Application.ScreenUpdating = False                  'speed up macro
                                                    'sort the data by names and ID in column A
Range("A1").CurrentRegion.Sort Cells(2, vCOL), xlAscending, Cells(2, "A"), , xlAscending, Header:=xlYes

For RW = LR To 3 Step -1                            'merge from the bottom up
    If Cells(RW, vCOL).Value = Cells(RW - 1, vCOL).Value Then   'if the vcol value matches the row above
        For Col = 1 To LC                           'then cycle through each column to test and merge up
            If Col <> vCOL Then                     'skip the column being used for the value match
                                                    'if current row is not blank, copy to matched row above
                If Len(Cells(RW, Col).Value) > 0 Then Cells(RW - 1, Col).Value = Cells(RW, Col).Value
            End If
        Next Col
                                                    'memorize the rows that will be deleted at the end
        If DelRNG Is Nothing Then Set DelRNG = Cells(RW, "A") Else Set DelRNG = Union(DelRNG, Cells(RW, "A"))
    End If
    Application.StatusBar = "Progress: " & RW & " rows remaining..."        'update the lower left status bar to indicate progress
Next RW

If Not DelRNG Is Nothing Then DelRNG.EntireRow.Delete xlShiftUp             'delete the unneeded rows
Application.ScreenUpdating = True                   'update the screen
Application.StatusBar = False                       'clear the status bar
End Sub

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

[kimbekaw]

Thanks for your work. This codes did work on the sample I gave you, but it's not working for the entire database.
I'm inserting a module -> pasting the code & running it. It processes, but very few of the records are merged. I have 689 rows total and the subroutine only gets it down to 635. Am I using the code incorrectly or is something wrong?

Scratch that! It was a mistake on my part. Thank you, thank you!