Capturing Multiple Column’s Data in a Single Column Based on Criteria

Dear Expert,

Need Your Support On my Subjected Query!

In Analysis Worksheet: -

Range AQ9:DC9 We have Finished Goods Part Number

Range AO10: AO223 We have Raw Material Part Number

Range AQ10:DC223 We have Usage of Raw Material Against Each Finished Goods Part Number

Support Required: -

Range AQ10:DC10 wherever value >0; the Corresponding Finished Goods Part Number to be captured In Cell D10

Same to be repeated for each Rows to the extent of value exits in Column AO

Note:

Above mentioned Ranges are completely dynamic, in my actual file I have 1000+ Finished Goods With 3000+ Raw Materials

Looking for your valuable suggestion!

Thanks & Regards,
Rajeshkumar R