Complicated Dup Issues

B

belboy87

New Member
Joined
Apr 9, 2008
Messages
35
Ok, maybe not complicated for some of you, but definitely complicated for me!!

Here's a sample of the Excel data I'm working with:

FamilyName Address1 FirstName LastName Relationship Phone#
00001 1234 N Rd John Doe Father 1234
00002 5678 S Ln Jane Doe Dismissal 1 (blank)
00002 (blank) Jane Doe Mother 5678
00003 2468 W Hwy Jane Doe Grandmother 1234


So here's what I want to do... you'll notice that line 2 and 3 are duplicates. I want to run a formula or macro on this data (I have about 30,000 rows that is about 12,000 duplicates). Here's the issue, instead of just running a formula that deletes one entire row (ie; the formula identifies rows 3 and 4 as a dup, and just deletes the first one), I want to identify the blanks (see red cells) and then I want to "merge" the cell so that the left over row has the most complete information possible. So here's a breakdown of the formula I want to write:

A: Check the to see if FirstName and LastName are identical

B: Next, I want to check the rows vs. the FamilyName to make sure they are identical (if it does not match, then the formula stops).

C: Then, if either the Address cell or the Phone cell is blank, I want to copy the cell that has a value. (and if they're both blank, I want to leave them blank).

D. Lastly, IF the cell is a duplicate, I want to merge the relationship column. In this case, the end result of Relationship should be Dismissal 1 + Mother.

Can anyone help me? I have no idea where to even begin!
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
Sort by date Sort by votes
My data didn't line up. Here it is again:

FamilyName .......Address1 ........ FirstName........ LastName ..........Relationship......... Phone#
00001.............. 1234 N Rd........ John .................. Doe .................. Father................ 1234
00002 ............. 5678 S Ln ..........Jane ................Doe.................. Dismissal 1......... (blank)
00002 ...............(blank) .............. Jane............. Doe .....................Mother............ 5678
00003............. 2468 W Hwy ..........Jane............. Doe................. Grandmother............ 1234
 
Upvote 0
Code: 
Sub test()
Dim a, b(), i As Long, ii As Long, n As Long, z As String
With Range("a1").CurrentRegion.Resize(, 6)
    a = .Value
    ReDim b(1 To UBound(a, 1), 1 To UBound(a, 2))
    With CreateObject("Scripting.Dictionary")
        .CompareMode = vbTextCompare
        For i = 1 To UBound(a, 1)
            z = a(i, 1) & ";" & a(i, 3) & ";" & a(i, 4)
            If Not .exists(z) Then
                n = n + 1 : .add z, n
            End If
            For ii = 1 To UBound(a, 2)
                If ii <> 5 Then
                    If a(i, ii) <> "" Then b(.item(z), ii) = a(i, ii)
                Else
                    b(.item(z), ii) = Trim(b(.item(z), ii) & " " & a(i, ii)
                End If
            Next
        Next
    End With
    .Offset(, .Columns.Count + 1).Resize(n, UBound(b, 2)).Value = b
End With
End Sub
 
Upvote 0
Thank you for the extremely prompt response!!

However, when I try to run the Macro, it throws a "Syntax error"
 
Upvote 0
Can you add ")" at the of
Rich (BB code): 
                    b(.item(z), ii) = Trim(b(.item(z), ii) & " " & a(i, ii))
                                                                           ↑
 
Upvote 0
Ok, so here's the last two things I need to be able to do with this Macro...

A) If Relationship is the same (ie; row 1 is "Mother" and row 2 is "Mother", then the merge ends up being "Mother Mother" and I only need it to be "Mother").

B) If any of the rows (Address, Phone number) have the word "Same" in them, then I need the program to treat it to the same as being blank.

How can I modify the macro to do this?
 
Upvote 0
Thanks DonkeyOte<SCRIPT type=text/javascript> vbmenu_register("postmenu_1902484", true); </SCRIPT>
Try
Code: 
Sub test()
Dim a, b(), i As Long, ii As Long, n As Long, z As String, e
With Range("a1").CurrentRegion.Resize(, 6)
    a = .Value
    ReDim b(1 To UBound(a, 1), 1 To UBound(a, 2))
    With CreateObject("Scripting.Dictionary")
        .CompareMode = vbTextCompare
        For i = 1 To UBound(a, 1)
            z = a(i, 1) & ";" & a(i, 3) & ";" & a(i, 4)
            If Not .exists(z) Then
                n = n + 1 : .add z, n
            End If
            For ii = 1 To UBound(a, 2)
                If ii <> 5 Then
                    If (a(i, ii) <> "") * (UCase(a(i, ii)) <> "SAME") Then _
                        b(.item(z), ii) = a(i, ii) 
                Else
                    b(.item(z), ii) = Trim(b(.item(z), ii) & " " & a(i, ii))
                End If
            Next
        Next
    End With
    .removeall
    For i = 1 To n
        If a(i, 5) <> "" Then
            For Each e In Split(a(i, 5))
               .item(e) = Empty
            Next
            a(i, 5) = Join(.keys)
        End If
        .removeall
    Next
    .Offset(, .Columns.Count + 1).Resize(n, UBound(b, 2)).Value = b
End With
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question
Issues copying data to another sheet
Replies
4
Views
286
jynxy
J
S
  • Question
Formula to filter COUNTIFS for discrete values too
Replies
9
Views
711
RoryA
RoryA
G
  • Question
Formula to look upwards in column and return value below first blank cell encountered
Replies
8
Views
1K
GTS
G
Jyggalag
  • Question
help with super complex formula
2
Replies
18
Views
744
Sam_D_Ben
S
Skrej
  • Solved
Mysterious calculation issue & lettercase issue
Replies
5
Views
478
Skrej
Skrej

Forum statistics

Threads
1,215,693
Messages
6,126,246
Members
449,304
Latest member
hagia_sofia

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top