Solved

Auto merging excel cells

Posted on 2011-09-27
5
329 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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

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.
In this post we will learn how to connect and configure Android Device (Smartphone etc.) with Android Studio. After that we will run a simple Hello World Program.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

828 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