Reading multiple sheets with different headers and merging data - Help required

Question

Dear All,

I have 3 sheets in an excel file:

"Summary" - this contains the order number and invoice number along with description of the product

"List" - this contains the actual data

"Desired_Output" - desired output

I have some information on "Summary" sheet which i need to place as a column and most of the information on "List" sheet which contains the main data.

On List sheet - I need to merge row 4 and 5 to create a column header and from row 6 onward is my data which comes under these headers.

I have attached my sample workbook, If You can kindly help me with the attached would be really grateful.

Many thanks

SampleQuestion.xlsx

Sebastiaandb · Answer

@Data-Nuker

Here you go. You can utilize named ranges in Excel, it makes it a bit easier to proces. Still, it would be way easier if you could throw all your parameters in a simple table :-).

Hope it helps!

SampleQuestion.xlsx

Excelranges.yxmd

j_acon · Answer

You can open each sheet with it's own Input Tool, then use Union, Append or Join to merge the data.