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

6

3

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!

Emily

Posted 2013-08-08T13:30:14.370

Reputation: 61

Answers

3

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 below. 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.

Excellll

Posted 2013-08-08T13:30:14.370

Reputation: 11 857

thank you so much for your answer. it sounds like it's exactly what i'm trying to do but i'm obviously doing something wrong. – Emily – 2013-08-10T14:40:23.703

thank you so much for your answer. it sounds like it's exactly what i need but i'm clearly doing something wrong. i have headings from A to BO

i select the cells with the data that I want to be merged (I just highlighted the first ten rows from A:BO) I clicked F5 > special > select blanks. It successfully highlights the blanks

I don't click anything and simply paste your formula but get the error: "The command you chose cannot be performed with multiple selections. Select a single range and click the command again." Sorry if I'm being obtuse, I love excel but my forumulas are rather basic! – Emily – 2013-08-10T14:58:58.827

No, not obtuse -- "without clicking anywhere" was inaccurate. When you have the blank cells highlighted, click in the formula bar and then paste in the formula. I will edit my answer since what I had was confusing (and wrong). – Excellll – 2013-08-11T16:40:23.467

1Thank you so much!! I am forever grateful, and will be able to use this for so many things. A couple of things for anyone looking to use this:

  1. My version of excel only allowed me to select a column at a time.
  2. When it didn't work it was because there was hidden formatting messing up the formula - copy and paste into notepad and then back into excel and it should work.
  3. For one column with text I actually had to create a blank row above for the formula to work.

Other than that, it worked a treat. Genuinely so grateful. thank you Excellent Excellll! – Emily – 2013-08-16T19:56:37.567

2@Excellll: The OP showed two rows per widget type, but said “more than one row”.  I believe your answer breaks if there are more than two rows for a given widget type.  You might be better saying =IF($A1=$A2, C1, "") and then using some other trick to retain only the last of each group. – Scott – 2013-09-11T23:33:40.560

1@Scott I agree. If there are more than 2 rows per key, the formula needs to be made one-sided and there needs to be some pre-sorting. I stuck with this solution for this case because it made the removal of duplicates in the last step much easier. In the more general case, there needs to be some creative sorting and/or filtering to remove the extra rows. – Excellll – 2013-09-12T01:31:26.580

0

Sort the Table by Keyword... so that all the Widgets are at least grouped together. Then if column g is open enter following array formula in cell G2

=IF(AND(A2:F2=A3:F3),"Duplicate", "Unique")

Ctr-Shft-Enter to evaluate as array formula. WIll mark duplicates. You will have to find ways to clean out contradictory Attributes

Joop

Posted 2013-08-08T13:30:14.370

Reputation: 136

1thanks so much for your answer. I really appreciate you taking the time to help! It hasn't done what I was hoping for though - i don't just want to see the duplicates but to merge them with the row that has the same title. I think i might need a macro but am very poor at this! thanks again! – Emily – 2013-08-08T17:12:14.853

Asked: 2013-08-08T13:30:14.370

Viewed: 68 349 times

Active: 2014-10-30T20:44:24.543