Solved

Auto merging excel cells

Posted on 2011-09-27
5
326 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:Ingeborg Hawighorst
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

ScreenConnect 6.0 Free Trial

Want empowering updates? You're in the right place! Discover new features in ScreenConnect 6.0, based on partner feedback, to keep you business operating smoothly and optimally (the way it should be). Explore all of the extras and enhancements for yourself!

Question has a verified solution.

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

If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…

770 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