Merge cells in a COLUMN when value is the same

aprice

New Member
Joined
Jun 16, 2016
Messages
27
I'm really, really new at this. In fact, I don't even understand the directions on how to post a sample of my data except to hope my copy/paste holds up. I can't figure out where to insert the Row/Column/Cell Range info as it pertains to my data when it is given as VBA as an answer to someone else's question. So, please be detailed and basic when you answer my question because I am trying really hard to learn.
Here's what I'm dealing with, sort-of:
ABCD
1Out
1006/11AP
2Out1096/01MR
3Out1125/24JC
4In1095/30JC
5In1786/11AP
6Lost1005/15AP

<tbody>
</tbody>

I want to merge A1-A3, then A4-A5, then A6-to how ever many would be below with the same text. This data goes on for about 150 rows. So, I don't want to have to select the duplicated cells in column A and merge them myself, like I have been doing.

I want it to look like this:
ABCD
1100
6/11AP
2Out109
6/01MR
3___112
5/24JC
4In109
5/30JC
5___178
6/11AP
6Lost
100
5/15AP

<tbody>
</tbody>


I hope that makes sense. I have been creating macros, and I have been stepping into the code to make minor alterations as I've come across things I need to correct in the macro, so I do have some basic knowledge of how it works, just not a lot.
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
A few questions:
1) In your output above, why does "Out" appear in A2 instead of A1 or A3? Why does "In" appear in A4 instead of A5? I'm trying to determine the logic required here, not questioning your needs.
2) Is, for example, "Out" repeated anywhere further down the column? If so, do you need it to be merged separately?
3) Do you want the merging to occur in column A or can it be in another column as a formula?
 
Upvote 0
Welcome to the MrExcel board!

Try this in a copy of your workbook. To implement ..
1. Right click the sheet name tab and choose "View Code".
2. In the Visual Basic window use the menu to Insert|Module
3. Copy and Paste the code below into the main right hand pane that opens at step 2.
4. Close the Visual Basic window.
5. Back in your worksheet press Alt+F8, select the 'Merge_Cells' macro & click 'Run'.
6. If using Excel 2007 or later your workbook will need to be saved as a macro-enabled workbook (*.xlsm)

Rich (BB code):
Sub Merge_Cells()
  Dim rBlank As Range
  
  Application.ScreenUpdating = False
  With Range("A2", Range("A" & Rows.Count).End(xlUp))
    .Value = Evaluate(Replace(Replace("if(#=^,"""",#)", "#", .Address), "^", .Offset(-1).Address))
    For Each rBlank In .SpecialCells(xlBlanks).Areas
      With rBlank.Offset(-1).Resize(rBlank.Rows.Count + 1)
        .MergeCells = True
        .VerticalAlignment = xlCenter
      End With
    Next rBlank
  End With
  Application.ScreenUpdating = True
End Sub


Excel Workbook
A
1Out
2Out
3Out
4In
5In
6Lost
7Out
8In
9In
10In
11In
12Out
Before



Excel Workbook
A
1Out
2
3
4In
5
6Lost
7Out
8In
9
10
11
12Out
After
 
Upvote 0
1) "Out" and "In" are only in those rows because when I tried to copy/paste my example with merged cells, the whole row shifted over as if the blank cells were missing. In my Excel worksheet, "Out" is centered within merged cells A1:A3, and "In" is centered within merged cells A4:A5. I just couldn't paste it into the topic space like that and keep the "table appearance" looking right.
2) The words "Out, In, Lost, etc." shouldn't be by themselves anywhere else unless I messed up somewhere. So far the macro has always grouped them together. Then I just go through an manually merge the like-cells in column A.
3) The merging has to occur in column A in order to serve as a separate group header (sider?). Sort-of like labeling a stacked bar graph.
 
Upvote 0
Thanks! I will try it tomorrow at work. I have a Mac at home, and right clicking the sheet name did not give me the option of "View Code." I can't find the Developer tab on my Excel for Mac, even though I keep reading about a Ribbon bar that I don't have either.
I really appreciate the help. This has had me stumped for a while.
 
Upvote 0
Sorry, I can't help with Mac issues, but I should be able to help with getting it working on a Windows machine - if it doesn't already do what you want.
 
Upvote 0
What version of Excel for Mac do you have? 2011?

Press Command + , (⌘,) to open preferences
then
under the Sharing and Privacy section, click the Ribbon button
select to turn on the ribbon and to expand the ribbon when workbook opens

Then in the list below that make sure "Developer" is ticked.

That should allow you to open the Editor window.

or Function (fn) + Option (alt) + F11 to open the Editor windows
or Function + Option F8 to open the Macro window


If you're using 2016 version I am not certain of what to do but I cannot imagine it would be all that different.
 
Last edited:
Upvote 0
I think I keep posting my replies to the wrong place. This formula was exactly what I needed. How you know all of those codes is beyond me, but I am very grateful.
 
Upvote 0

Forum statistics

Threads
1,213,567
Messages
6,114,342
Members
448,570
Latest member
rik81h

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top