excel macro

[elenamccarthy]

I want to look at one excel sheet called "shipping and receiving log", I want it to find a "serial number" ex. W-111, then go to another excel sheet called "Serial Numbers" and find the correct instrument type ex. Worker. I want it to find the matching serial number from the first excel sheet ex. W-111, locate the date ex. 1-28-10 and then input it into the "shipping and receiving log". How would I write this up? Any code is appreciated.

 

[oldbrewer]

instead of having 2 workbooks can you have 2 sheets in the same workbook

or pull the data from the second workbook into your first workbook - that will make it simpler for you I think

 

[elenamccarthy]

See if you can look at this. C:\Users\emccarthy\Documents\EJHTMLe\TempJean.htm

If not i am not sure how to show you what i am trying to do.

 

[oldbrewer]

Shipping and receiving Log
Date received	RMA#	Customer	Model Number	Serial Number	cal	ship date	ship from	Manufacturing Date
9-18-17	12345	****	BT-1000	BT-692	c	1-20-17	UT	01/08/1997	#####
Serial Numbers
Date	Customer	Model	Serial Number
01/08/1997	****	BT-1000	BT-692
02/08/1997	fred	BT-2000	BT-777
			#####
			=OFFSET($C$6,MATCH(D3,$C$7:$C$8,0),-2)

<colgroup><col><col><col><col><col><col span="3"><col><col span="2"></colgroup><tbody>
</tbody>

 

[elenamccarthy]

Thanks i will give this a try.

 

[elenamccarthy]

ok, so what if i have multiple serial numbers that are the same. Is there a way to take the born date and copy it to that same serial number?

 

[oldbrewer]

please post your data as is to refresh our memories as 3 weeks has elapsed....

 

[elenamccarthy]

Date <tbody> </tbody> RMA # Customer Model Serial # Cal = C Ship Date Shipped from Manufacturing Date Received Repair = R City, State **** <tbody> </tbody> **** <tbody> </tbody> **** <tbody> </tbody> Solution S-392 **** <tbody> </tbody> **** <tbody> </tbody> **** <tbody> </tbody> **** <tbody> </tbody> **** <tbody> </tbody> **** <tbody> </tbody> Worker W-357 <tbody> </tbody> **** <tbody> </tbody> **** <tbody> </tbody> **** <tbody> </tbody> **** <tbody> </tbody> **** <tbody> </tbody> **** <tbody> </tbody> Worker Integra <tbody> </tbody> LT-0193 **** <tbody> </tbody> **** <tbody> </tbody> **** <tbody> </tbody> **** <tbody> </tbody> **** <tbody> </tbody> **** <tbody> </tbody> BT-1000 <tbody> </tbody> BT-1101 **** <tbody> </tbody> **** <tbody> </tbody> **** <tbody> </tbody> **** <tbody> </tbody> **** <tbody> </tbody> **** <tbody> </tbody> BT-1000 <tbody> </tbody> BT-687 **** <tbody> </tbody> **** <tbody> </tbody> **** <tbody> </tbody> 12/10/96 <tbody> </tbody> **** <tbody> </tbody> **** <tbody> </tbody> **** <tbody> </tbody> BT-IP <tbody> </tbody> IP-0194 **** <tbody> </tbody> **** <tbody> </tbody> **** <tbody> </tbody> **** <tbody> </tbody> **** <tbody> </tbody> **** <tbody> </tbody> MDT-100 <tbody> </tbody> MDT-1446 **** <tbody> </tbody> **** <tbody> </tbody> **** <tbody> </tbody> **** <tbody> </tbody> **** <tbody> </tbody> **** <tbody> </tbody> MDT-50 <tbody> </tbody> MDT-502 **** <tbody> </tbody> **** <tbody> </tbody> **** <tbody> </tbody> **** <tbody> </tbody> **** <tbody> </tbody> **** <tbody> </tbody> Verification Box <tbody> </tbody> CF-743 **** <tbody> </tbody> **** <tbody> </tbody> **** <tbody> </tbody> **** <tbody> </tbody> **** <tbody> </tbody> **** <tbody> </tbody> MDT-100 <tbody> </tbody> MDT-1527 **** <tbody> </tbody> **** <tbody> </tbody> **** <tbody> </tbody> **** <tbody> </tbody> **** <tbody> </tbody> **** <tbody> </tbody> ES-1000 <tbody> </tbody> ES-1036 **** <tbody> </tbody> **** <tbody> </tbody> **** <tbody> </tbody> **** <tbody> </tbody> **** <tbody> </tbody> **** <tbody> </tbody> LA-04 <tbody> </tbody> 504350 **** <tbody> </tbody> **** <tbody> </tbody> **** <tbody> </tbody> **** <tbody> </tbody> **** <tbody> </tbody> **** <tbody> </tbody> BT-1000 <tbody> </tbody> BT-687 **** <tbody> </tbody> **** <tbody> </tbody> **** <tbody> </tbody> 12/10/96 <tbody> </tbody> <tbody> </tbody>

<tbody>
</tbody>

i would like it to recognize duplicate serial numbers and take the manufacturing date and update it.

 

[oldbrewer]

so if say you have 3 identical serial numbers, do you want the latest date to be assigned to all three of them ?

 

[elenamccarthy]

well i want the date that it was made to be put in for all 3 of those matching serial numbers.

 

[oldbrewer]

so if bt 687 occurs anywhere higher up the list, you want the date from the nearest bt 687 above to be entered against the latest bt 687 ?