How to pick the associated rows w.r.t search string?

[vbaswal]

Hi All,

I'm not new to Excel VBA but not an expert too. I'm in strange problem, someone plz help me I'm not able to think anymore.

My Excel Story:
I have some 40,000 rows in a spreadsheet. rows are in the pattern mentioned as below:

Source
Destination
Destination
Source
Destination
Source
-----------------------------------------------------------------------------------

Now I want to write a program to search for an application lying either in Source Row or Destination Row. If it is found in Source then I need all corresponding destination rows and if found in Destination row then all associated destinations as well as the source rows in another sheet.

for eg case 1) XYZ app is found in Source:

Source - XYZ found
Destination - required
Destination - required

case 2) XYZ app is found in Destination
Source - required
Destination - XYZ found
Destination - required

case 3) XYZ app is found in Destination
Source - required
Destination - required
Destination - XYZ found


Is there anyone who can guide and assist me I have spent my 3 days in this but not got any result.
I have a very critical schedule for preparing this inventory sheet I was doing it manually and it was taking 5-6days to do manually. I thought of automizing it but got stuck.

Please find the excel attached , suppose I'm searching for ACS app name then it should bring all source or destination rows associated to ACS position (Source/Destination) to next sheet.

[shg]

Post a workbook and explain in an example.