+ Reply to Thread
Results 1 to 2 of 2

Merge and only keep duplicates? Looking for tutorial

  1. #1
    Registered User
    Join Date
    01-03-2021
    Location
    Indiana
    MS-Off Ver
    365
    Posts
    31

    Merge and only keep duplicates? Looking for tutorial

    Hi, I can't find a tutorial on what I need to do and thought maybe I'm searching online with the wrong keywords. I hope to get some advice and/or pointed in the right direction.

    I have two excel sheets. I want to merge the two sheets but only have the duplicates rows kept and anything that is not a duplicate to not be merged into the sheet.

    On my sample sheets column B the Job Numbers is what is being compared to find duplicates. Anything without a duplicate in column B should not be merged.

    Main Sheet Inspection is the main spreadsheet.



    Thank you for any input
    Kimberly
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,423

    Re: Merge and only keep duplicates? Looking for tutorial

    This proposal employs a helper column (F) on the 38-Reinspections-Report.xlsx-20 sheet which may be moved and or hidden for aesthetic purposes.
    The helper column finds the corresponding row of duplicates on the Lifetime sheet and is populated using: =MATCH(RIGHT(B2,5)+0,Lifetime!B$1:B$14,0)
    The formula that populates columns A:E the Output sheet is: =IFERROR(INDEX('38-Reinspections-Report.xlsx-20'!A$1:A$10,AGGREGATE(15,6,ROW(A$1:A10)/(ISNUMBER('38-Reinspections-Report.xlsx-20'!$F$1:$F$10)),ROWS($A$1:$A1))),"")
    The formula that populates columns F:G is: =IFERROR(INDEX(Lifetime!F$1:F$14,MATCH(RIGHT($B2,5)+0,Lifetime!$B$1:$B$14,0)),"")
    Note that zeros in column F are hidden using conditional formatting.
    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

+ 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. Merge rows containing duplicates
    By Coretex in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 02-26-2019, 09:23 PM
  2. Merge Duplicates with Different Partial Data??
    By Osensnolf in forum Excel General
    Replies: 0
    Last Post: 08-06-2013, 09:16 AM
  3. Sum, Merge, Delete Duplicates
    By DeXTeR270 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-24-2013, 08:37 AM
  4. Merge Rows of duplicates??
    By anixan in forum Excel General
    Replies: 4
    Last Post: 01-09-2013, 12:45 PM
  5. merge wordlist without duplicates
    By wali in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 12-30-2009, 03:25 AM
  6. Remove duplicates from col.A and merge col.B
    By wali in forum Excel Programming / VBA / Macros
    Replies: 19
    Last Post: 08-13-2008, 04:54 PM
  7. How to merge and weedout duplicates
    By akondeti in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-19-2008, 12:48 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