How to find records in one spreadsheet but NOT in a second spreadsheet

[lesj1968]

Hi

I have two spreadsheets. They have virtually the same data however one has 256 records more than another. I need to see what these extra records are. However there are approximately 10000 records in each spreadsheet so it is impossible for me to tell. Is there some kind of filter or function that I can run to just display those records that are in a second spreadsheet which are not in the first?

I have tried putting both sets of records into one big spreadsheet (so there are 20000 records in total and many duplicates of course) and filter out unique records using advance filter but this is not helping.

Please can anyone assist with this problem?

Thank you very much for any help.

Les

 

[franklin97355]

What results do you get?

 

[AnotherHiggins]

lesj1968,

I don't suppose you have MS Access?

[tt]_____
[blue]-John[/blue]
[/tt][red]Quidquid latine dictum sit, altum viditur[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[lesj1968]

Yes I have MS Access. Can this be used to do what I want?

 

[Bob44TheCat]

Les,

Have a look at the VLookup Function in Excel. Lookup the records on the smaller spreadsheet from the bigger one, where there's no match you'll get a value of #N/A. Copy and Paste Special>Values the results of the lookup, sort by that column, and you'll have all the unmatched records together.

Hope this helps,

Bob.

 

[AnotherHiggins]

lesj1968,

we really need more info. Bob44TheCat's suggestion of VLookup might work if you have a single field (column) that is never repeated throughout the dataset. An example would be a phone number, SSN, or some other customer ID # for a list of customers.

If you have such a field, then go with the VLookup idea. But if any given field might be repeated, and the only way to identify unique records is by looking at the data in more than one column, then I think Access would be a much easier solution.

Post back to let us know whether you have a unique record.

[tt]_____
[blue]-John[/blue]
[/tt][red]Quidquid latine dictum sit, altum viditur[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[lesj1968]

Thanks I'll give that a try.

 

[DaleWatson123321]

les,

Although you tried the Advanced Filter, there is really no reason for it not to work.

Perhaps you didn't use a required step - which is to set up an extra field to "concatenate" the all the fields.

e.g. =A2&B2&C2&D2&E2&F2&G2&H2&I2

For the criteria, you need to reference the field name used for this concatenation field (e.g. combined), and of course leave the second row of the criteria BLANK.

If you want to re-try this option and encounter any problem, let me know.

A couple of pointers...
a) You should be setting up the criteria on a SEPARATE sheet.
b) You should be extracting the UNIQUE records to a SEPARATE sheet.

Here's example code for the extraction....
Sub Extract_Unique()
'extracts a UNIQUE set of records to the "UniqueList" sheet
Range("data").AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:="comb_cr", _
CopyToRange:=Range("ext"), _
Unique:=True
End Sub

Note: The names above in quotes are Range Names, which of course you'll need to create.


Regards, Dale Watson

 

[lesj1968]

I have two columns. Column A and column B. Each contains a company name. 99% of the records in both columns are the same. However column A contains slightly more company names than the second smaller column B. I need to find out what company names these are. The records in Column A are all unique (in that column only) and the records in Column B are all unique too, only in that column of course. I filtered out any duplicates in each column before hand. I have now put the two columns side by side, as mentioned above - Column A and Column B.

I have tried =VLOOKUP(B1,A1:B10000, A1, FALSE)

However this returns a #ref error. I have no idea if what I have done above is correct. I have looked in the Excel help but it doesn't give much detail on how to use this function. In my formula I attempt to lookup the company name in B1. A1:B10000 is the range of cells in both column A and B. A1 is what I am trying to return - which is the company name in the new column list. I'm not sure what the false does but I have seen this in the many examples of how to use VLookup.

Please can anyone help me resolve the problem of how to return JUST the company names that are in Column A (the slightly larger column) which are not in the smaller Column B?

Thank you for any help.

 

[AnotherHiggins]

lesj1968,

If that's all you need, there are lots of ways to get what you want.

Try this:

Put the smaller list in column A. Put the larger list in column D. In column E, use this formula:

=COUNTIF(A:A,D1)

Now sort by column E, Ascending. All of the zeros will move to the top, and all store names in column D with a zero beside it don't appear in column A.

Dale,

How can you use advanced filter to return UNmatched records? Won't your suggestion return records that appear in both lists?

[tt]_____
[blue]-John[/blue]
[/tt][red]Quidquid latine dictum sit, altum viditur[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[lesj1968]

Thank you very much. That worked perfectly.

 

[DaleWatson123321]

les,

I noticed by your profile that you haven't yet discovered the "proper" method of "giving thanks".

The "textual" is nice. However the "proper" method also includes issuing one of those "Purple STARS" you might have noticed.

To award a STAR, simply click on the "*Thank ____ for this valuable post" - located in the lower-left-corner" of the user's contribution - in this case "anotherhiggins".

Please don't award me a STAR for this simple tip.

Regards, Dale Watson

 

[PeterMoran]

Hi,

At this late stage I might add a further approach as indicated by Chip Pearson on his site:

http://www.cpearson.com/excel/duplicat.htm#InOneNotOther

No doubt there are complications because of the size of your files.

Also checkout his Topic Index at:

http://www.cpearson.com/excel/topic.htm

and keep it for future reference.

Good Luck!

Peter Moran