How do I merge rows with the same name?

Question

How do I turn this.....

...Into this?

Answer 1

Hi vgnapi!

I'm Jen, and I'd be happy to help you out with this issue.

You could achieve this by using VBA. Kindly check this page that provides couple of solutions on how to combine rows with the same name or ID in Excel.

https://www.extendoffice.com/documents/excel/48...

Note: This is a non-Microsoft website. The page appears to be providing accurate, safe information. Watch out for ads on the site that may advertise products frequently classified as a PUP (Potentially Unwanted Products). Thoroughly research any product advertised on the site before you decide to download and install it.

I hope this information helps. If you have any questions, please let me know and I'd be glad to assist you further.

Best Regards!

Jen :)

Answer 2

Hi,

pic

vba macro

(results in a new sheet)

Sub ConvertData()

'## 06-01-2022 ##

Dim ws1 As Worksheet, ws2 As Worksheet

Dim r As Long, c As Long, x As Long

Application.ScreenUpdating = False

Set ws1 = Worksheets("Sheet1") '<< source sheet name / change as needed >>

c = ws1.Cells(1, Columns.Count).End(xlToLeft).Column

r = ws1.Cells(Rows.Count, 2).End(xlUp).Row

Set ws2 = Sheets.Add

ws1.Cells.Copy

ws2.Range("A1").PasteSpecial xlValues

Application.CutCopyMode = False

ws2.Cells(2, c + 1).FormulaR1C1 = "=CONCAT(RC[-5]:RC[-1])"

ws2.Cells(2, c + 1).Resize(r - 1).FillDown

ws2.Cells(2, c + 1).Resize(r - 1).Value = ws2.Cells(2, c + 1).Resize(r - 1).Value

For x = r To 3 Step -1

If ws2.Cells(x, c + 1).Value = ws2.Cells(x - 1, c + 1).Value Then

ws2.Cells(x - 1, 2).Value = ws2.Cells(x - 1, 2).Value & ", " & ws2.Cells(x, 2).Value

ws2.Rows(x).Delete

End If

Next x

ws2.Columns(c + 1).ClearContents

ws2.UsedRange.EntireColumn.AutoFit

Range("A1").Select

Application.ScreenUpdating = True

End Sub

Answer 3

If you have Excel 365, one way can be using UNIQUE and TEXTJOIN functions

formula in

A19 = TEXTJOIN(";",,IF(($B$3:$B$12=B19)*($C$3:$C$12=C19),($A$3:$A$12),"")) which can be copied down

B19 = UNIQUE(B3:F12)

Answer 4

Hi,

This M code in the Query Editor works

let

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

#"Changed Type" = Table.TransformColumnTypes(Source,{{"Phone", type text}}), 

#"Grouped Rows" = Table.Group(#"Changed Type", {"First Name", "Last Name", "Address", "City", "State"}, {{"Phone", each Text.Combine([Phone], ", "), type text}}), 

#"Reordered Columns" = Table.ReorderColumns(#"Grouped Rows",{"Phone", "First Name", "Last Name", "Address", "City", "State"}) 

in

#"Reordered Columns"

Hope this helps.