How can I reproduce this example of 2 merged cells having the same value

bunabaker

New Member
Joined
Jul 11, 2022
Messages
3
Office Version
  1. 2016
Platform
  1. Windows
Hello,

Let me start by saying I'm aware that merging two cells A1 and A2 should remove the value from A2 but I was given a workbook with merged cells where the second cell's value was not removed. However, I am unable to reproduce this when attempting to change the values

In the example, I have pasted the original merged cells in B3/C3 and you can see that B3=C3="CVA GROUP"

In row 5 the 2 cells were unmerged and you can see the formatting is different for C5 but the values are still the same.

Row 7 the cells are remerged and C7's value is removed as a consequence

Finally Row 9 is the original with an attempt to change the value to "EXERCISE GRP" but here, only the first value is changed and the original second value is retained

Any ideas on how to rename the original such that both cells return the same value?

merge example.xlsx
ABCDEF
1
2A3B3
3Original: CVA GROUP CVA GROUP CVA GROUP
4
5Row3 unmerged: CVA GROUP CVA GROUP CVA GROUP CVA GROUP
6
7Row 5 merged CVA GROUP CVA GROUP0
8
9Row 3 renamedEXERCISE GRPEXERCISE GRP CVA GROUP
Sheet1
Cell Formulas
RangeFormula
E9:F9,E7:F7,E5:F5,E3:F3E3=B3
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

offthelip

Well-known Member
Joined
Dec 23, 2017
Messages
2,675
Office Version
  1. 2010
Platform
  1. Windows
Merged cells create problems all the time, the usual advice is to avoid them entirely and use "centre across selection" formatting instead. FORMAT/FORMATCELLS/ALIGNMENT/HORIZONTAL/Centre Across Selection in my version of Excel
 

bunabaker

New Member
Joined
Jul 11, 2022
Messages
3
Office Version
  1. 2016
Platform
  1. Windows
Fair enough point on avoiding merged cells, but for anyone looking for a workaround that keeps them, I was given this tip on another board.

Starting from row 5, don't merge the cells in the traditional way but instead merge a group (same size) of empty cells and then use the format painter to copy the merged format of the empty cells onto the first cell of the desired merging. For added versatility, in this example C5 could be changed to "=B5" and then only B5 need be updated should a different value needed. This method is perfect for what I needed so hopefully others can use it too
 
Solution

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
77,771
Office Version
  1. 365
Platform
  1. Windows
Welcome to the MrExcel Message Board!
I was given this tip on another board.
This is known as cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Please provide links to any other sites where you have asked this question.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.
 

Forum statistics

Threads
1,176,264
Messages
5,902,229
Members
434,952
Latest member
AlanN

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
Top