VBA: How to summarise and consolidate data into merged fields

[abssorb]

Hi,

I have some data which has a one-to-many relationship tracking projects to companies, which serves well enough for information, however we now need to produce a 'pretty' report and they don't want to see duplicate lines. It's also necessary to fit corporate formatting (not shown) and be consistent week-to-week across many different users.

I'm not the best at VBA but I already have a macro that will take the data line-by-line, filter and preset in a pretty report, but it has duplicate rows as per the sample data. I need to find out how to merge cells which match criteria.
Does anyone have some examples or suggestions where to get started please?

 

[HighAndWilder]

Why not sort the data first by Company and Project ID and then run the macro.

You can sort it afterwards by Project ID and Company to revert it to how it was originally entered.

It is a good idea to have raw data in one worksheet and the analysis / reporting on another worksheet.

 

[abssorb]

It's just a faked sample. The actual data is non-disclosable. I've spotted an error in the mock-up, that ABC Widgets should not appear twice. Sorry about that.
The report is on a separate sheet, and the vba already sorts.

Determining which rows should result in merged cells, and then carrying out merge, is the bit I need help with please.

 

[abssorb]

If it helps anyone else, I found a solution here:

My example is working down through col D to a row number set as a variable.

'  rr is set elsewhere as row number
Mergecells:
       
        For Each Rng In Range("D4:D" & rr)
            If Rng.Value = Rng.Offset(1, 0).Value And Rng.Value <> "" Then
                Range(Rng, Rng.Offset(1, 0)).Merge
                GoTo Mergecells
            End If
        Next

 

[abssorb]

I spoke to soon. the above only operates column by column.

I've spent all evening trying different code postings. I've made a better picture.