How do I do this?

Dhawk

New Member
Joined
Aug 28, 2002
Messages
13
I have two different sheets. One has all the associate info, the other has just associates with the date their certification was issued. Not all the employees have certifications, so the cert sheet will have less names.

What I would like to do is find a way to merge the cert info to the master associate info sheet. Is this possible?
JulyCertData.xls
ABCDEFGH
1RegDistLocAssociateNameJobTitleIssued
2076020765501852Roddenberry,JohnBTechnician1/2/02
3084010846506533Diaz,DavidBMgrServCntrEx1/4/02
4083010825001829EllingsworthJr.,ErnieD.Technician01/07/02
5083010825003115Schild,GlenMTechnician01/08/02
6078000776501811Gregory,StephenE.MgrMarket01/09/02
7073010726500794Hall,ClintMTechnician01/11/02
8073010726505845Bonners,JayMobileProSpecialist01/11/02
9078000776501253Price,DerreckL.Technician01/11/02
10078000776501863Conti,DanielPTechnician01/11/02
11078000776501863Rios,ErnestoTechnician01/11/02
12083010825001829Ames,RichardK.MgrDCCAssist01/11/02
13078000776505698Peart,RussellB.MobileProSpecialist01/14/02
14078000785001867Blaszczyk,LawrenceATechnician01/14/02
15083010826506202Lonn,ScottTechnician01/14/02
16077010805000687Ray,KennethM.MgrServCntrEx01/17/02
17077010805000687Scott,TedL.Technician01/17/02
18077010805000687Waters,JamesG.Technician01/17/02
19077010805001825Strawn,WayneA.Technician01/17/02
20076020747501186Smith,CliffordLTechnician01/23/02
21074010738506385Deemer,EdmundTechnician01/25/02
22076020768501812Meardry,KevinTechnician01/25/02
23073010719501871Collette,BrianTechnicianTrainee02/08/02
24076020765500795MoselyJr.,RobertL.Technician02/18/02
25074010745005840Holden,BenjaminWMobileProSpecialist02/20/02
26074010745000363Thongvong,KathingtongTechnician02/21/02
27078000776501820Mauersberg,DanielDTechnicianTrainee02/21/02
28
Sheet1
<SCRIPT language=JavaScript src="http://www.interq.or.jp/sun/puremis/colo/popup.js"></SCRIPT><CENTER><TABLE cellSpacing=0 cellPadding=0 align=center><TBODY><TR><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid" bgColor=#0c266b colSpan=10><TABLE width="100%" align=center border=0><TBODY><TR><TD align=left><FONT color=white>Microsoft Excel - JulyAssocInfo.xls</FONT></TD><TD style="FONT-SIZE: 9pt; COLOR: #ffffff; FONT-FAMILY: caption" align=right>___Running: xl2000 : OS = Windows 98</FONT></TD></TR></TBODY></TABLE></TD></TR><TR><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; HEIGHT: 25px" bgColor=#d4d0c8 colSpan=10><TABLE width="100%" align=center border=0 VALIGN="MIDDLE"><TBODY><TR><TD style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: caption">(<U>F</U>)ile (<U>E</U>)dit (<U>V</U>)iew (<U>I</U>)nsert (<U>O</U>)ptions (<U>T</U>)ools (<U>D</U>)ata (<U>W</U>)indow (<U>H</U>)elp (<U>A</U>)bout</TD><TD vAlign=center align=right><FORM name=formCb059465><INPUT onclick='window.clipboardData.setData("Text",document.formFb543420.sltNb142651.value);' type=button value="Copy Formula" name=btCb290334></FORM></TD></TR></TBODY></TABLE></TD></TR><TR><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid" bgColor=white colSpan=10><TABLE border=0><TBODY><TR><FORM name=formFb543420><TD style="WIDTH: 60px" align=middle bgColor=white><SELECT onchange="document.formFb543420.txbFb965317.value = document.formFb543420.sltNb142651.value" name=sltNb142651><OPTION value="" selected>A1</OPTION></SELECT></TD><TD align=right width="3%" bgColor=#d4d0c8>=</TD><TD align=left bgColor=white><INPUT size=80 value=Reg name=txbFb965317></TD></FORM></TR></TBODY></TABLE></TD></TR><TR><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle><CENTER>A</CENTER></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle><CENTER>B</CENTER></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle><CENTER>C</CENTER></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle><CENTER>D</CENTER></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle><CENTER>E</CENTER></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle><CENTER>F</CENTER></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle><CENTER>G</CENTER></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle><CENTER>H</CENTER></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle><CENTER>I</CENTER></TD></TR><TR><TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"><CENTER>1</CENTER></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: top; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Tahoma; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: center"><U>Reg</U></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: top; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Tahoma; BACKGROUND-COL
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

Dhawk

New Member
Joined
Aug 28, 2002
Messages
13
I guess you can't view both sheets in one post. Here is what the associate info sheet looks like.
JulyAssocInfo.xls
ABCDEFGHI
1RegDistLocAssociateNameSocSecNoJobTitleIssued
2073010717501203SilviaIII,PeterJ.MgrServCntrEx
3073010717501203Rocha,RuiTechnician
4073010717501203ScullyJr.,WalterJ.Technician
5073010717501204Camelo,EmanuelMgrServCntrEx
6073010717501204Amaral,ScottM.Technician
7073010717501204Cabral,StevenJTechnician
8073010717501211Umana,HectorE.Technician
9073010717501212Machado,LisaA.MgrServCntrEx
10073010717501212Arroyo,EdwinTechnician
11073010717501212Machado,JosephJ.Technician
12073010717501213Vokey,KennethMgrServCntrNe
13073010717501213Smith,MichaelTechnician
14073010717501214Sousa,JeffreyM.MgrServCntrEx
15073010717501214Byers,DarrenPTechnician
16073010717501214Faria,JoseF.Technician
17073010717501222LLoyd,JamesNMobileProSpecialist
18073010717501227Lloyd,ShawnT.MgrServCntrEx
19073010717501227Sabeti,NematollahS.Technician
Sheet1
 

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234

ADVERTISEMENT

Depending on exactly what you need, this should'nt be too difficult. But - it's not clear (to me at least!) what this actually is! You didin't indicat which of your 2 posted 'sheet 1's' was the master. which the 'cert' sheet; you didn't indicate what you meant by 'merge' - return a field from the cert to the master if there's a match between the two? flag master record with arbtrary value if there's a match? genuinely 'merge' so all records are combined??

More details please...

Paddy
 

Dhawk

New Member
Joined
Aug 28, 2002
Messages
13
<Q>You didin't indicat which of your 2 posted 'sheet 1's' was the master.</Q>

That would be the JulyAssocInfo.xls shown on my second post.

<q>you didn't indicate what you meant by 'merge' - return a field from the cert to the master if there's a match between the two?</q>

Yes, that sounds right.

<q>flag master record with arbtrary value if there's a match? genuinely 'merge' so all records are combined??</q>

I am still a bit of a newb to excel, so I am not sure I understand the question.

I will try to explain. The "JulyAssocInfo" would be a master record of all the employees in any given month (usually about 2800). The JulyCertData is a worksheet with the date that any given employee has received a certification. They are similar layouts, with each showing the Region, District, store location,SS#(I deleted SS# info for this post), Associate name, and job title.

The difference between the two workbooks is that the AssocInfo sheet shows all of the employees and the Certdata sheet shows what date they recieved a certification.

What I would like to do is, find a way to bring the certification date from the certdata sheet over to the AssocInfo sheet to the appropriate employee.

Thanks for your help.
 

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
OK - try this:

in G2 of the associnfo sheet, put:

=vlookup(d2,[JulyCertData.xls]sheet1!$D$2:$F$27,3,0)

and copy down as needed.

You will need to change the $F$27 to whatever is the last cell in your live data range.

Post back if you need..

Paddy
 

Forum statistics

Threads
1,144,765
Messages
5,726,173
Members
422,659
Latest member
RGP268

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top