Merging columns based on the specific value

Question

I am working on an excel sheet where i need to merge cells bases on vehicle number. If there is same vehicle number in two or more consicutive cells then i need to merge the cells to right to the vehicle number. I have a huge file which contains 200 something vehicle numbers. So merging cells based on each vehicle is a time consuming task. Is there any way to simplify this. I have attached a screenshot of my excel sheet.

Screenshot

Answer 1

I would like to suggest you the VBA code to achieve your goal, since it's the best and easiest possible method to apply.

You can use the below written VBA code to Merge adjacent cells in Right Column, if the values Left to them are duplicate.

Check the Screen Shot:

Sub MergeSameCell()

Dim Rng As Range, xCell As Range
Dim xRows As Integer
xTitleId = "Merge Cells In Excel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
Application.ScreenUpdating = False
Application.DisplayAlerts = False
xRows = WorkRng.Rows.Count
For Each Rng In WorkRng.Columns
    For i = 1 To xRows - 1
        For j = i + 1 To xRows
            If Rng.Cells(i, 1).Value <> Rng.Cells(j, 1).Value Then
                Exit For
            End If
        Next
        WorkRng.Parent.Range(Rng.Cells(i, 2), Rng.Cells(j - 1, 2)).Merge
        WorkRng.Parent.Range(Rng.Cells(i, 3), Rng.Cells(j - 1, 3)).Merge
        WorkRng.Parent.Range(Rng.Cells(i, 4), Rng.Cells(j - 1, 4)).Merge
        i = j - 1

    Next
Next
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

How to use the Code:

• Hold down the ALT + F11 keys, and it opens the Microsoft Visual Basic for Applications window.
• Click Insert then Module, and paste the Code.
• Run the Macro.
• When Input box appear select the Data range where Duplicate values are Available.
• Finish with ok.

You get the job done.