Merge Cells Depending on whether the next column is identical

**shanyc** · 11-28-2018, 10:03 PM

Hi All! Was hoping to get some help on the following issue:
I have a long list of names and addresses of people. Currently the list separates two people associated with the same address. I would like to merge the names of the two people in identical addresses and eliminate the extra row. Attached is a screen shot of the CURRENT LIST and what I want it to look like. Please let me know if you have an easy solution. If I am able to add the text "and" or "&" between the two merged cells, that would be ideal.

Thank you so much!!

**kersplash** · 11-28-2018, 10:08 PM

Can you attach a sample workbook, not a screenshot?

**shanyc** · 11-28-2018, 10:12 PM

I just added a sample workbook. thanks for the prompt response!!

**JeteMc** · 12-04-2018, 11:41 AM

Perhaps this formula based proposal will help.
D3:D10 are populated using: =COUNTIFS(B$3:B3,B3,C$3:C3,C3)
E3:E10 are populated using: =IF(B3&C3=B4&C4,A3&" & "&A4,A3)
A14:A24 are populated using: =IFERROR(INDEX(E$3:E$10,AGGREGATE(15,6,(ROW($3:$10)-2)/($D$3:$D$10=1),ROW(1:1))),"")
B14:C24 are populated using: =IFERROR(INDEX(B$3:B$10,AGGREGATE(15,6,(ROW($3:$10)-2)/($D$3:$D$10=1),ROW(1:1))),"")
Let us know if you have any questions.