+ Reply to Thread
Results 1 to 6 of 6

Extract data from one spreadsheet to another

  1. #1
    Registered User
    Join Date
    07-13-2019
    Location
    Sydney, Australia
    MS-Off Ver
    Microsoft Office 365
    Posts
    3

    Question Extract data from one spreadsheet to another

    Hi,

    I have basic self taught skills with excel, with regards to formulas, calculating numbers across worksheets etc.

    But I've been asked to do the following. I have a excel spreadsheet with about 800 rows of data - containing names and a unique identifier. From that spreadsheet I need to extract all occurrences of those names/unique identifier from another excel workbook ( which has about 800000 rows of data) and then report the results back to the requestor. Normally the data would be extracted from a data warehouse, but I have no access to that and I was told to use vlookup, to create the report.
    But I have no idea about vlookup.

    Can anyone suggest a tutorial that will show me how to use vlookup to extract data between two workbooks?


    Would this be a simple formula that I could use in one row and then copy down the column?

    Looking forward to any guidance you can give m

    Regards, NatalieMC



    disclaimer - I can't provide examples as the data is sensitive and confidential

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Northern Ireland
    MS-Off Ver
    Excel 2013 & 2016
    Posts
    23,837

    Re: New poster needing help with finding data in one spreadsheet from another

    You haven't provided anything like enough informaton. Make up a couple of sheets, say about 3 rows of data in the first and 10 in the second. Lay them out as per your real sheets and show how you want your results to be laid out. That way, we CAN help.


    Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

    Incidentally, I edited the title of your thread, as it didn't really meet the requirement of Rule 1.
    Last edited by Glenn Kennedy; 07-13-2019 at 05:42 AM.
    Glenn



  3. #3
    Registered User
    Join Date
    07-13-2019
    Location
    Sydney, Australia
    MS-Off Ver
    Microsoft Office 365
    Posts
    3

    Re: Extract data from one spreadsheet to another

    My apologies regarding the subject line.
    As for laying out how I want it to look, I'm not even sure what kind of results vlookup can give me. I've never used it before or seen it used. I'm not even sure it's what I should be using, but I was told to. I was hoping someone could tell me that a comprehensive online tutorial exists and point me in the right direction.

    Anyway, as requested I've added two sample workbooks.

    Sample 1 is the list of names/data that I have to search/extract from Sample 2.

    I need the complete row of data.


    Thanks in advance
    Attached Files Attached Files

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Excel 2019 (Win 10 - Work) & 365 Subscription (Win 10 - Home)
    Posts
    32,787

    Re: Extract data from one spreadsheet to another

    This looks like a job for PowerQuery. Please have a look at the attached and see if it's what you want. If so, I can explain how to achieve the same yourself.
    Attached Files Attached Files
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  5. #5
    Registered User
    Join Date
    07-13-2019
    Location
    Sydney, Australia
    MS-Off Ver
    Microsoft Office 365
    Posts
    3

    Re: Extract data from one spreadsheet to another

    Quote Originally Posted by AliGW View Post
    This looks like a job for PowerQuery. Please have a look at the attached and see if it's what you want. If so, I can explain how to achieve the same yourself.
    Hi ALi,

    Thank you. That looks great. I don't have Power Query at work but I'm thinking I can download it at home and work from home on it. If you can explain how you achieve it or given time difference point me to an online tutorial I would really appreciate it.

    Thanks in advance

  6. #6
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    8,289

    Re: Extract data from one spreadsheet to another

    Power Query is also know as Get & Transform and should be standard on the ribbon (far left) of the Data tab for Excel versions 2016, 2019 and the subscription (365) version.
    Here is a link to the Highline College tutorial on merging tables using Power Query.
    https://www.youtube.com/watch?time_c...&v=NJEvr5ZoEEw
    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1