I have multiple rows of contract data that have the same 'Synonym ID' that require the information from 'Device1' and 'Service1' columns to be combined on the first row with that particular Synonym ID.
The complexity is that there can be up to 4 different contracts with different devices and services that require combining in the one row and in the relevant column (i.e. for the second contract with the same Synonym ID the Device or Service would be populated into Device2 or Service2 columns).
Is there anyway to automate this with a macro, I've tried using excel formulas to combine rows, but I'm stuck when there are more than 2 rows that need combining.
The end result should look something like this, with the data from the second/third rows with the same synonym ID on the first row with that reference: end result required