+ Reply to Thread
Results 1 to 6 of 6

Identify missing and duplicate data

  1. 08-11-2015, 10:47 AM #1
    coopedup
    coopedup is offline
    Registered User
    Join Date
    02-21-2013
    Location
    Reading, England
    MS-Off Ver
    Excel 2010
    Posts
    48

    Identify missing and duplicate data

    Hi,

    I have two sets of data from two sources and they should match, rows numbers and row content. Each row only has two columns.


    My problem is that the source systems have come out of sync and I need to identify the discrepancies.

    The format is exampled below and will run into hundreds of lines in each file.

    AA11BB11 JOAN1
    AB77GG88 JOHN28
    BS54SB77 JACK00


    My thought was to copy one set of data and add to columns C and D, then do something clever which would align the two sets of data (first set in A,B the second in C,D columns).

    AA11BB11 JOAN1 AA11BB11 JOAN1
    AB77GG88 JOHN28 BS54SB77 JACK00
    BS54SB77 JACK00 DE89SS88 JIMAB
    DE89SS88 JIMAB AN32RU88 WAAV2F
    AN32RU88 WAAV2F AN78UA40 MACKAR
    AN78UA40 MACKAR AN95LA92 VUEBAL
    AN95LA92 VUEBAL


    My short fall is the clever bit. Any good ideas?


    Thanks, Neil
    Reply With QuoteRegister To Reply
  2. 08-11-2015, 10:55 AM #2
    GeneralDisarray
    GeneralDisarray is offline
    Forum Expert GeneralDisarray's Avatar
    Join Date
    09-15-2011
    Location
    Pittsburgh, PA, USA
    MS-Off Ver
    Windows Excel 2016
    Posts
    1,416

    Re: Identify missing and duplicate data

    What is your end goal exactly?
    Remember, saying thanks only takes a second or two. Click the star icon(*) below the post you liked, to give some Rep if you think an answer deserves it.

    Please,mark your thread [SOLVED] if you received your answer.
    Reply With QuoteRegister To Reply
  3. 08-11-2015, 10:55 AM #3
    JBeaucaire
    JBeaucaire is offline
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Identify missing and duplicate data

    1) Original data in A:B
    2) Second set of data in G:H
    3) This formula in C1, copied down and into column D:

    =IFERROR(INDEX(H:H, MATCH($A1, $G:$G, 0), "")

    4) Copy the results in C:D and do a PASTE SPECIAL > VALUES.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)
    Reply With QuoteRegister To Reply
  4. 08-11-2015, 11:29 AM #4
    coopedup
    coopedup is offline
    Registered User
    Join Date
    02-21-2013
    Location
    Reading, England
    MS-Off Ver
    Excel 2010
    Posts
    48

    Re: Identify missing and duplicate data

    GeneralDissaray, Sorry. End goal.

    Which data is found in both sets. These are correct and left as is.
    What data is found only in first set. Update to second source system.
    What data is found only in second set. Update to first source data system

    JBeaucaire, Not sure what is expected. When I copy the data as paste special the formula is overwritten and nothing happen???

    Thanks,
    Neil
    Reply With QuoteRegister To Reply
  5. 08-11-2015, 11:50 AM #5
    JBeaucaire
    JBeaucaire is offline
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Identify missing and duplicate data

    another sure fire route:

    1) Copy all the data from BOTH sets into a single set A:B, data starts in row2 and titles in row1.
    2) Use the Data > Advanced Filter to extract a UNIQUE set of data from the combined lists.
    3) Copy the unique set back to your two source files. They are now in sync.
    Reply With QuoteRegister To Reply
  6. 08-11-2015, 11:52 AM #6
    JBeaucaire
    JBeaucaire is offline
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Identify missing and duplicate data

    One more:

    1) Copy all the data from BOTH sets into a single set A:B, data starts in row2 and titles in row1.
    2) Use the Data > Remove Duplicates to get a UNIQUE set of data from the combined lists.
    3) Copy the unique set back to your two source files. They are now in sync.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. identify missing data in an excel file
    By sarahsh in forum Excel General
    Replies: 2
    Last Post: 05-07-2015, 09:44 AM
  2. Identify missing rows from 9000+ row spreadsheet and add data to certain rows
    By sammyandco in forum Excel General
    Replies: 3
    Last Post: 07-04-2014, 09:35 PM
  3. Find and identify duplicate data across multiple workbooks
    By raw_geek in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 12-23-2013, 02:27 PM
  4. Identify Blank cells with Missing Data
    By Cortlyn in forum Excel General
    Replies: 3
    Last Post: 11-05-2012, 01:04 PM
  5. Is there a macro to identify and remove duplicate data in Excel?
    By Cindy Lou in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-08-2006, 11:15 PM
  6. Missing or Duplicate Data
    By DaveBrown in forum Excel General
    Replies: 1
    Last Post: 09-06-2005, 02:10 PM
  7. [SOLVED] identify duplicate data in excel spreadsheet
    By Mandeep Dhami in forum Excel General
    Replies: 3
    Last Post: 07-16-2005, 09:05 AM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1