Solved

Auto merging excel cells

Posted on 2011-09-27
5
321 Views
Last Modified: 2012-05-12
Hi,

I want to be able to auto merge cells in excel that have the same data for example.

Column A

7568
7569
7569
7575
7575
7576
7578
7581
7581
7602
7602
7602
7607
7617
7617
7617
7617
7618


So where 7617 appears four times I want to merge that into one cell and display 7617 in the cell.

Thanks

Luke
0
Comment
Question by:luketr
5 Comments
 
LVL 50

Expert Comment

by:teylyn
ID: 36708724
Hello,

with all due respect, but: why merge?

Merged cells create all kinds of problems, e.g. with selecting rows and columns, formula referencing, conditional formats and VBA.

Merged cells should be avoided wherever possible. If you want to group cells with the same category, use formatting instead, for example a border around a vertical group of cells that belongs together, or "center across selection" for horizontal alignments.

cheers, teylyn

0
 
LVL 9

Expert Comment

by:sachinpatil10d
ID: 36708751
Click the Data tab on the Ribbon.
Click on the Remove Duplicates icon.
Clicking on the icon highlights all data in the sheet and open the Remove Duplicates dialog box.
When the dialog box opens, all of the column headings are selected by default.
click ok

you will receive a dialog box as below
dialogresult.png
0
 

Author Comment

by:luketr
ID: 36708809
Hi,

This won't work for what I need let me explain in more details.

I have 8 eight columns with duplicated data only in column A

For example this is what the data is like

A                   B                   C                   D                                            E                 F                   G
10337      N      Y      Mr ZXA                 4/7/1936      CL      Yes
10337      N      Y      Mrs ZZZ                           1/24/1939      PT      Yes
10341      Y      Y      Mr ccccccc                10/5/1942      PT      Yes
10341      Y      Y      Mrs fffff                     8/17/1949      CL      Yes
10344      Y      Y      Mrs mmmm               11/27/1942      CL      Yes

This is what i would like it to look afterwards.

A                   B                   C                   D                                            E                 F                   G
10337      N      Y      Mr ZXA                 4/7/1936      CL      Yes
      N      Y      Mrs ZZZ                           1/24/1939      PT      Yes
10341      Y      Y      Mr ccccccc                10/5/1942      PT      Yes
      Y      Y      Mrs fffff                     8/17/1949      CL      Yes
10344      Y      Y      Mrs mmmm               11/27/1942      CL      Yes

I have about 15,000 records which is why i need to automate this process.

Thanks

Luke

0
 

Author Comment

by:luketr
ID: 36708814
Hi,

This won't work for what I need let me explain in more details.

I have 8 eight columns with duplicated data only in column A

For example this is what the data is like

A                   B                   C                   D                                            E                 F                   G
10337      N      Y      Mr ZXA                 4/7/1936      CL      Yes
10337      N      Y      Mrs ZZZ                           1/24/1939      PT      Yes
10341      Y      Y      Mr ccccccc                10/5/1942      PT      Yes
10341      Y      Y      Mrs fffff                     8/17/1949      CL      Yes
10344      Y      Y      Mrs mmmm               11/27/1942      CL      Yes

This is what i would like it to look afterwards.

A                   B                   C                   D                                            E                 F                   G
10337      N      Y      Mr ZXA                 4/7/1936      CL      Yes
      N      Y      Mrs ZZZ                           1/24/1939      PT      Yes
10341      Y      Y      Mr ccccccc                10/5/1942      PT      Yes
      Y      Y      Mrs fffff                     8/17/1949      CL      Yes
10344      Y      Y      Mrs mmmm               11/27/1942      CL      Yes

I have about 15,000 records which is why i need to automate this process.

Thanks

Luke

0
 
LVL 17

Accepted Solution

by:
andrewssd3 earned 500 total points
ID: 36708818
I'm inclined to agree with teylyn that merged cells are nothing but trouble, but assuming you have to do it, this code will do it for the first column in the activeworkbook:

Public Sub MergeData()

    Dim c As Excel.Range
    Dim rngMerge As Excel.Range
    Dim rngIn As Excel.Range
    Dim vPrevious As Variant
    
    Set rngIn = Application.Intersect(ActiveSheet.UsedRange, ActiveSheet.Columns(1).EntireColumn)
    
    vPrevious = Empty
    Set rngMerge = Nothing
    
    Application.DisplayAlerts = False
    
    For Each c In rngIn
        If (c.Value <> vPrevious) Then
            ' if we have a previous range, merge the cells
            If Not (rngMerge Is Nothing) Then
                If rngMerge.Cells.Count > 1 Then
                    rngMerge.Merge
                End If
            End If
            Set rngMerge = c.Cells(1)
            vPrevious = c.Value
        Else
            If Not rngMerge Is Nothing Then
                ' extend the range to copy
                Set rngMerge = rngMerge.Resize(rngMerge.Rows.Count + 1, 1)
            End If
        End If
        
    Next c
     
    ' merge the final range if applicable
    If rngMerge.Cells.Count > 1 Then
        rngMerge.Merge
    End If
    
    Application.DisplayAlerts = True
    
    MsgBox "Complete"
    
End Sub

Open in new window

Change the column number in line 8 to do it for a different range, or you could change line 8 to
Set rngIn = Selection

Open in new window

if you want to select the relevant cells before running the macro.
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

A short article about a problem I had getting the GPS LocationListener working.
Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
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.

744 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now