Challenge two worksheets query and merge !!

[jvmatias]

Dear Excel Gurus

I have tried looking up answers and never realized how difficult it would be to bring data from two worksheets into one- seemed simple when I took on the task.

So here goes.....I am working with two worksheets that are different sizes (meaning unknow number of entries as it depends on month some more than others), with different headers that need to be merged into a worksheet with all the columns copied side by side on the same row.

WORKSHEET 1- Producers

Prod_rec	Prod_name	Prod_attn	Prod_str	Prod_city	Prod_st	Prod_zip	Prod_phb	Prod_fax	Prod_email
001	Good Inc.	Suite 200	4 Good St	Today	CA	32457	800-123-4567	352-222-1234	good@good.com
GV3	Happy Way	Building U	20 Years Ago Rd	Time	FL	33184	800-444-4567	203-456-7890	Happy@happy.com
LL2	Time Today		45 Sun Terrace	Sunnydale	VA	32784	888-145-5687	321-123-4568	Time@time.com
354	Excel Inc.		34 Some Street	Cow	TX	12345	888-894-7894	200-523-1654	Excel@excel.com

<TBODY>
</TBODY>

Worksheet 2- Broker

Br_rec	Br-name	Br_attn	Br_str	Br_city	Br_st	Br_zipcode	Br_phb	Br_email	Br_hdg	Br_note	Br_ccode
ABCD354	Excel Inc.	Suite A	34 Some Street	Cow	TX	12345	888-894-7894	Excel@excel.com	1/11/11	Leo x12	IN
LOSTLL2	Time Today		65 Fun Terrace	Sunny	WY	58457	887-452-7894	Time1@time.com	NOONE		AL
BRAV001	Good Inc.		4 Good St	Today	CA	32457	800-123-4567	good@good.com	05/11/11	In GA	BB
GOEDGV3	Happy Way	Building U	40 Years Ago Rd	Sunnydale	VA	32784	800-444-4567	Happy@happy.com	INACTIVE	www.a.com	HB

<TBODY>
</TBODY>

So NEW WORKSHEET- RESULTS would look like this

001

Good Inc.

Suite 200

4 Good St

Today

CA

32457

800-123-4567

352-222-1234

good@good.com

BRAV001

Good Inc.

4 Good St

Today

CA

32457

800-123-4567

good@good.com

05/11/11

In GA

BB

<TBODY>
</TBODY>

<TBODY>
</TBODY>

SO what I tried was to get the 001 from one worksheet to find it in the other worksheet and pull data for both in the third workbook side by side. SO basically use producer record number and pull both records into one row.

NO LUCk So any ideas????

Have never programmed macros, tried using basic functions.

 

[Smitty]

Welcome to the Board!

If the Prod_rec number exists in the other worksheet you should be able to use VLOOKUP. Note that's if the unique ID's are in the leftmost column, otherwise you can use INDEX/MATCH.

If you have a lot of records (and are using Excel 2010+) then you can use PowerPivot to link the tables and create your desired output.

But if you don't have a unique ID between the two recordsets then you'll have a tough time trying to pull them together.

HTH,

 

[jvmatias]

Thanks for your welcome

They are two different headers Br_rec vs Prod_rec that have can be cross referenced by the three digits, will look into how to use PowerPivot and INDEX/MATCH. Wish me luck

 

[Smitty]

If you add a column to the second sheet (column B) you can use this:

=RIGHT(A2,3) to pull the last three digits, and then use VLOOKUP on that column.

 

[jvmatias]

I did:

=VLOOKUP(Producer!A2,--RIGHT(Broker!A3:A59,3),2,FALSE) this gave me #N/A

==VLOOKUP(Producer!A2,Broker!A3:59,2,FALSE) this gave me #REF

 

[Smitty]

For the first see:

Vlookup to match partial text string in array and return value - Microsoft Community

For the second, you're missing the full range reference:

Broker!A3:xx59

Where xx represents the last column in the dataset you're trying to retrieve.