Trying to automate conditional data entry from one worksheet to another

[mikeecoon]

First post.... hello everyone!

I 'm trying to figure out how to automate conditional data entry from one worksheet to another.
I need to automatically fill data from one worksheet to another
One sheet only has 3 columns of data Value Stream, Major, total labor pay that I care about. (I actually make a pivot table from it if that matters)
the other has 3 columns that need to match up Value stream, program name, and rework.
The data from "value stream" needs to match on both worksheets plus the data in "major" needs to partially match the data in "program name" (multiple programs in one cell)
THEN, if the about data lines up, I need the value from "total pay" to automatically input into "Rework".
This is way beyond my limited knowledge of macros and I suspect there will be some VB coding.
Can anyone help me? This has been taking me many hours and I know it can be much faster...

[Mumps1]

Because of the duplicate values in Value Stream and due to the fact that you have "blacked" out the data under Major and Program Name, it makes it difficult to see possible match ups. For example, You have 2 occurrences of "MPS" under Value Stream in the bottom sheet. Can the matches for each "Major" data to the right of these values be found in more than one row of Program Name? Also, we can't see any column letters or row numbers. It would be easier to help and test possible solutions if you could replace any confidential data with generic data and attach a copy of your file.

[mikeecoon]

Ok sorry. Basically, I need to fill data from total labor pay into the appropriate rework$ cell based on customer and program. The data varies and not all values get filled every time. Hopefully, my example spreadsheet attachement helps.

Edit: CAS and MPS both get reported into MPS. Also, B2 value stream should have an entry I accidentally left it out.

[Mumps1]

What do you mean by:

CAS and MPS both get reported into MPS

[mikeecoon]

There are 3 value streams. CAS no longer exist and was combined into MPS but for some reason our system still reports CAS separately in the rework sheet. So I want to take the value in total labor pay and copy it to the other sheet into the rework$ cell but value stream and minor have to match value stream and program name in the other sheet. I end up with a pretty large spreadsheet and I am having to search and enter each one manually which takes a lot of time with risk of typos. I just know there has to be a better way.

Edit: at the very least I'd like someone to point me into the right direction so I can research better. Google hasn't helped me much.

[Mumps1]

It appears that all the values in column C (Major) are unique values. This macro is based on that assumption. If this is not the case, please let me know.

Please Login or Register  to view this content.

[mikeecoon]

Getting close...Thank you. Yes Column C should always be unique. I'm noticing the multiple values that go into the same cell are getting overwritten verses being added together. For example Customer1 has values for abc and edf. Those two should be added together. Instead its just displaying the value for edf (probably because the code found it last). Also, for some reason customer3 has 0.00. There should be something in there. I can't tell why it's not picking it up. I see the logic but this coding is a little more that I fully grasp at the moment.

[Mumps1]

Try:

Please Login or Register  to view this content.

Before running the macro, the "c1 " in the Major column has a trailing space at the end. Delete that extra space. Also, customer 4 and customer 6 data is missing a trailing bracket at the end. Please put in that missing bracket. Please check all your data for this kind of typo.

[mikeecoon]

Holy heck! I think you have done it! Hopefully it goes smoothly on next weeks report but from what I've seen just testing it out this will do nicely. Thanks so much.

[Mumps1]

You are very welcome.

[mikeecoon]

Actually, the numbers aren't coming out right. I think it's because the real thing is a little more complex. Maybe I could try to upload something closer to the real thing at a later date? It's very close to what I need though.

[Mumps1]

OK. Post an updated copy of the file to this same thread and I will have a look.

[mikeecoon]

Here's a better sample of what I'm dealing with. I need to do the same for scrap and rework. For some reason it's not adding up right.

[Mumps1]

After looking at your new data, I don't think it will be possible to do what you want. VBA relies on patterns. The pattern my macro used was the use of one set of brackets and the values between the brackets being separated by commas. Some of your new data has 2 sets of brackets and sometimes one of the brackets is missing. If I searched for the Major value of "DFAS" from Rework and "H1" from Scrap, this Program Name: "Bell Helic (H1, DFASH1, BELLH1, BH1RWK" would be a problem as "DFASH1" contains both values that are being searched. This name: "Boeing (TX, St. Louis BDI,)(MD-10, MD10, B717, BLI-2, BLI-3, BoeingBLI)" has a comma to the right of "BDI". These may seem trivial but any deviation from a set pattern will create problems. I really don't know of a way around this.

[mikeecoon]

Oh well. Worth a shot. I truly appreciate you trying so hard. I suspected the issues you mentioned might be the problem and I was beginning to think the format needs changing to get this to work.

[Mumps1]

If you can change the format somehow to be consistent, post back with a copy of the file. Good luck with it.