How do I do this?

[Dhawk]

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
	A	B	C	D	E	F	G	H
1	Reg	Dist	Loc	AssociateName	JobTitle	Issued
2	07602	07655	01852	Roddenberry,JohnB	Technician	1/2/02
3	08401	08465	06533	Diaz,DavidB	MgrServCntrEx	1/4/02
4	08301	08250	01829	EllingsworthJr.,ErnieD.	Technician	01/07/02
5	08301	08250	03115	Schild,GlenM	Technician	01/08/02
6	07800	07765	01811	Gregory,StephenE.	MgrMarket	01/09/02
7	07301	07265	00794	Hall,ClintM	Technician	01/11/02
8	07301	07265	05845	Bonners,Jay	MobileProSpecialist	01/11/02
9	07800	07765	01253	Price,DerreckL.	Technician	01/11/02
10	07800	07765	01863	Conti,DanielP	Technician	01/11/02
11	07800	07765	01863	Rios,Ernesto	Technician	01/11/02
12	08301	08250	01829	Ames,RichardK.	MgrDCCAssist	01/11/02
13	07800	07765	05698	Peart,RussellB.	MobileProSpecialist	01/14/02
14	07800	07850	01867	Blaszczyk,LawrenceA	Technician	01/14/02
15	08301	08265	06202	Lonn,Scott	Technician	01/14/02
16	07701	08050	00687	Ray,KennethM.	MgrServCntrEx	01/17/02
17	07701	08050	00687	Scott,TedL.	Technician	01/17/02
18	07701	08050	00687	Waters,JamesG.	Technician	01/17/02
19	07701	08050	01825	Strawn,WayneA.	Technician	01/17/02
20	07602	07475	01186	Smith,CliffordL	Technician	01/23/02
21	07401	07385	06385	Deemer,Edmund	Technician	01/25/02
22	07602	07685	01812	Meardry,Kevin	Technician	01/25/02
23	07301	07195	01871	Collette,Brian	TechnicianTrainee	02/08/02
24	07602	07655	00795	MoselyJr.,RobertL.	Technician	02/18/02
25	07401	07450	05840	Holden,BenjaminW	MobileProSpecialist	02/20/02
26	07401	07450	00363	Thongvong,Kathingtong	Technician	02/21/02
27	07800	07765	01820	Mauersberg,DanielD	TechnicianTrainee	02/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

 

[Dhawk]

I guess you can't view both sheets in one post. Here is what the associate info sheet looks like.

JulyAssocInfo.xls
	A	B	C	D	E	F	G	H	I
1	Reg	Dist	Loc	AssociateName	SocSecNo	JobTitle	Issued
2	07301	07175	01203	SilviaIII,PeterJ.		MgrServCntrEx
3	07301	07175	01203	Rocha,Rui		Technician
4	07301	07175	01203	ScullyJr.,WalterJ.		Technician
5	07301	07175	01204	Camelo,Emanuel		MgrServCntrEx
6	07301	07175	01204	Amaral,ScottM.		Technician
7	07301	07175	01204	Cabral,StevenJ		Technician
8	07301	07175	01211	Umana,HectorE.		Technician
9	07301	07175	01212	Machado,LisaA.		MgrServCntrEx
10	07301	07175	01212	Arroyo,Edwin		Technician
11	07301	07175	01212	Machado,JosephJ.		Technician
12	07301	07175	01213	Vokey,Kenneth		MgrServCntrNe
13	07301	07175	01213	Smith,Michael		Technician
14	07301	07175	01214	Sousa,JeffreyM.		MgrServCntrEx
15	07301	07175	01214	Byers,DarrenP		Technician
16	07301	07175	01214	Faria,JoseF.		Technician
17	07301	07175	01222	LLoyd,JamesN		MobileProSpecialist
18	07301	07175	01227	Lloyd,ShawnT.		MgrServCntrEx
19	07301	07175	01227	Sabeti,NematollahS.		Technician
Sheet1

 

[PaddyD]

</table>

 

[PaddyD]

just fixing up the missing tags...

 

[PaddyD]

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]

<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]

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