lookup to see if a value(P/N) has more than one parent part

[jham]

Good Day!

I am having trouble figuring this out through a formula and im wondering if its possible through a VBA code. I will attach a sample file.
It needs to lookup a P/N for eg.(9791008)and give me a 1 if that specific P/N only has 1 Parent (3791010)which is 6 columns to the right and a 2 if it has more than one parent.
It is basically a vlookup that finds its value which is 6 rows over, but then needs to start after the P/N(9791008) it just referenced and see if the next occurance of (9791008) has the same parent part as the previous occurance.

Thanks very much in advance!

[p24leclerc]

Here is a macro that might help you. You'll have to crreate the criteria and output range in the columns M and N in order for this macro to work properly. It put the number 1 or 2 in column G. See attached file.

Please Login or Register  to view this content.

Let me know if it is what you were looking for.
Pierre

[jham]

Thanks Pierre, thats looking like its working awesome!

Since my next step is a variation of that last code, i might as well just ask this on this thread. now for the other sheet "MRP", I need a code that is going to add the "planned order release" from each of those parents. if possible i would like this to be a code that can be in the "Gross Requirements" cells of the charts. If this is possible it would be such a great help.

So for an example the chart with the part number "3791010", in the gross requirements cells, would need to lookup and add the POR cells in the charts of its two Parents in this case which are "B270850 & B270850C" and put them in the GR cell in the same time period.

Any help would be greatly appreciated.
And once again thanks so much for the first part!!!
Jay

Ps. if you used the original attachment there is still numbers in the chart which may make it easier.
Note: this will have to be able to go in many more charts via copy/paste but will always be in same spot relative to the above chart

[p24leclerc]

Sorry but I don't understand what you want.
You file is linked to other files I don't have and I have lots of formula in error. May be it does not help me to understand your request.
Can you upload a more complete file? Or be more detailed in your explanation may be with a sample in a workbook. That would help for sure.

[jham]

Hi again,

ok i have attached a new sheet that should show better, i have colour coded where the requirements come from.

So when i said it is close to that macro i mean that the gross requirements row needs to lookup all the parents(of the part number in top left of chart from the BOM sheet) and then match(the parents) with the charts in the MRP sheet. it then needs to add the parent Planned Order Releases which becomes the Gross Requirements for the child part.
to me it seems basically the same other than it finds the parents and then adds POR's together in the MRP sheet.

Like i said earlier this will need to be applied to many more charts, so somehow it will need to work in every Gross Requirement row

thanks in advance!!!

[p24leclerc]

This would destroy the formula you have in GR for each part. Is that a problem?

[jham]

No that is not a problem that is what i need. that formula either wont add all the requirements from all the parents because it cannot continue the lookup. the other way i tried doubled the requirements for some of the parts in certain situations

thanks!

[p24leclerc]

Hi, I think the attached file works like you want.
You have to make sure the workbook you want to work with is exactly as the one in the attached file. The name of the sheets have to be the same. And the position of data in each sheet have to be at the exact same place.

You can modify the macro if you see any differences.

The macro will create the criteria and output range automatically and it will erase it at the end.

It tooks about 20 minutes on my old computer to scan all the data and I think it is only a small sample of what it should be. I hope you have a better computer than mine. anyway I suggest you run it during nighttime.
Good luck
Pierre

[jham]

Hi,

So that almost works perfectly except for when it adds the requirements it should only be for the period that it is in. for example a planned order release of 100 for period 8. the GR for period 8 of that child should be 100 as well, where now it adds all the POR of all parents for all of the previous periods.

Other than that it looks awesome and i am so happy.

Ps I wish i could do this im sure its fairly simple but I'm still a rookie in VBA.

[p24leclerc]

I forgot to reset the GR_val variable to zero after each search.
Make this small change and it should be OK.

Please Login or Register  to view this content.

[jham]

Good afternoon,

I have attached a new workbook without the "C$" in the component qty column.
It is working well so far I turn manual calculations on and now it works a lot faster.


thanks
JHam

[p24leclerc]

OK, Lets try this code. Copy it to your module in your file and run it.
It will create all titles, copy the 2 columns and paste it as values only and it also sorts data by LEVEL.
I also inserted some shortcuts to improve treatment speed.
It takes into account the component quantity and the scrap factor.
Check it thoroughly.
here is the macro code.

Please Login or Register  to view this content.

Pierre

[p24leclerc]

I modified the file to scan only once every items. I created a criteria and output range in columns Q, R and S where you'll find the number of parents for each item number. These columns will not be erased at the end of the macro like columns M,N and O which have no value after the macro is completed.
Pierre