index match between the nth and nth +1 value

[cwichlaz]

Good Evening,

I have a imported report that is pulling assembly part numbers into column b. In column a I have at random the word "Parent", This is the start of a new assembly. I would like to pull all the part numbers between the words parent and place them in column c. I have tried a verity of items to try and get it to go with out luck.

This seemed to get me the code above and to the right of where I needed to be sometimes other times it was just out in space.

Please Login or Register  to view this content.

This was entered as an array Control, Shift, Enter.

I have tried several different versions of this

I have attached a sample file

[Phuocam]

Try:

=IFERROR(INDEX($B$1:$B$150,SMALL(IF($A$1:$A$150="parent",ROW($A$1:$A$150)),ROWS($A$1:A1))),"null")

[cwichlaz]

Try:

=IFERROR(INDEX($B$1:$B$150,SMALL(IF($A$1:$A$150="parent",ROW($A$1:$A$150)),ROWS($A$1:A1))),"null")

Thank you for the quick reply. That worked beautifully. Now tomorrow I'm off to figure out how to find the children part numbers.

Chris

[cwichlaz]

Now I just don't know if I just can't see the forest from the woods. My mind is at a blank on how to find the children part numbers.

I'm trying to think thru the logic. First define the upper limit parents and the lower limit parents. Then identify all the parts in between.

I guess I need to figure out nth and nth +1 parents cell locations then place my index small to limit to that range?

So in the sample #2 attached I need to use to find the defined 1st parent cell location

Please Login or Register  to view this content.

Please Login or Register  to view this content.

This defines the upper and lower control limits.

I need to now look at the data pulling.

Please Login or Register  to view this content.

I need what is between the parents. In the sample #2 file the cells in green are the number I would like to find. The numbers in yellow are the parents