Solved

How to unmerge and fill in identical values

Posted on 2011-09-24
11
376 Views
Last Modified: 2013-11-05
Hello,

I was wondering if there's an easy way to do this. I have a spreadsheet with about 8,000 rows. It is supposed to be something like this (please disregard all except the first column)

1 a a x x
1 b a x z
1 c a a z
2 a s d z
2 k a z c
...

but it was entered in such a way that Cells A1-A3 where merged and centered with just one entry of 1, and Cells A4-A5 were merged/centered with one value of 2. There are almost 8000 such rows, and identical / repeats in the first column are different (e.g. in the example above there are three 1s but just two 2s. Each of this "grouping" variable can be as many as 100 rows, or as few as just one row.

Can someone show me what's an efficient way of Unmerge this and turn it into the format shown above (i.e. no merged cells)?

Much appreciated
0
Comment
Question by:ocan
11 Comments
 
LVL 10

Expert Comment

by:Tony Barkdull
Comment Utility
Highlight All merged cells, right click and format cells. On the Alignment tab, uncheck the Merge cells box. Then set the Horizontal alignment above to General. Click OK.
0
 
LVL 9

Expert Comment

by:jsdray
Comment Utility
Maybe I'm missing sumething but you should be able to select all (CTRL-A) and the unmerge everything... then you could delete the empty columns which would shift it all over the way you want...
0
 
LVL 41

Expert Comment

by:dlmille
Comment Utility
I think you need a bit of VBA code to do the unmerge, AND populate all the unmerged cells with the proper data.

Here's the code, which looks at column A for the merged cells:

Hit Alt-F11, create a public module in the VBA editor, and add the code below (copy/paste) to the public module.  The code runs on the active sheet, assuming data starts in Column A, Row 2 until there's no data.  The app determines merged areas, gets the value for the merged area, unmerges, then sets the value to that just-unmerged area, as appropriate.

Sub unmergeAndFix()
Dim wkb As Workbook
Dim sht As Worksheet
Dim r As Range, rInArea As Range, vMgValue As Variant
Dim mAreaCount As Long

    Set wkb = ActiveWorkbook
    Set sht = wkb.ActiveSheet
    
    For Each r In sht.Range("A2", sht.Range("A" & sht.Rows.Count).End(xlUp))
        
        If r.MergeArea.Count > 1 Then 'propogate data, then unmerge
            'first get the value on the merged area
            
            For Each rInArea In r.MergeArea
                If rInArea.Value <> "" Then
                    vMgValue = rInArea.Value
                    Exit For
                End If
            Next rInArea
            
            Set rInArea = r.MergeArea 'hold this address
            
            'now, breakup the merge
            r.MergeArea.UnMerge
            
            'now, set each element in the merged area to that value
            rInArea.Value = vMgValue
            

        
         End If
    Next r
End Sub

Open in new window


Attached, please find a demonstration workbook.

Enjoy!

Dave
unmergeRepair-r1.xlsm
0
 
LVL 41

Expert Comment

by:dlmille
Comment Utility
Whoops - I did this on a rowbasis... Column basis coming...

:)

Dave
0
 
LVL 41

Expert Comment

by:dlmille
Comment Utility
Wait a sec - maybe I did do this right...  I was thrown off by jsdray's comment, re: columns

@ocan Please advise - Column A is the focus column, correct?  If so, please see my post (the one with code and demo file, above), if not, please clarify.

Dave
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 92

Expert Comment

by:Patrick Matthews
Comment Utility
Another approach:

Sub UnMergeThem()
    
    Dim LastR As Long, LastC
    Dim arr As Variant
    Dim Counter As Long
    Dim TheValue As Variant
    Dim TestValue As Variant
    
    With ActiveSheet
        LastR = .Cells(.Rows.Count, "b").End(xlUp).Row
        LastC = .Cells(1, .Columns.Count).End(xlToLeft).Column
        .Range("a1:a" & LastR).UnMerge
        arr = .Range("a1", .Cells(LastR, LastC)).Value
        For Counter = 1 To LastR
            TestValue = arr(Counter, 1)
            If TestValue <> "" Then
                TheValue = TestValue
            Else
                arr(Counter, 1) = TheValue
            End If
        Next
        .Range("a1", .Cells(LastR, LastC)).Value = arr
    End With
    
    MsgBox "Done"
    
End Sub

Open in new window

0
 
LVL 50

Accepted Solution

by:
teylyn earned 500 total points
Comment Utility
Hello,

if I understand the question correctly, there's also a non-VBA approach:

Select column A and unmerge

unmerge
With Column A still selected, hit the F5 function key. In the next box, click the Special button, tick Blanks and hit OK

select
all blank cells are now selected and the top blank cell has the focus
enter a = sign, hit the up arrow, hold down the Ctrl key and hit Enter

Now all the numbers are filled in.

Formula
You can now copy column A and paste it with Paste Special > Values to replace the formulas with their values.  

cheers, teylyn
0
 

Author Closing Comment

by:ocan
Comment Utility
Thanks to everyone who responded - I am amazed to see so many responses during the weekend. I choose Teylyn's answer because it is very straightforward and easy to follow; better yet, no coding is involved! This is a blessing for me who doesn't know much about VBA.

For other responses, please excuse my ignorance but I haven't got a chance to try it (not sure how to run the code)
0
 
LVL 92

Expert Comment

by:Patrick Matthews
Comment Utility
ocan,

No worries, and glad you got the help you needed.

Patrick
0
 
LVL 92

Expert Comment

by:Patrick Matthews
Comment Utility
BTW, for the future, if you have a preference one way or another as to whether the solution should use code, it would be good to state that up front.

:)
0
 
LVL 41

Expert Comment

by:dlmille
Comment Utility
great solution, teylyn.  I keep forgetting the goto special option! :)
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

762 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

10 Experts available now in Live!

Get 1:1 Help Now