VBA merge cells that contain the same value but within a particular group

[sm786]

Hi All,

I have a question regarding automatic merging in VBA. Its similar to this thread (http://www.mrexcel.com/forum/excel-...tically-merge-vertically-identical-cells.html) but with a slight twist.

So say if I have four columns set up as follows, where the first line contains headings (before)

Team	Position	Salary	Term (years)
Arsenal	Goalkeeper	100	1
Arsenal	Goalkeeper	100	1
Arsenal	Goalkeeper	50	2
Arsenal	Midfield	50	2
Arsenal	Midfield	50	2
Arsenal	Striker	25	2
Manchester	Striker	25	2
Manchester	Striker	25	3
Manchester	Striker	25	3
Manchester	Manager	25	1
Manchester	Manager	25	1
Manchester	Manager	30	1
Chelsea	Manager	35	2
Chelsea	Staff	35	2
Chelsea	Defender	25	2
Chelsea	Defender	25	2
Chelsea	Striker	25	2
Chelsea	Striker	25	2

<col width=80 style='mso-width-source:userset;mso-width-alt:2925;width:60pt'> <col width=79 style='mso-width-source:userset;mso-width-alt:2889;width:59pt'> <col width=44 style='mso-width-source:userset;mso-width-alt:1609;width:33pt'> <col width=85 style='mso-width-source:userset;mso-width-alt:3108;width:64pt'>
<![if supportMisalignedColumns]>
<![endif]>

Im looking for something like this (please ignore the periods as i had to put them to keep the table from incorrectly displaying).
I'd like to highlight the non-merging of Striker (line 7 and 8 in column2) becasue one belongs to Manchester and the other to Arsenal

Team	Position	Salary	Term (years)
Arsenal	Goalkeeper	100	1
.	.	.	.
.	.	50	2
.	Midfield	50	2
.	.	.	.
.	Striker	25	2
Manchester	Striker	25	2
.	.	.	3
.	.	.	.
.	Manager	25	1
.	.	.	.
.	.	30	1
Chelsea	Manager	35	2
.	Staff	35	2
.	Defender	25	2
.	.	.	.
.	Striker	25	2
.	.	.	.

<col width=64 span=4 style='width:48pt'> <col class=xl6522403 width=64 style='width:48pt'>
<![if supportMisalignedColumns]>
<![endif]>

</div>

 

[Andrew Poulsom]

Have you tried a pivot table? Here's on I created in Excel 2010:

Excel 2010
	K	L	M	N
1	Team	Position	Salary	Term (years)
2	Arsenal	Goalkeeper	50	2
3			100	1
4		Midfield	50	2
5		Striker	25	2
6	Chelsea	Defender	25	2
7		Manager	35	2
8		Staff	35	2
9		Striker	25	2
10	Manchester	Manager	25	1
11			30	1
12		Striker	25	2
13				3
Sheet1

 

[sm786]

thank you for your quick response!Unfortunately I dont think that will work as there are other columns to the right of the four that are all text which dont need to be considered for merging...

 

[Andrew Poulsom]

You don't need to use those columns in your pivot table.