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

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


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

So NEW WORKSHEET- RESULTS would look like this

[TABLE="width: 500"]
<TBODY>[TR]
[TD]001[/TD]
[TD]Good Inc.[/TD]
[TD]Suite 200[/TD]
[TD]4 Good St[/TD]
[TD]Today[/TD]
[TD]CA[/TD]
[TD]32457[/TD]
[TD]800-123-4567[/TD]
[TD]352-222-1234[/TD]
[TD]good@good.com[/TD]
[TD]BRAV001[/TD]
[TD]Good Inc.[/TD]
[TD]4 Good St[/TD]
[TD]Today[/TD]
[TD]CA[/TD]
[TD]32457[/TD]
[TD]800-123-4567[/TD]
[TD]good@good.com[/TD]
[TD]05/11/11[/TD]
[TD]In GA[/TD]
[TD]BB[/TD]
[/TR]
</TBODY>[/TABLE]
[TABLE="width: 500"]
<TBODY>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</TBODY>[/TABLE]


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.