Merge Cells in a column based on merged status of another column

[Jediben]

[TABLE="width: 341"]
<tbody>[TR]
[TD="width: 128, bgcolor: transparent, colspan: 2"]Hello everyone,
[/TD]
[TD="width: 71, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, colspan: 7"]I have a question regarding Merging cells within a macro, in an attempt to remove the manual process I currently have to follow.
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, colspan: 7"]I have a spreadsheet of multiple items, which have around 15 further variable in columns to the right.
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, colspan: 5"]Each item can have a different subset of these variables.
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]EG
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Ref
[/TD]
[TD="bgcolor: transparent"]Base
[/TD]
[TD="bgcolor: transparent"]Date
[/TD]
[TD="bgcolor: transparent"]Branch
[/TD]
[TD="bgcolor: transparent"]Owner
[/TD]
[TD="bgcolor: transparent"]Action
[/TD]
[TD="bgcolor: transparent"]Status
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]10
[/TD]
[TD="class: xl65, bgcolor: transparent"]London
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]01/02/2000
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]4
[/TD]
[TD="class: xl65, bgcolor: transparent"]Dave
[/TD]
[TD="class: xl65, bgcolor: transparent"]Sell
[/TD]
[TD="class: xl65, bgcolor: transparent"]On-going
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]10
[/TD]
[TD="class: xl65, bgcolor: transparent"]London
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]01/02/2000
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]4
[/TD]
[TD="class: xl65, bgcolor: transparent"]Joe
[/TD]
[TD="class: xl65, bgcolor: transparent"]Sell
[/TD]
[TD="class: xl65, bgcolor: transparent"]On-going
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]10
[/TD]
[TD="class: xl65, bgcolor: transparent"]London
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]19/02/2000
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]2
[/TD]
[TD="class: xl65, bgcolor: transparent"]Joe
[/TD]
[TD="class: xl65, bgcolor: transparent"]Buy
[/TD]
[TD="class: xl65, bgcolor: transparent"]Complete
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]10
[/TD]
[TD="class: xl65, bgcolor: transparent"]London
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]22/02/2000
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]2
[/TD]
[TD="class: xl65, bgcolor: transparent"]Harry
[/TD]
[TD="class: xl65, bgcolor: transparent"]Sell
[/TD]
[TD="class: xl65, bgcolor: transparent"]On-going
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]20
[/TD]
[TD="class: xl65, bgcolor: transparent"]Hull
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]01/02/2000
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]4
[/TD]
[TD="class: xl65, bgcolor: transparent"]Dave
[/TD]
[TD="class: xl65, bgcolor: transparent"]Sell
[/TD]
[TD="class: xl65, bgcolor: transparent"]On-going
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]20
[/TD]
[TD="class: xl65, bgcolor: transparent"]Hull
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]01/02/2000
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]4
[/TD]
[TD="class: xl65, bgcolor: transparent"]Joe
[/TD]
[TD="class: xl65, bgcolor: transparent"]Sell
[/TD]
[TD="class: xl65, bgcolor: transparent"]Complete
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]20
[/TD]
[TD="class: xl65, bgcolor: transparent"]Hull
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]01/02/2000
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]1
[/TD]
[TD="class: xl65, bgcolor: transparent"]Dave
[/TD]
[TD="class: xl65, bgcolor: transparent"]Sell
[/TD]
[TD="class: xl65, bgcolor: transparent"]Complete
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]20
[/TD]
[TD="class: xl65, bgcolor: transparent"]Hull
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]11/04/2000
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]4
[/TD]
[TD="class: xl65, bgcolor: transparent"]Harry
[/TD]
[TD="class: xl65, bgcolor: transparent"]Buy
[/TD]
[TD="class: xl65, bgcolor: transparent"]On-going
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]20
[/TD]
[TD="class: xl65, bgcolor: transparent"]Hull
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]11/04/2000
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]3
[/TD]
[TD="class: xl65, bgcolor: transparent"]Harry
[/TD]
[TD="class: xl65, bgcolor: transparent"]Buy
[/TD]
[TD="class: xl65, bgcolor: transparent"]Complete
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]20
[/TD]
[TD="class: xl65, bgcolor: transparent"]Hull
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]11/04/2000
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]4
[/TD]
[TD="class: xl65, bgcolor: transparent"]Joe
[/TD]
[TD="class: xl65, bgcolor: transparent"]Buy
[/TD]
[TD="class: xl65, bgcolor: transparent"]On-going
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]30
[/TD]
[TD="class: xl65, bgcolor: transparent"]Leeds
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]01/02/2000
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]2
[/TD]
[TD="class: xl65, bgcolor: transparent"]Joe
[/TD]
[TD="class: xl65, bgcolor: transparent"]Sell
[/TD]
[TD="class: xl65, bgcolor: transparent"]On-going
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]30
[/TD]
[TD="class: xl65, bgcolor: transparent"]Leeds
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]01/02/2000
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]2
[/TD]
[TD="class: xl65, bgcolor: transparent"]Joe
[/TD]
[TD="class: xl65, bgcolor: transparent"]Sell
[/TD]
[TD="class: xl65, bgcolor: transparent"]Complete
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]30
[/TD]
[TD="class: xl65, bgcolor: transparent"]Leeds
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]01/02/2000
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]4
[/TD]
[TD="class: xl65, bgcolor: transparent"]Harry
[/TD]
[TD="class: xl65, bgcolor: transparent"]Buy
[/TD]
[TD="class: xl65, bgcolor: transparent"]On-going
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]30
[/TD]
[TD="class: xl65, bgcolor: transparent"]Leeds
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]11/04/2000
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]1
[/TD]
[TD="class: xl65, bgcolor: transparent"]Dave
[/TD]
[TD="class: xl65, bgcolor: transparent"]Sell
[/TD]
[TD="class: xl65, bgcolor: transparent"]Complete
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]40
[/TD]
[TD="class: xl65, bgcolor: transparent"]Diss
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]01/02/2000
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]1
[/TD]
[TD="class: xl65, bgcolor: transparent"]Bill
[/TD]
[TD="class: xl65, bgcolor: transparent"]Sell
[/TD]
[TD="class: xl65, bgcolor: transparent"]Complete
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]40
[/TD]
[TD="class: xl65, bgcolor: transparent"]Diss
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]22/02/2000
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]1
[/TD]
[TD="class: xl65, bgcolor: transparent"]Bill
[/TD]
[TD="class: xl65, bgcolor: transparent"]Sell
[/TD]
[TD="class: xl65, bgcolor: transparent"]Complete
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, colspan: 7"]I am required to merge the items in the REF column (which are unique identifiers) and then where possible merge other cells in the column where data is the same, up until the point where it is no longer the same in each row.
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"](Gaps would be merged but the web form doesn't like it!)
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, colspan: 7"]At present I have a macro which is able to merge the cells vertically where they are identical, and I can use it on the cells that I know are unique (in example above only Ref and Base but I have multiple columns in my table to with a Ref is the 'Key' and they are equally unique.)
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, colspan: 7"]Can anyone suggest a way I can get a macro to check the merge status of one other cell in the same row and then only merge the current cell if the other cell is merged too?
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, colspan: 7"]IE. Looking at Action, only merge if all other cells to the left are already merged as well?
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, colspan: 7"]Looking at the Date, it would first check if the Base cell on the same row is merged, and only then would it merge the cells with the same Date.
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, colspan: 7"]Then looking at Branch, it would only merge identical Branch cells if the Date cell to the left was merged.
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, colspan: 7"]Then looking at owner, only merge if the Branch to the left was merged etc etc down to Status (which should be a unique combination).
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]thanks
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]