Data Search/Comparison and Extraction (VB Macro)

Dear All,

Can someone help me out in generating or updating my existing macro for Data search/compare and extraction (i.e., Compare and extraction of data based on keyword match and populate in new cell range)

I have clearly mentioned the requirements in Excel sheet enclosed (Please refer cells M6:M24 in 'Algorithm' worksheet)
Algorithm: for your kind reference.
1. In 'Input-data' worksheet, if the 'Data-Comparison' command button is clicked, the macro will search for keywords such as "CBUSH", "PBUSH" and "CONM2" in 'Input-data worksheet' and retain only the rows with keywords CBUSH (rows 172 to 179), PBUSH (rows 555, 558, 561, 564, 567, 570) and CONM2 (Rows 45 to 101).
[Search and Comparison check should be done for a generic data-set ie., it should run till the end of data 'n' or row limit of Excel (1,048,576 rows)]

2. For the keyword CONM2, please retain the consecutive second row (i.e one more row) along with the ‘keyword’ row i.e 2 consecutive rows
These intermediate outputs are populated in 'Intermediate Outputs worksheet' (A62:I77) under the title "Intermediate Outputs". Please refer the 'Intermediate Outputs' sheet for the format.
A3:J59 range for CONM2 data; M3:U8 for PBUSH; X3:AF10 for CBUSH data. Row should be capable to accommodate data until row limit 'n'

3. In 'Input-data' worksheet, if the 'Enter Inputs for CBUSH & CONM2 Nos.' command button is clicked, then the Userform will prompt the user to enter inputs [numbers as shown below] as input for CONM2 / CBUSH data extraction
Enter Inputs for CBUSH Nos: 75216904, 79216907, 75216901
Enter Inputs for CONM2 nos: 75216890, 75218243

3a. In the 'Intermediate outputs' sheet, search for the user input no. 75216890 in column C and data needs to be extracted within the CONM2 range (A3:J59) as follows*:
Extract data from column E, and in the second consecutive row, extract data from columns B to G. Repeat the same for the other user input nos. (75218243)
Outputs are populated in 'Outputs worksheet' (L4:S6) under the title "Final Outputs". Please refer the 'Outputs' sheet for the format.

3b.* In the 'Intermediate outputs' sheet, search for the user input no. 75216904 in columns AA & AB and data needs to be extracted within the CBUSH range (X3:AF10) as follows*:
Extract data from column Z. With this extracted data as reference, *check within the rows starting with PBUSH (range M3:U8) and extract the corresponding data from columns O to U
Outputs are populated in 'Outputs worksheet' (A4:I6) under the title "Final Outputs". Please refer the 'Outputs' sheet for the format.

I tried developing a VB routine based on the algorithm. The routine searches for the data line by line and compares. If a difference is found, routine is halted and cell selection box highlights identified row based on keyword match. But my requirement should follow the algorithm as explained before i.e extraction of data based on keyword match and populate in new cell range.
Please assume that the data in 'Input-data' sheet will always be present or a condition check can be kept, if the data is not present.

Requesting you to please get me an Excel Macro for this.
It will be helpful, if you could please add few comment lines to the code, for better understanding and also easier for further updates.

Thanks in advance,
Kind regards,
reachspk123
(Note: Please do not use Excel cell reference formulae anywhere in the sheet. I request you to perform calculations using VB macros only.)