Extract data from one spreadsheet to another

[NatalieMC]

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

[Glenn Kennedy]

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.

[NatalieMC]

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

[AliGW]

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.

[NatalieMC]

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

[JeteMc]

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.