Getting master list Data from multiple worksheets

[xcl]

Hi,
I have a lots of sheets.I want to get a master list from all those specific sheets.This means I want to get a unique list from Multiple worksheets from column A at user defined selection i.e referencing to a sheet in A2:A as the number of worksheets are many.
Those data is actually extracted from other softwares not properly formatted.I tried with Unique function but it has its own limitation to single worksheet only.

Secondly, I needed to get other columns data as well in which Matching would be required from multiple worksheets as it may not be available in one worksheet after getting output in A column.

So, basically there are two cases.

 

[jdellasala]

This can be done with relative ease using Power Query. Mr. Excel has a great YouTube playlist on it - Power Query, as does the Excel is Fun YouTube channel here: Full Advanced Data Analysis & BI Class (MSPTDA). Power Query, Power Pivot, DAX, M Code, Power BI & Excel that includes start and end Excel file samples. Time spent learning Power Query will save you hours of tedious manual work!