Excel 2007 : Nested Search Or I do not know what to call it !!

[R.Mati]

Hi All,
I am new in this forum, thanks for your answers in advance.

I am having an issue which I don't know how to solve it.

In my attched file I have two cloumns, the left always start with 416 number, the right number has 216 numbers and 416 numbers.
when you filter the left coloumn to show 41600081 it should show some 216 numbers and 416 numbers, you will get somthing like below

Material Component
41600081 21632826 <-------- Copy this into the same sheet (Sheet 216)
41600081 21633499 <-------- Copy this into the same sheet (Sheet 216)
41600081 41603275 <-------- do anather filter or search to look for 216 numers that related to this number

What I want is to copy 216 numbers into new sheet (Sheet 216) AND search for 216 numbers that related to 416 numbers in Component side

Material Component
41600081 21632826
41600081 21633499
41600081 41603275
41603275 216XXXX <-------- Copy this into the same sheet (Sheet 216)


I need to do that untill I found all 216 numbers.

Is that possible? I hope my English is clear to you, maybe not

Please if you know how to do it, I need your help....


Thanks so much again

[sushil10s]

See attachment

[R.Mati]

sushil10s I have checked your file, looks good but What I am trying to do is when if iam searching for a number let us say (41600081), a macro will give me all the numbers starting with 216, and if the macro find 416 numbers in its search it will go and find 216 numbers again and again.

Search number 1

41600081 -------> 21632826 <-------- Good
41600081 -------> 21633499 <-------- Good
41600081 -------> 41603275 <-------- Oooops this is not 216 .....> go and find 216 number for that

so the next step would be searching 216 numbers for our 41603275
we found the fellowing numbers in Search number 2

41603275 -------> 21600588 <------- Good
41603275 -------> 21600628 < ------- Good
41603275 -------> 41603098 < ---- Ooooops another 416 number ....> go and find 216 number for that and so on

Search number 3
41603098 -------> 21600465 <------- Good
41603098 -------> 21600498 < ------- Good
41603098 -------> 21600502 <-------Good
41603098 -------> 41602996 <------- Ooooops another 416 number
41603098 -------> 41603129 <-------Ooooops another new search
41603098 -------> 41600449 <-------Ooooops another new seach
41603098 -------> 41603166 <-------OMG another new search
41603098 -------> 41600451 <-------not again
41603098 -------> 41600452 <------- another nasty 416 number

and so on...

what is the best way to do that?