Advertisement
If you have a new account but are having problems posting or verifying your account, please email us on hello@boards.ie for help. Thanks :)
Hello all! Please ensure that you are posting a new thread or question in the appropriate forum. The Feedback forum is overwhelmed with questions that are having to be moved elsewhere. If you need help to verify your account contact hello@boards.ie
Hi there,
There is an issue with role permissions that is being worked on at the moment.
If you are having trouble with access or permissions on regional forums please post here to get access: https://www.boards.ie/discussion/2058365403/you-do-not-have-permission-for-that#latest

Compare two spreadsheets

  • 05-07-2010 12:00pm
    #1
    johnfás
    Registered Users, Registered Users 2 Posts: 1,342 ✭✭✭


    Hey, just wondering if anyone knew whether what I want to do is possible.

    I am currently writing a Masters thesis and basically I have two large spreadsheets in front of me, each has about 8,000 entries. One is a list of UK companies and the other a list of Irish companies. However, some of the companies appear on both lists (I'm guessing maybe 5%). I want to be able to count all the instances where the same name appears in both spreadsheets so that I can say how many entries are common to both... However, I of course want to do this automatically rather than manually which would probably take me weeks!!

    Does anybody have any ideas as to how I could do this?

    Cheers


Comments

  • #2
    chin_grin
    Closed Accounts Posts: 10,808 ✭✭✭✭chin_grin


    Is this what you're looking for?

    http://support.microsoft.com/kb/214153


  • #3
    johnfás
    Registered Users, Registered Users 2 Posts: 1,342 ✭✭✭johnfás


    Thanks chin_grin

    Hmmm, I'm not sure that it would do what I need though (perhaps nothing will do what I need)! That will tell me the occurrence of something which I can input. However, to know what to input I would need to go manually through the database, surely?

    What I want is the following:

    List A
    John
    Joseph
    Mary
    Derek
    Paul
    Ian
    Harry

    List B
    Howard
    Mary
    Paul
    Andrew
    Christina
    Rachel
    Harry

    I want the computer to tell me that there are 3 common entries on the two lists. Rather than me having to go through them together (huge databases) and estimating how many appear on both lists. What I'm trying to ascertain is how many of these companies work in both the UK and Ireland but only have a separate list for each!


  • #4
    mathepac
    Banned (with Prison Access) Posts: 4,991 ✭✭✭mathepac


    You could use the VLOOKUP function. If I remember correctly the tables will need to be sorted. This article looks useful, but the Excel sheets will need to be in the same workbook.


  • #5
    bhickey
    Registered Users, Registered Users 2 Posts: 1,340 ✭✭✭bhickey


    Assuming that there are no duplicate companies in List A, then something like the following formula should work in ListA Column B next to each company name:

    =IF (COUNTIF(ListB.$A$2:$A$8;A2)>0;1;0)

    This will give you a '1' for each company that exists in ListB and a '0' where the name doesn't appear. Sum ListA Column B to get the total number of companies in ListA that appear in ListB.


    Regards
    Brian


Advertisement