• July 17, 2017
    Announcement: New site design for Microsoft Community

    In July, Microsoft will roll out the first of ongoing site improvements aimed to modernize Microsoft Community and help customers get the most out of their community experience.

    • During the roll out period, you may see the old or new site design depending on your location

    • We expect the roll out to finish by 31 July

    Note: Past private message conversations will not move to the new site design. Please save any private messages you would like to keep.

     Learn more about the upcoming site improvements in this thread.

    Thank you for being part of Microsoft Community!

 
Question
37 views

Getting unique values across two files

frosty1433 asked on

I have two very large files we'll call Old and New. New contains many entries that Old contains. What I need to do is remove any entry from New that Old contains. There are 9,459 entries in Old with 55 columns. New contains 11,983 entries with 76 columns. I need to make the comparison based on 5 columns; 'name_last', 'name_first', 'name_middle', 'street', and 'type'

I'm using Excel 2010, I'm very new to it, and haven't got a clue where to start.

1 person had this question

Abuse history


The answered status icon Answer
LianneByrne replied on


If your columns line up when pasting them into a new worksheet you could paste the old and the new worksheet together and in your options go to data - filter  - advanced set the list range as the five columns  nameL      nameF    nameM   street     type  and tick the box that says unique values only.  This will filter your data so you have unique values only and then you can copy the visible cells to a new worksheet.


Be the first person to mark this helpful

Abuse history


The answered status icon Answer

Hi,

 

One simple approach could be as follows:

 

1. In a spare column of the old file, concatenate the five columns i.e. A2&C2&F2&G2&H2.  I have assumed that 'name_last', 'name_first', 'name_middle', 'street', and 'type' are in column A, C, F, G and H

2. Copy this down till last row

3. Repeat steps 1 and 2 for new file as well.  Supoose the spare column in new file in BZ

4. In another spare column of the new file, enter this formula

 

=VLOOKUP(BZ2,spare column of the old file created in step 1 above,1,0)

 

You will get an error against entries which are in new file and not in old file.  Filter on non error values and delete those rows.

 

Ta reduce file size, you may now delete the spare columns created.

Regards,

Ashish Mathur
www.ashishmathur.com
http://twitter.com/excelashish
Be the first person to mark this helpful

Abuse history


progress