Copy data from one part# on sheet 2 to the same Part# on sheet 1

[sald]

I need help, this is driving me crazy!

I think this can be done?

What I am trying to do is automatically move the date received and the total qty received from the Rcvg. Log sheet to the RFMs Release sheet.

The problem is:
1) I don't know what I'm doing
2) On the Rcvg. Log several part numbers that are the same with different Qtys.
3) on the Rcvg. Log the are several part numbers that are the same using different programs.

What I would like to do is combine all the same part numbers with the same program add the total Qty received and paste the date received and total Qty into the RFMs Release sheet in the proper part numbers row.

Is this possible?
Attached is a shortened copy of the spreadsheet normally there about 3000 entries.
If anyone has nothing better to do I would appreciate the help.

[rylo]

Hi

1) What is the significance of the distinction between points 2 and 3.
2) What do you want to do when you have multiple dates for the same Partnumber / Program combination?
3) What do you want to do when there isn't a part number / Program combination?


rylo

[sald]

Thank you for your reply.

I'll try to answer your questions as best I can.

1) Each time a shipment arrives they log the part number, program number and quantity. I think Excel would have to look at the part number first then the program number then add the quantities.
2) Good question, sense this will be done on a daily basis the latest date should be entered,
3) Flag that entry red on the Rcvg Log.

This is off the subject but I was thinking about trying to learn VBA. I have a little programming experience (Visual Basic). Do you have any suggestion for books or programs?

Thanks again for your help

[rylo]

Hi

So rcvg.log has new data appended daily, and only the most recent data is to be actioned? What happens if you miss a day? How do you know what has been actioned and what is yet to be actioned?

rylo

[sald]

Rylo

What I was thinking is use a VBA user form with (part#, Program#, date, ect.) when the submit button is executed run code that would enter all the information into the Rvcg Log spreadsheet and search the RFMs Release spreadsheet for the proper part# and program# if found enter the date into the proper cell. If not found highlight the data just entered into the Rcvg Log spreadsheet red to alert the user that the part was not found in the RFMs Release spreadsheet.

What do you think? can this be done?

Thanks for all your help......

[rylo]

Hi

Using your example file, can you document some items from Rcvg. Log and explain exactly what should happen to them. Try to pick a some that have multiple entries across 2 or more dates.


rylo

[sald]

Rylo
1) I’ve completed the user form for sheet Rcvg. Log. What this simply does is ask for the Part#, Program#, Qty, Date Received, Vendor, and location. Then it finds the next available line on the Rcvg. Log sheet and enters it in the correct location.

2) I’d like to take that one step further and have it also reference the part# and corresponding program# in the RFMs Released sheet and paste the date and qty in the matched cells. This way a part would be dealt with one at a time. When an identical part is entered do the same as above but add the qty’s to keep a running total and if the date is newer paste the new date.

3) If the part# and program# can’t be referenced in the RFMs Release sheet simply carry out step 1 but then highlight the row to alert the user that the part could not be found in the RFMs Release sheet.

Example:
Referenced from Rcvg Log sheet

DATE QTY PART # PROGRAM LOCATION
11/17/2008 2 6444412-02m1 D6K 900
11/18/2008 1 6444412-02M1 D6K BLDG 900


The end result in the RFMs Release sheet would be :

Program Account Number RFM Date Ordered Due Date Date Received Qty O Qty R Part # Description
D6K 35281.118150.100 1688 11/18/2008 1 3 6444412-02M1 PANEL, DOOR, EXT, LH

I think this is the best way to do this although any suggestions are welcome.

Once again thanks for taking time to help me it is appreciated.

[rylo]

Hi

What the code below will do is action all the items on the log sheet. If it finds a matching entry, it will copy over the total amount from the log sheet, and as it is in date order, will eventually have the most recent date of an item being received.

If there is no match, then it will color the log red, and if there is more than one instance on the release sheet, it will color the item blue (refer to line 44 in the log sheet - this appears on lines 107 and 108 of the release sheet)

Please Login or Register  to view this content.

Once you have all these actioned, you could restrict the code to the item being added, or you could just let the whole thing run in total each time.

rylo

[sald]

Rylo,


Your code works perfect....

Thanks for your time and help.

Steve

[rylo]

Steve

If this has solved your problem, can you please go back to your original post, edit it and mark it as solved.


rylo