[Solved] Help Merge

[GIC David]

I need to merge the duplicates names at the same address so that only 1 line remains for both people. So Bill @ 123 main St and Jane @ 123 Main St become Bill & Jane @ 123 Main st.

41099 Ms. Alyssa J. Aiello 1109 W Washington Blvd Apt 6D Chicago Illinois 60607-2116 Ms. Aiello
43667 Ms. Gianna Aiello 1109 W Washington Blvd Apt 6D Chicago Illinois 60607-2116 Ms. Aiello
to become
41099 Ms. Alyssa J. Aiello & Ms. Gianna Aiello 1109 W Washington Blvd Apt 6D Chicago Illinois 60607-2116 Misses Aiello

 

[shamimarita]

Hello Gic David,

You can achieve this in Excel using the Power Query tool, which is perfect for combining rows based on matching addresses and merging names into a single line. Here’s a step-by-step guide:

1. Load your data into Power Query

• Select your data range.
• Go to the Data tab >> choose From Table/Range.
• If prompted, confirm your table has headers.

2. Group by Address

• In Power Query, select all the columns that make up the address (for example: “Address”, “City”, “State”, “Zip”).
• Go to Home tab >> select Group By.
• In the Group By dialog, for New column name, enter Combined Names.
• For Operation, choose All Rows (or if you only need the Name column, choose “Text Combine” and select your Name column, with a separator like “ & ”).

3. Combine Names

• If you used “All Rows”, click the expand icon next to the new column and select just the Name field.
• Use the Text.Combine formula to merge all names with “ & ” between them. Example formula:

= Text.Combine([Names], " & ")

• You can also use Add Custom Column and write:

Text.Combine([AllNames][Name], " & ")

4. Recombine Other Data

• Keep the address fields as is (since they’re the same).
• Add any needed static text (e.g., “Misses Aiello”).

5. Close & Load: Once finished, click Close & Load to put the merged results back into Excel.