1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Comparing of data in two excel files

Discussion in 'Business Applications' started by eddy9800, May 14, 2013.

Thread Status:
Not open for further replies.
Advertisement
  1. eddy9800

    eddy9800 Thread Starter

    Joined:
    Mar 11, 2013
    Messages:
    32
    Hi,

    I have the following challenge: I need to compare two excel files and check if certain identification numbers of participants in the one file are also present in the second file. The first file contains the identification numbers of the people who were invited to complete a questionnaire (SampleMapLookUp) and the second file contains the data of the questionnaires that were sent back (SampleFileBase). Is there a way to automate this process? In annex two sample files: SampleMapLookUp is the file with the numbers (name of the column is "ID_IND") that I want to check in the second file: SampleMapBase (name of the column here is "his_1").
    I don't know if it was necessary but for testing sake I marked the numbers that should be found in yellow in the SampleMapBase. The resulting file should ideally have the lay-out and content of SampleMapLookUp.

    Thanks for any advice,

    Eddy
     

    Attached Files:

  2. draceplace

    draceplace

    Joined:
    Jun 8, 2001
    Messages:
    2,583
    Eddy, I've attached Lookup spreadsheet. I copied the Base spreadsheet to tab Blad2 and added a lookup column next to IN_IND. Using the Vlookup function it finds the matching data in Blad2. VLookup will connect to the other spreadsheet but for demo purpose I just copied to the spreadsheet.

    See if this is what your looking for.
     

    Attached Files:

  3. eddy9800

    eddy9800 Thread Starter

    Joined:
    Mar 11, 2013
    Messages:
    32
    That is exactly what I was looking for. I suppose I can just copy the Vlookup function in to another xlsx file to get it running there to?

    Many thanks,

    Eddy
     
  4. draceplace

    draceplace

    Joined:
    Jun 8, 2001
    Messages:
    2,583
    Yes, you can copy and use it where ever. Google Vlookup and get an understanding of how it configured. There are lots of tutorials that go thru the components. And there are a few rules As I mentioned you can point to an external spreadsheet, It can be nested in ifs.
    One thing they often fail to cover in tutorials:
    =VLOOKUP(C2,Blad2!D$2:D$168,1,FALSE)
    Using the $ to make the 'table_array 'sticky'. In that when you pull it down is doesn't keep incrementing those values. Also the col_index_num (value to return from search table) starts at 0 from the search column and data returned must be to the right of the search column.
     
  5. draceplace

    draceplace

    Joined:
    Jun 8, 2001
    Messages:
    2,583
    Not sure why web is rendering an icon for colon D

    Code:
    =VLOOKUP(C8,Blad2!D$2:D$168,1,FALSE)
    [
     
  6. eddy9800

    eddy9800 Thread Starter

    Joined:
    Mar 11, 2013
    Messages:
    32
    Ok Dan,

    Thanks again,

    I will try to get my head around this via available tutorials. Can I contact you again if I hit a bump in the road?

    Eddy
     
  7. draceplace

    draceplace

    Joined:
    Jun 8, 2001
    Messages:
    2,583
    Not a problem, I'll stay subscribed to this thread.
     
  8. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1098687

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice