Advanced Excel Formula

Hi, i wanted to know whether we can bring Item Batch in Parellal Line to Item Name as shown in Excel Attached Format. 'Present' Sheet contains the default report derived from system. 'Required' Sheet contains the desired result required. I know it seems illogical but some how using advanced excel functions as search, isnumber, if, text, we can crack it out. I am not too sure of success in this just giving it a try for some ultra knowledged excel expert. Thank YOu in advance.