Merging two lists in Excel

Question

The Situation: I have two teams both on separate sheets of an excel document writing out the info of people they talk to each day (Name, customer number, Date etc).

The third team want a sheet with every person spoken to so they can sort through everyone, regardless of team.

My question is how do I have this sheet update automatically.

I've tried Consolidate but it messes with the data in ways i don't want (tries to numbers etc)

Here's a dummy example Team 1's sheet and Team 2's sheet. What I want is a dynamically changing sheet three. With all the entries of team 1 and team

This is what happens when I try and consolidate the two lists

This is what I what the list to look like. Except it adds new entries dynamically

TL:DR I have list one. I have list two. I need to make List three that has everything of both list inside it.

Answer 1

You can achieve this via Power Query or VBA. You might be able to achieve other ways, too, but they seem difficult.

Power Query solution:

First, in Team1 sheet, select the data and choose Insert Table. This will format your data as a table.

Repeat for Team2 sheet.

Next, go back to Team1 sheet and click on any cell within your table. Then go to Data tab... Get Data... From other sources... from table/range. This will open Power Query Editor (screen below).

Click the Close & Load button. It will create a new sheet with the name you listed (ie, Table1 was the default name given when opening the query editor).

Next, go to Team2 sheet and click on any cell within your table. Then go to Data tab... Get Data... From other sources... from table/range. This will open Power Query Editor. Again, select the Close & Load button.

Now you should have four sheets:

Team1: where team 1 records their data.

Team2: where team 2 records their data.

Table1: Don't touch, this is automatically fed from Team1 sheet.

Table2: Don't touch, this is automatically fed from Team2 sheet.

Next we'll append both queries to generate a new output object. Go to Data... Get Data... Combine Queries... Append. Within the append window, select both your tables. Click OK.

The Query Editor again opens. Click the Close & Load button.

Now you should have a 5th sheet called "Append1" or something similar.

Personal preference, I would probably hide the two blue sheets (Those automatically fed tables). Now your teams just need to update their respective yellow sheets.

Your third team will be the users of the Append worksheet. Just instruct them to press the Refresh All button within the Data tab, which will pull in New data.