Consolidating rows in one column

B

bpsp

New member
Joined
Jan 30, 2020
Messages
2
Reaction score
0
Points
0
Excel Version(s)
2016
This is my data:
External Name First
External Name LastRelationshipInternal Name
JohnSmithLeaderSara Jones
JohnSmithLeaderJennifer Smith
BobBrownLeaderDavid Chan
MichelleAdamLeaderNoel David
MichelleAdamLeaderAllen Stevens
MichelleAdamLeaderJordan Wood
MichelleAdamLeaderErin May
MichelleAdamLeaderKaren Sinclair


I would like the end result to be:

External Name First
External Name Last
RelationshipInternal Name
JohnSmithLeaderSara Jones; Jennifer Smith
BobBrownLeaderDavid Chan
MichelleAdamLeaderNoel David; Allen Stevens; Jordan Wood; Erin May; Karen Sinclair
Is this possible without manually doing it? data is over 1000 rows long.
 

Attachments

  • forum question.xlsx
    12.9 KB · Views: 9
In E2 of your sheet:
Code: 
=IF(AND(A3=A2,B3=B2,C3=C2),E3 & "; " & D2,D2)
In F2 of your sheet:
Code: 
=OR(A1<>A2,B1<>B2,C1<>C2)
Copy both down.
Convert Column E to plain values by Copy/Paste-Special|Values in situ.
TRUE will be in column F for the rows you want to keep. So Autofilter for FALSE in column F and select the rows using the row header numbers and right-click and select Delete Rows. Remove the Autofilter. Delete column F.
 
Thank you!

Thank you so much. this works great.

p45cal said:
In E2 of your sheet:
Code: 
=IF(AND(A3=A2,B3=B2,C3=C2),E3 & "; " & D2,D2)
In F2 of your sheet:
Code: 
=OR(A1<>A2,B1<>B2,C1<>C2)
Copy both down.
Convert Column E to plain values by Copy/Paste-Special|Values in situ.
TRUE will be in column F for the rows you want to keep. So Autofilter for FALSE in column F and select the rows using the row header numbers and right-click and select Delete Rows. Remove the Autofilter. Delete column F.
Click to expand...
 
You must log in or register to reply here.
Back
Top