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.

table example

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

  • could you give a data sample (some rows). I may see a possibility with the Power Query Add-in of Microsoft.
    – visu-l
    Jun 19, 2016 at 19:48

1 Answer 1


Not sure if I completely understand the requirements. Consider this screenshot.

enter image description here

The formula in C3 is


Copy across to F3, then copy down. The formula in G3 is


Copy across to J3 and copy down.

Edit after comment: If you want the two values in one cell you can use something along the lines of

  • Thanks @teylyn - I need the contract data on the same row, so for example row 7 with aa5 and bb5 should be included in row 3 (the first row with that ID number 11), is there a way to do this in one step? (or use a macro function?)
    – newbie
    Jun 20, 2016 at 13:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.