+ Reply to Thread
Results 1 to 8 of 8

How to extract duplicates with date as well.

  1. #1
    Registered User
    Join Date
    08-18-2019
    Location
    england
    MS-Off Ver
    2019
    Posts
    6

    How to extract duplicates with date as well.

    Hi,
    I'm struggling to create a list of duplicates in Excel. I have a list of dates in column A and names in column B. I'd like to create a list in 2 separate columns, let's say C and D, that lists all the names that are duplicated in column B, along with the date that each person appears.
    So if row 3 and 4 had the same name with the dates 10/8/19 and 14/8/19, repsectively, then I would like them to appear in columns C and D like that.
    Ideally I would like this to run automatically as new names and dates are continuously added, so I would imagine an array formula would work well but I can't seem to do this myself. I'm also unsure how to input array formulas in Excel on a mac (2019 version), is it command-shift-enter?
    Many thanks!

  2. #2
    Forum Guru Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    Excel 2010 on Linux
    Posts
    12,031

    Re: How to extract duplicates with date as well.

    Welcome to the forum

    Please attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

  3. #3
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    15,442

    Re: How to extract duplicates with date as well.

    Hi rpclarke94 and welcome to the forum,

    See the attached where I've added a Helper column with a Countifs() formula and then done an advanced filter to get the results I think you want. I created some random data to show what I think you have.

    Countifs() and Adv Filter for Helper.xlsx
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  4. #4
    Registered User
    Join Date
    08-18-2019
    Location
    england
    MS-Off Ver
    2019
    Posts
    6

    Re: How to extract duplicates with date as well.

    I've uploaded a sample workbook as suggested.
    Thanks
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    08-18-2019
    Location
    england
    MS-Off Ver
    2019
    Posts
    6

    Re: How to extract duplicates with date as well.

    Thanks for that, the result is exactly what I want but is there not a way to update it automatically? I've added new names and dates to your list and although the names are added, the dates aren't showing up correctly. For instance, I added another row as name4 with the date 12/9/19 and it shows up with the date 11/8/19?

  6. #6
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    12,082

    Re: How to extract duplicates with date as well.

    Using the sample file attached to post #4 try the following:
    1. Convert A1:B9 into a table
    2. Add a helper column (duplicates) populated using: =COUNTIFS([Name],[@Name])>1
    3. Populate the Duplicates column of the output range using: =IFERROR(INDEX(Table1[Name],AGGREGATE(15,6,(ROW(Table1[Name])-ROW(Table1[[#Headers],[Name]]))/(Table1[duplicates]=TRUE),ROWS(A$1:A1))),"")
    4. Populate the Dates column of the output range using: =IF(E2="","",AGGREGATE(15,6,Table1[Date]/(Table1[Name]=E2),COUNTIFS(E$2:E2,E2)))
    Note that if another row is added to the table (by selecting cell B9 and pressing the tab key) then Sarah is given the date 12/9/19 her name and the new date will appear in the output range.
    Note that, as shown, similar results may be obtained using a pivot table.
    Let us know if you have any questions.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  7. #7
    Registered User
    Join Date
    08-18-2019
    Location
    england
    MS-Off Ver
    2019
    Posts
    6

    Re: How to extract duplicates with date as well.

    Thank You! That's exactly what I needed, it updates itself and works flawlessly.

  8. #8
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    12,082

    Re: How to extract duplicates with date as well.

    You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. I hope that you have a blessed day.

+ 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. [SOLVED] Extract datas and date after given date by eliminating duplicates
    By Sekars in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 07-07-2016, 05:30 AM
  2. [SOLVED] Extract Duplicates
    By Sasquatch2014 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 07-06-2015, 02:19 PM
  3. Extract lookup value from many duplicates
    By ABSTRAKTUS in forum Excel General
    Replies: 4
    Last Post: 04-13-2011, 07:15 AM
  4. Extract Duplicates Only
    By JungleJme in forum Excel General
    Replies: 2
    Last Post: 03-23-2011, 12:08 PM
  5. Extract Duplicates?
    By RickT in forum Excel General
    Replies: 3
    Last Post: 05-23-2009, 06:53 AM
  6. Compare and extract duplicates
    By grouchmax in forum Excel General
    Replies: 7
    Last Post: 05-05-2009, 12:56 PM
  7. Extract duplicates
    By excelexcess in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-05-2007, 02:25 PM

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