?
Solved

Merge a duplicate row based on the data of one column.

Posted on 2011-05-04
8
Medium Priority
?
392 Views
Last Modified: 2012-05-11
Dear Excel/VB Experts,

I am trying to right a macro in Excel that will help cleanup the duplicates in my workbook. I have some rows with a duplicate field and would like to have the first occurrence of the duplicate deleted and replaced with the second along with all the fields after it.

For example:

(before)
name phone email address
someone       11111111     hbdfk@        
                                       hbdfk@         12 jjj ave
Someonelse   33333333     dsds@          
                                        dsds@          23 jjj blvd

(after)
name phone email address
someone       111111111   hbdfk@        12 jjj ave
someoneelse  333333333   dsds@         23 jjj blvd

Is this possible??

Thanks in advance.
0
Comment
Question by:Tim
  • 5
  • 3
8 Comments
 
LVL 20

Expert Comment

by:Ardhendu Sarangi
ID: 35692259
Hi,
couple of questions -

1. are the duplicate entries listed in consecutive rows in the file?

2. What is the key (field) used to identify duplicate records?

Can you please post a sample sheet?

thanks,
Ardhendu
0
 

Author Comment

by:Tim
ID: 35692301
Yes and yes.

The field is "CX."

Thanks.
Sample.xlsx
0
 

Author Comment

by:Tim
ID: 35692331
Here is the desired outcome of that sample sheet.
Sample-of-Desired-Outcome.xlsx
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 20

Accepted Solution

by:
Ardhendu Sarangi earned 2000 total points
ID: 35692705
Can you try the following macro...


Sub removedupes()
Application.DisplayAlerts = False
Application.ScreenUpdating = False
    Columns("CX:CX").Select
    Selection.TextToColumns Destination:=Range("CX1"), DataType:=xlDelimited, _
                            TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
                            Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
                                                                                      :=Array(1, 1), TrailingMinusNumbers:=True
    For i = Cells(65536, "CX").End(xlUp).Row To 2 Step -1
        If Range("CX" & i).Value = Range("CX" & i - 1).Value Then
            For n = 1 To 110
                If Cells(i - 1, n) = "" Then Cells(i - 1, n) = Cells(i, n)
            Next
            Rows(i & ":" & i).Delete Shift:=xlUp
        End If
    Next
    Range("CX2").Copy
    Columns("CX:DF").PasteSpecial Paste:=xlPasteFormats
    Application.CutCopyMode = False
Application.DisplayAlerts = False
Application.ScreenUpdating = False
MsgBox ("Done!")
End Sub

Open in new window

0
 

Author Closing Comment

by:Tim
ID: 35694228
Thanks that worked fine!
0
 

Author Comment

by:Tim
ID: 35755842
Hi, could you help me understand why the For n = 1 To 110 is there?
0
 
LVL 20

Expert Comment

by:Ardhendu Sarangi
ID: 35756031
n = 1 thru 110 denotes the number of columns that you have with data. if the number of columns are more, then 110 will increase accordingly.
0
 

Author Comment

by:Tim
ID: 35756239
Oh I see. Thank you sir. What about the 65536? if you don't mind.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article, I’ll look at how you can use a backup to start a secondary instance for MongoDB.
In this article, we’ll look at how to deploy ProxySQL.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question