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

[bunabaker]

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
	A	B	C	D	E	F
1
2					A3	B3
3	Original:	CVA GROUP			CVA GROUP	CVA GROUP
4
5	Row3 unmerged:	CVA GROUP	CVA GROUP		CVA GROUP	CVA GROUP
6
7	Row 5 merged	CVA GROUP			CVA GROUP	0
8
9	Row 3 renamed	EXERCISE GRP			EXERCISE GRP	CVA GROUP
Sheet1

Cell Formulas
Range	Formula
E9:F9,E7:F7,E5:F5,E3:F3	E3	=B3

 

[offthelip]

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]

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

 

[Fluff]

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.

 

[bunabaker]

Here is the link to where I was given a solution