Merge cells in a COLUMN when value is the same

[aprice]

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:
[TABLE="width: 226"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Out
[/TD]
[TD]100[/TD]
[TD]6/11[/TD]
[TD]AP[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Out[/TD]
[TD]109[/TD]
[TD]6/01[/TD]
[TD]MR[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Out[/TD]
[TD]112[/TD]
[TD]5/24[/TD]
[TD]JC[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]In[/TD]
[TD]109[/TD]
[TD]5/30[/TD]
[TD]JC[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]In[/TD]
[TD]178[/TD]
[TD]6/11[/TD]
[TD]AP[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Lost[/TD]
[TD]100[/TD]
[TD]5/15[/TD]
[TD]AP[/TD]
[/TR]
</tbody>[/TABLE]

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:
[TABLE="width: 226"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD]100
[/TD]
[TD]6/11[/TD]
[TD]AP[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Out[/TD]
[TD]109
[/TD]
[TD]6/01[/TD]
[TD]MR[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]___[/TD]
[TD]112
[/TD]
[TD]5/24[/TD]
[TD]JC[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]In[/TD]
[TD]109
[/TD]
[TD]5/30[/TD]
[TD]JC[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]___[/TD]
[TD]178
[/TD]
[TD]6/11[/TD]
[TD]AP[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Lost
[/TD]
[TD]100
[/TD]
[TD]5/15[/TD]
[TD]AP[/TD]
[/TR]
</tbody>[/TABLE]


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.

 

[barjoman]

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?

 

[Peter_SSs]

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)

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
1	Out
2	Out
3	Out
4	In
5	In
6	Lost
7	Out
8	In
9	In
10	In
11	In
12	Out
Before

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

 

[aprice]

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.

 

[aprice]

That was supposed to have gone above.

 

[aprice]

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.

 

[Peter_SSs]

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.

 

[tygrrboi]

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.

 

[aprice]

This worked PERFECTLY!!! Thank you so much.

 

[aprice]

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.