Iterative Macro to count Levels

[JordyMicheal]

Hey there!

Need a little help building out an iterative macro that checks for levels of parts.

As an example in this sample data. 19052 would be a Parent Part. It is made up of the child parts 19052C.2, 19250, 92922.2, and 92934.5.

The part 19052 and the records 2 through 5 would be a Level 1 part, as they go into a top tier.

The records below with PartNumber 19032C.2 would be Level 2 parts, as they go into making Level 1 parts.

The records with PartNumber 90062 would be Level 3 parts, as they go into making Level 2 parts. So on and so forth.

There is an undefined level of parts.
I know I need to make an iterative macro to loop through them and section out which records, but I've been staring at the problem for too long!!

It's preferable that the levels take place after the other work that has been done!

[gabrielvilella]

Hi @JordyMicheal, what is that you need as output? I see on your file that you are multiplying parent quantity by child quantity. 

[JordyMicheal]

Hey @gabrielvilella!

So what I would hope for is just another column at the end of that dataset (say called Level), and then just a numeric value.

What I highlighted in green would be 1, and red would be 2, the next set would be 3.

Does that make sense?

[gabrielvilella]

If the part number above is the same as the one below, keep the level number. Otherwise increase level by one. Is this what you are looking for?

[JordyMicheal]

It's a bit more complex than that unfortunately. The sample data is just a simple example, but there are thousands of parts. So a parent child key might be an easier way to tackle it as an example, but that has lead me down a bit of a rabbit hole. Here would be a more complete look of the data

[gabrielvilella]

I see what you mean now, you have the child part number field there listing all possible options for each part number. Do you have a list of all part numbers that should be level 1?

[JordyMicheal]

I don't sadly... Though that would make it easier

[gabrielvilella]

On you dataset, both 18063 and 19052 part numbers have the 19052C.2 as child. On this case which one should I consider as level 1 for that child number?

[JordyMicheal]

Depends on the line. You don't have a complete dataset so understand parts could be used at any level in any other part.

[gabrielvilella]

The same child part number has multiple parent part numbers on different levels, see 900461 for instance. There needs to be a one to one relationship, one child can only have one parent. Unless you just want to see what is the highest level a number can have.