Merge and append data from two worksheets

{ 6 Comments }

Visualise a workbook with two worksheets. The tab name of the first worksheet is “My Table” and of the other worksheet is “My colleagues Table”. Each worksheet has 4 columns – First Name, Last Name, Pin code and Mobile Number. In “My Table”, the first, second and third columns are filled up and in “My colleagues table”, first, second and fourth columns are filled up. Furthermore, there are a few additional names on “My colleagues worksheet”. The task is to create a combined database from both these worksheets and for doing so, the following sub tasks have to be performed:

1. Bring over data from the 4th column of “My colleagues Table” to the 4th column of “My Table” (Merge data); and
2. Bring over the additional rows from “My colleagues Table” to “My Table” (Append data)

If you are using the Power Query add-in, then a few simple steps (no formulas) can solve this problem. The result will be dynamic and refreshable (just as in a Pivot Table).

You may download the workbook from here

You may watch a short video of my solution here

Leave a Comment Cancel reply

Your email address will not be published.

*

  • Hi Ashish,

    I am trying to consolidate multiple sheets(~75) into 1 master worksheet.

    The template for the sheets is the same ( all the data is in the same cells, well most of the time it is the same). I want to take everything from the sheets and transpose it so then I can filter the data. Not sure if this makes sense?

    Thanks

  • Hi Ashish

    When i right click on the 4th column, there has no option of Insert Custom Column…… my PC is updated 2020/ Please help to advise why

    Thanks
    Debbie