Consolidating rows in one column

[bpsp]

Here is my data

External first name	external last name	relationship	Internal name
John	Smith	leader	Sara Jones
John	Smith	leader	Jennifer Smith
Bob	Brown	leader	David Chan
Michelle	Adam	leader	Noel David
Michelle	Adam	leader	Allen Stevens
Michelle	Adam	leader	Jordan Wood
Michelle	Adam	leader	Erin May
Michelle	Adam	leader	Karen Sinclair

And this is what i want my data to look like:

External first name	external last name	relationship	Internal name
John	Smith	leader	Sara Jones; Jennifer Smith
Bob	Brown	leader	David Chan
Michelle	Adam	leader	Noel David; Allen Stevens; Jordan Wood; Erin May; Karen Sinclair

Is this possible without manually doing it? data is over 1000 rows long.

 

[VBE313]

Assuming your data starts in A1 and Ends in D9(Picture Example), and also assuming that you have the new dynamic array functions, Type "=UNIQUE(A2:D9)" in a cell with nothing surrounding it

Update, Just realized you want your last column to be concatenated. This will not do that.

 

[sandy666]

with Power Query (add-in for XL2013)

// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Group = Table.Group(Source, {"External first name", "external last name", "relationship"}, {{"Count", each _, type table}}),
    List = Table.AddColumn(Group, "Internal name", each List.Distinct(Table.Column([Count],"Internal name"))),
    Result = Table.TransformColumns(List, {"Internal name", each Text.Combine(List.Transform(_, Text.From), "; "), type text})
in
    Result

 

[Fluff]

Cross posted

Consolidating rows in one column

This is my data: External Name First External Name Last Relationship Internal Name John Smith Leader Sara Jones John Smith Leader Jennifer Smith Bob Brown Leader David Chan Michelle Adam Leader Noel David Michelle Adam Leader Allen Stevens Michelle Adam Leader Jordan Wood...

www.excelguru.ca

consolidating rows in one column

Here is my data External first name external last name relationship Internal name

www.excelforum.com

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.