Lv 4
Ben asked in Computers & InternetSoftware · 7 years ago

How do I combine row data in excel?

I have 2 columns, A and B, Column A has person id #'s and column B has names, one name per person id, the problem is that some person id's have more than one name and I need to make it so only 1 person id appears and all the names with those id's get merged into the same cell, here is what is looked like before:

1010 | John Doe

1010 | Jane Doe

1111 | Ed Smith

1222 | Jack Brown

1222| James Black

What it needs to look like:

1010 | John Doe\Jane Doe

1111 | Ed Smith

1222 | Jack Brown\James Black

Any suggestions?

2 Answers

  • 7 years ago
    Favorite Answer

    This will combine the row information as you require:

    =IF(A2=A1,B1&"\"&B2,IF( AND(A2<>A1,A2<>A3), B2,""))

    Now, to get rid of column B information without killing the newly created column of information.

    1. You can select column B and hide it.

    2. Select column C, copy it, then Paste Values. This will replace the formulas with the results of the formulas. You can now delete column B. Select all the data and sort by column B then delete all the rows that have blanks in column B.

    Source(s): Experience
    • Login to reply the answers
  • Anonymous
    7 years ago

    add a third column

    starting in c2 (and down):

    if(a2=a1,b1&"\"&b2, b2)

    • Login to reply the answers
Still have questions? Get your answers by asking now.