Search and Pull Data

[Xanderman41]

Hello,

Normally I can figure things out in Excel pretty well but this one has me right stumped. Not even sure if Excel can do it but it would be great if it could. I have three sheets in one Workbook 2 are data imported from a tracking program I use externally. Now my question is Can I have the first Workbook search for names in the other two and if it finds that name pull the data to a corresponding cell in the first book. I have attached an example this is a small sample size as the true one has a little over 500 lines to it.

I know I can do this manually but that is very administratively heavy and I am trying to reduce that, any help even the bad news that it cant be done would be appreciated.

[GeoffW283]

First you need some common key between the worksheets. Is the team small enough that "Last name" would suffice? Or a combination of first and last name?

I have assumed a combination of first and last name. If just last name would suffice then the following formula would simplify considerably:
In F2:

Please Login or Register  to view this content.

This is an array formula and needs to be committed via CTRL-SHIFT-Enter.
Copy F2 down as far as needed.

There is a blank value in cell F5. This can be removed either by cell formatting or a modification to the formula.

The formulas in col-H for Test3 would be similar.

Hope this helps, let us know.

[alansidman]

Using Get and Transform (Power Query) found on the Data Tab.
1. Bring each table into the PQ Editor. Close and Load to a Connection only.
2. Merge the Test 1 with Test 2 table on the name Left inner join
3. Merge the new merged table with Test 3. Expand the results
4. Close and Load to Excel

Results:
Excel 2016 (Windows) 32 bit

	A	B	C	D	E	F	G	H	I
1	Last Name	First Name	Test 1 Required	Last Test 1	Test 1 Due	Test 2 Due	Test 3 Due	Test2.Last Test Date	Test3.Last Test Date
2	Wiggins	Chief	Y	12/14/2018	12/14/2020	12/31/1901	12/31/1901	2/20/2019	2/20/2019
3	Simpson	Homer	Y	9/26/2018	9/26/2020	12/31/1901	12/31/1901	2/20/2019	2/20/2019
4	Smith	Burke	Y	10/12/2018	10/12/2020	12/31/1901	12/31/1901	1/12/2020	1/12/2020
5	Kingsley	Pearl	Y	11/19/2018	11/19/2020	12/31/1901	12/31/1901
6	Fecondiak	Paul	Y	9/5/2019	9/5/2020	12/31/1901	12/31/1901	1/5/2018	1/5/2018
7	Kerny	John	Y	8/22/2019	8/22/2020	12/31/1901	12/31/1901	5/8/2018	5/8/2018
8	Grande	Prairie	N			12/31/1901	12/31/1901	6/4/2018	6/4/2018
9	Deer	Red	N			12/31/1901	12/31/1901	7/19/2019	7/19/2019

Sheet: Sheet2

Review PQ
In the attached file
Click on any cell in the new table
On the Data Tab, click on Queries & Connections
In the right window, double click to open Query
Review PQ steps

M-code basics:
- "let" is the start of a query
- "in" is the closing of a query
- each transformation step sits in between those 2 lines
- each step line is ended by a comma, except the last one
- "Source" is always the first step (Source Data)
- After "in" you have the last step referenced


File is attached for your review

[mehmetcik]

Please Login or Register  to view this content.