Excel – merge two rows in excel worksheet where one cell has same content but other cells have different content

mergemicrosoft excel

My problem is that in my excel worksheet of several thousand entries the item (eg widget a) is included in more than one row, with different attributes listed in the different rows. Aside from the title column, the content in the rows is not duplicated ie if value 1 has an entry for widget a in one row, it does not in the second row, which I hope will make the job of merging the two rows easier.

Here an image that shows my issue.
http://postimg.org/image/n04g4neod/

Where the title columns match I want to merge the data in the two rows to end up with one single row with all the data combined ending up with One entry for widget A: Title, Value 1, Value 2, Value 3 and so on.

Thanks so much in advance for any help!

Best Answer

Here's a neat little trick for doing this kind of merge.

  1. Select the value cells in your table, i.e., B2:F5 in your example.
  2. Press F5. Click Special.... Select Blanks and click OK. This will select all the blank cells in the table.
  3. Click inside the formula bar and paste or type the formula below (Do NOT hit Enter):
    =IF($A3=$A2,C3,IF($A1=$A2,C1,""))
    C3 is the cell below the active cell, and (obviously) C1 is the cell above. Adjust the formula to match your table.
  4. Press Ctrl+Enter. This will effectively fill down this formula to all the selected blank cells.

The formula just checks for an entry with the same keyword below it and copies its value if it exists. If no duplicate exists below, then it checks above. If none exists there, the cell remains blank.

Finally, you can copy the table and Paste Special > Values back on top of the table to clear out the formulas but keep the values. Then you can use the Remove Duplicates tool located on the Data ribbon to clean up the table.

Related Question