Detect Duplicate and Consolidate Lists

**lysergiclove** · 02-15-2019, 04:35 AM

Hi, so I was sent an address list with names by one of my friends. It's approximately around 1800 and I wanted to group them like:

Name	Adress
John Smith	123 Alpha Ave
John Smith	]321 Bravo Rd
Jane Doe	156 Charlie Fwy
Jane Doe	57 Delta St

Because how it looks like right now is:

Address	Name
John Smith	123 Alpha Ave
Jane Doe	156 Charlie Fwy
John Smith	321 Bravo Rd
Jane Doe	57 Delta St

In short, I wanted a list from wherein it would group the addresses under the same name so that it would be less time consuming when I started inputting the numbers. Because I've checked for duplicate names and there's a lot but has different addresses. If that made any sense, apologies English is not my first language. Any help would be appreciated, Thanks!

**PaulM100** · 02-15-2019, 04:41 AM

I would go with a pivot table. set a pivot table based on your data, add Name and Address in Rows box. Click on pivot, go to Design - Subtotals - Do not show; Grand Totals - Off for Rows and Columns; Report Layout - Show in Tabular; Report Layout - Repeat all Item Labels

**lysergiclove** · 02-15-2019, 04:59 AM

Hi, thank you for the quick response. I was trying to follow your instructions however I'm getting an error saying "Data Source Reference Not Valid". I'm a fairly new to using excel btw. Thanks again!

**PaulM100** · 02-15-2019, 05:06 AM

I've attached an example. Follow the instructions above and try to replicate it. Also, select your range, in this case in columns A and B and press Ctrl+T to convert it to range. Tick the My data has headers box and press ok. Doing so, whenever you insert a new row of data and refresh the pivot, the pivot data source will get automatically updated with all data rows
A link to pivot tables: https://www.contextures.com/CreatePivotTable.html