How to combine data of the same person into 1 cell

[chornat]

Hi

I have a very badly design report where there's a lot of duplicate data in many rows. (sadly, the IT has not consult me before they launch the system) T-T

Basically, I'm working on employee's development plan where one person has more than one row of action plan. I need to combine the data that one person has only one row of information

Here is the simplified version of the report.

ID	Name	Development Goal	Details	Competency	Method
11023545	Molly	English language skills	Increase knowledge of English language skills in listening, reading and comprehension to apply for work. (by SEP., 2021.)		Study
11023545	Molly	English language skills	Increase knowledge of English language skills in listening, reading and comprehension to apply for work. (by SEP., 2021.)		Practice
11023545	Molly	English language skills	Increase knowledge of English language skills in listening, reading and comprehension to apply for work. (by SEP., 2021.)		Coaching
11033851	Amy	English skill	for presentation	LC1.1	Study
11034213	Amy	Strategic thinking	Analyst data to use in daily work	LC1.1	Study
11034213	Amy	Strategic thinking	Analyst data to use in daily work	LC3.2	Study
41000003	Sally	Strategic thinking	Develop own thinking system	LC1.3	Study
41000003	Sally	Strategic thinking	Develop own thinking system	LC3.2	Study
41000003	Sally	Strategic thinking	Develop own thinking system	LC4.1	Study
41000003	Sally	Strategic thinking	Develop own thinking system	LC3.3	Study

and I need to make a report for my boss to look like this.
- combine column "development goal" : "details"
- without any duplication data

ID	Name	Development Goal
11023545	Molly	English language skills: Increase knowledge of English language skills in listening, reading and comprehension to apply for work. (by SEP., 2021.)
11033851	Amy	English skill: for presentation Strategic thinking:Analyst data to use in daily work
41000003	Sally	Strategic thinking: Develop own thinking system

I know that it must be complicated. if not the whole thing, only some steps that ease the pain, would be appreciated.

 

[alansidman]

Here is a Power Query Solution.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Name", type text}, {"Development Goal", type text}, {"Details", type text}, {"Competency", type text}, {"Method", type text}}),
    #"Removed Duplicates" = Table.Distinct(#"Changed Type", {"ID"}),
    #"Merged Columns" = Table.CombineColumns(#"Removed Duplicates",{"Development Goal", "Details"},Combiner.CombineTextByDelimiter(":", QuoteStyle.None),"Merged"),
    #"Removed Columns" = Table.RemoveColumns(#"Merged Columns",{"Competency", "Method"})
in
    #"Removed Columns"

Book3
	A	B	C
1	ID	Name	Merged
2	11023545	Molly	English language skills:Increase knowledge of English language skills in listening, reading and comprehension to apply for work. (by SEP., 2021.)
3	11033851	Amy	English skill:for presentation
4	11034213	Amy	Strategic thinking:Analyst data to use in daily work
5	41000003	Sally	Strategic thinking:Develop own thinking system
Table1

 

[chornat]

Here is a Power Query Solution.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Name", type text}, {"Development Goal", type text}, {"Details", type text}, {"Competency", type text}, {"Method", type text}}),
    #"Removed Duplicates" = Table.Distinct(#"Changed Type", {"ID"}),
    #"Merged Columns" = Table.CombineColumns(#"Removed Duplicates",{"Development Goal", "Details"},Combiner.CombineTextByDelimiter(":", QuoteStyle.None),"Merged"),
    #"Removed Columns" = Table.RemoveColumns(#"Merged Columns",{"Competency", "Method"})
in
    #"Removed Columns"

Book3
	A	B	C
1	ID	Name	Merged
2	11023545	Molly	English language skills:Increase knowledge of English language skills in listening, reading and comprehension to apply for work. (by SEP., 2021.)
3	11033851	Amy	English skill:for presentation
4	11034213	Amy	Strategic thinking:Analyst data to use in daily work
5	41000003	Sally	Strategic thinking:Develop own thinking system
Table1

Thank you! at least it solved half of the process! ^-^

 

[chornat]

Update:

I am very new to Power Query. Frankly, I don't know where to put the code. hahaha. But thanks to @alansidman, I read his code and did it manually as follow,

1. concatenate "ID", "Name", "Development Goal", "Details"
2. remove duplicate in above column
3. in different column, ="Development Goal"& " : "&"Details"

So, this is like very brutal way of surviving excel 555

Next, How to combine 2 row that got the same name?

This is what I want at the end

Name	Development Goal
11023545	Molly	English language skills: Increase knowledge of English language skills in listening, reading and comprehension to apply for work. (by SEP., 2021.)
11033851	Amy	English skill: for presentation Strategic thinking:Analyst data to use in daily work
41000003	Sally	Strategic thinking: Develop own thinking system

 

[alansidman]

Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

- Follow this link to learn how to install Power Query in Excel 2010 / 2013.

- Follow this link for an introduction to Power Query functionality.

- Follow this link for a video which demonstrates how to use Power Query code provided.

 

[chornat]

Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

- Follow this link to learn how to install Power Query in Excel 2010 / 2013.

- Follow this link for an introduction to Power Query functionality.

- Follow this link for a video which demonstrates how to use Power Query code provided.

I definitely will do! (and after learning that my office's ms office is 2016, that is!)

 

[Peter_SSs]

Continued here.
This thread closed.