Solved

Auto merging excel cells

Posted on 2011-09-27
5
331 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 50
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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Computer science students often experience many of the same frustrations when going through their engineering courses. This article presents seven tips I found useful when completing a bachelors and masters degree in computing which I believe may he…

739 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