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.
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.
B2:F5
in your example.Special...
. SelectBlanks
and click OK. This will select all the blank cells in the table.C3
is the cell below the active cell, and (obviously)C1
is the cell above. Adjust the formula to match your table.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 theRemove Duplicates
tool located on the Data ribbon to clean up the table.