+ Reply to Thread
Results 1 to 5 of 5

Detect Duplicate and Consolidate Lists

  1. #1
    Registered User
    Join Date
    02-15-2019
    Location
    Philippines
    MS-Off Ver
    2016
    Posts
    3

    Detect Duplicate and Consolidate Lists

    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!

  2. #2
    Forum Expert PaulM100's Avatar
    Join Date
    10-09-2017
    Location
    UK
    MS-Off Ver
    2016
    Posts
    2,100

    Re: Detect Duplicate and Consolidate Lists

    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
    Click the * to say thanks.

  3. #3
    Registered User
    Join Date
    02-15-2019
    Location
    Philippines
    MS-Off Ver
    2016
    Posts
    3

    Re: Detect Duplicate and Consolidate Lists

    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!

  4. #4
    Forum Expert PaulM100's Avatar
    Join Date
    10-09-2017
    Location
    UK
    MS-Off Ver
    2016
    Posts
    2,100

    Re: Detect Duplicate and Consolidate Lists

    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
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    02-15-2019
    Location
    Philippines
    MS-Off Ver
    2016
    Posts
    3

    Re: Detect Duplicate and Consolidate Lists

    Perfect! Done, thank you so much!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Using Match To Detect Duplicate Record
    By spku in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-20-2013, 03:17 AM
  2. Textbox to detect if there is a duplicate
    By siobeh in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-01-2013, 06:42 AM
  3. Detect if a cell is duplicate
    By Jinxedcookie in forum Excel General
    Replies: 2
    Last Post: 11-29-2011, 01:08 PM
  4. Detect matching values in very large lists
    By Jbentley in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-09-2009, 04:08 AM
  5. detect duplicate number in a column
    By rossryan in forum Excel General
    Replies: 2
    Last Post: 10-06-2009, 09:16 PM
  6. Excel 2007 : Detect a duplicate value in a column / range
    By lmsexcelforum in forum Excel General
    Replies: 3
    Last Post: 05-29-2009, 05:32 AM
  7. Need Formula to detect duplicate invoices
    By XYZ via OfficeKB.com in forum Excel General
    Replies: 1
    Last Post: 10-11-2005, 06:05 PM
  8. detect Duplicate Data
    By bdq17 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-10-2005, 04:07 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1