Assume a file with two worksheets. The first worksheet has data in column A:H (8 columns) and the second one has data from B:J (9 columns). One may want to create a third worksheet with specific columns of data appended from both worksheets – the columns to be shown in the third worksheet are not at the same position on both worksheets i.e. while the Inventory Date data is in column A on sheet1, it is in column H on sheet2.
You may refer to my solution in “Resultant Data – final” worksheet of this workbook.
I have solved this problem using Power Query – a free add-in from Microsoft for Excel 2010 and higher versions. The result is dynamic and refreshable (just as in a Pivot Table).
You may watch a short video of my solution here