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