How do I merge duplicate names but keep other columns intact?

Question

How do I merge duplicate names but keep other columns intact?

Anonymous

Is there a formula that would transform the table on the left into the table on the right? This would merge duplicate name values but keep the others intact? (In Excel)

STARTING

LAST	FIRST	GRADE	COURSE	MP 1	MP 2
Smith	Joe	12	MATH	A	B
Smith	Joe	12	SCIENCE	C	D
Smith	Joe	12	ART	A	B
Smith	Joe	12	ENGLISH	C	D
Thompson	Matt	12	ENGLISH	A	B
Thompson	Matt	12	PE	C	D
Thompson	Matt	12	BIOLOGY	A	B
Wilson	Sally	11	MATH	C	D
Wilson	Sally	11	SCIENCE	A	B
Wilson	Sally	11	ART	C	D
Wilson	Sally	11	ENGLISH	A	B
Wilson	Sally	11	PE	C	D
Wilson	Sally	11	BIOLOGY	A	B

DESIRED FORMAT

Smith	Joe	12	MATH	A	B
SCIENCE	C	D
ART	A	B
ENGLISH	C	D
Thompson	Matt	12	ENGLISH	A	B
PE	C	D
BIOLOGY	A	B
Wilson	Sally	11	MATH	C	D
SCIENCE	A	B
ART	C	D
ENGLISH	A	B
PE	C	D
BIOLOGY	A	B

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

7 answers

Answer 1

Anonymous

Try the following:

0 comments

Answer 2

Anonymous

A plain pivot table can do this:

0 comments

Answer 3

Hi,

This M code works

let

Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content], 

#"Grouped Rows" = Table.Group(Source, {"LAST", "FIRST", "GRADE"}, {{"Group", each Table.AddIndexColumn(\_,"Index",1,1), type table}}), 

#"Expanded Group" = Table.ExpandTableColumn(#"Grouped Rows", "Group", {"COURSE", "MP 1", "MP 2", "Index"}, {"COURSE", "MP 1", "MP 2", "Index"}), 

#"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Expanded Group", {{"GRADE", type text}}, "en-IN"),{"LAST", "FIRST", "GRADE"},Combiner.CombineTextByDelimiter(";", QuoteStyle.None),"Merged"), 

#"Added Custom" = Table.AddColumn(#"Merged Columns", "Custom", each if [Index]=1 then [Merged] else null), 

#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Merged", "Index"}), 

#"Split Column by Delimiter" = Table.SplitColumn(#"Removed Columns", "Custom", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"Last", "First", "Grade"}), 

#"Reordered Columns" = Table.ReorderColumns(#"Split Column by Delimiter",{"Last", "First", "Grade", "COURSE", "MP 1", "MP 2"})

in

#"Reordered Columns"

Hope this helps.

Answer 4

Anonymous

Excel 365 Pro Plus with Power Pivot and Power Query.

Transform with PQ/PP.

No formulas, no VBA macro.

With Pivot Chart and Slicer.

https://www.mediafire.com/file_premium/k08jip63ori6voy/01_29_24.xlsx/file

https://www.mediafire.com/file_premium/biq8nfd64ont5j4/01_29_24.pdf/file

0 comments

Answer 5

It can be done with macro.

=================================

Sub MergeCells()

On Error Resume Next

Application.DisplayAlerts = False

Dim lastRow As Long 

Dim i As Long 

lastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row 

For i = lastRow To 2 Step -1 

    If Range("A" & i).Value = Range("A" & i - 1).Value And Range("B" & i).Value = Range("B" & i - 1).Value And Range("C" & i).Value = Range("C" & i - 1).Value Then 

        Range(Cells(i, 1), Cells(i - 1, 1)).Merge 

        Range(Cells(i, 2), Cells(i - 1, 2)).Merge 

        Range(Cells(i, 3), Cells(i - 1, 3)).Merge 

    End If 

Next i 

Application.DisplayAlerts = True

End Sub

=====================================

Test file sent in private message.

Share via

How do I merge duplicate names but keep other columns intact?

7 answers