How to show merge data from two sharepoint list and show it in excel

Question

I have two SharePoint lists A and B which have 2 same columns namely "Task" and "Details". I want to merge the data from both list and display it in excel sheet which show current data every time it is opened. I know how to show data of one list in excel and I am familiar with the concept of linked data source to merge data of both list and show in aspx page using sharepoint designer but not sure how to display it in excel

Answer 1

There are a couple of options but the one I know it is using SharePoint REST API.

All you need is to create 2 queries and later use "Append" or "Merge" to get results combine, here it is an example: you have two lists named ListA and ListB, you query each into Excel like using a REST API call, something similar like the following:

For the first sheet:

http://domain-name/_api/web/lists/GetByTitle('ListA')/items?$select=Title,Task,Details

And the second sheet:

http://domain-name/_api/web/lists/GetByTitle('ListB')/items?$select=Title,Task,Details

Once you have the data sources (QUERY1 and QUERY2) into your Excel, you can go to the Merge or Append, and combine both queries into a new data:

This is an example of an APPEND:

And this is an example of a MERGE: