How to combine rows where one column has duplicate value but other columns are unique?

[erdylu]

See attachment for example case! Basically, I want to merge two rows in which one of the columns has duplicate information. However, the other columns are unique and I would like to keep this data.

[Glenn Kennedy]

In B11:
=IFERROR(INDEX($B:$B,AGGREGATE(15,6,ROW($A$4:$A$8)/($A$4:$A$8=$A11),COLUMNS($B:B))),"")

and in E11:
=IFERROR(INDEX($C:$C,AGGREGATE(15,6,ROW($A$4:$A$8)/($A$4:$A$8=$A11),COLUMNS($E:E))),"")

for the second part, you'll need VBA. Is that OK??

[erdylu]

Thanks so much! The first solution works fine (I just posed both cases as they were both acceptable). Do you mind explaining a bit what this function is doing?

[erdylu]

Sorry - is there a way for it to check for unique values? (If it is Bob vs. Sally, if there is a duplicate in one column but not the other)

[Glenn Kennedy]

=IFERROR(INDEX($B:$B,AGGREGATE(15,6,ROW($A$4:$A$8)/($A$4:$A$8=$A11),COLUMNS($B:B))),"")

Red: if this condition is TRUE
Orange: return the row number
Cyan: from the smallest to the largest
Blue: in the order 1,2,3
Green: and return the corresponding vlaues in column B
Black: if an error is returned - return a blank.

You're welcome.

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

[Glenn Kennedy]

Away for an hour or so. On a bus!!

[Glenn Kennedy]

Just drag the formulae down. However, to get rid of the irritating 0s in B & E, amend the formula in column B to;

=IFERROR(INDEX($B:$B,AGGREGATE(15,6,ROW($A$4:$A$9)/($A$4:$A$9=$A12),COLUMNS($B:B)))&"","")

and similarly for column E.