Matching Data

[Jonny.Page]

Hello,

I've got 2 lists of publication titles (i.e. text), and I need to find out which titles are present in both lists, and which are only present in one of the lists. I've got a separate table indicating in which list the titles can be found (list 1, list 2 or both) using 0 and 1 for absence and presence respectively. There are a couple of thousand titles in each list, so I'm going to need a formula or macro, etc. if I want to do this in any sort of reasonable time. However, I've been struggling to find one that works. I've been experimenting with IF and VLOOKUP but I've had no luck so far. I was hoping someone might be able to help?

Thanks,

JP

[Sam Capricci]

Without a sample I would try =vlookup(A2,sheetwtitlesA:A,1,false) where you substitute your column you have the titles in where I put sheetwtitles.

[Sam Capricci]

BTW, the titles will need to match to get a match.

[aganesan99]

I think the formula in the attached workbook might help you.

[Jonny.Page]

Thanks for the help guys, I really appreciate it! I can see the formula should work, the only issue is that because the 2 lists are from 2 different sources (one compiled by me and one compiled by someone in the US) they're in a slightly different format, meaning that the text in the cells between the 2 lists don't match up perfectly, so formula isn't working at the moment. However I'm getting the list resent to me in the same format as the one I've done, so after I receive that everything should work.

Thanks again!

[newdoverman]

Without compiling a master list you can insert two helper columns. One helper column for the first list and one for the second list.
In the helper column for the first list enter this formula and copy down.

Please Login or Register  to view this content.

In the helper column for the second list enter this formula and copy down.

Please Login or Register  to view this content.

Sort both lists so that the "In List....." are at the top of the lists and you have the items that are in both lists. All the entries with a blank are only in that list.

[martindwilson]

because the 2 lists are from 2 different sources
why not use the same format as each other? only one of you has to change