Consolidate / Merge data in multiple sheets in Excel 2010

[grcshekar]

Hi,

In the attached sample I have 11 sheets of sample data.

I need to get only data (that is Sl No , Emp ID, Employee Name, Account Number , IFSC COde and Net Amount in one single sheet

How can I do this please

[alansidman]

In the attached, I used Power Query to append each of the sheets to the other. First, I cleaned each sheet in PQ so that only the table of data was available for merging. Then merged the sheets. Open Power Query/Get and Transform. If you don't already have it, you can download from MS. It is a free add on for Excel 2010




Review PQ
In the attached file
Click on any cell in the new table
On the Data Tab, click on Queries & Connections
In the right window, double click to open Query
Review PQ steps

M-code basics:
- "let" is the start of a query
- "in" is the closing of a query
- each transformation step sits in between those 2 lines
- each step line is ended by a comma, except the last one
- "Source" is always the first step (Source Data)
- After "in" you have the last step referenced