Help needed combining data from multiple files with different data and multiple lines into one file

Question

Hi

I have a few files I need to combine into one. The first file is my base file (list of ID, names, address, etc) the second file lists documents that have been loaded to an external system (list of ID, document name, expiration date, etc.) To further complicate matters on this second file the supplier ID could be listed multiple times with different document names or the same document name. Also some of those document have a flag marked "invalid" or "expired". I need this information as well to be able to filter the final report to determine actions needed. I hope that all makes sense. I'm clueless as to what program to use. I looked at Power Query but since the source files have different data in each I can't combine them. I can't use look up because it will only pick up the first row of data for each ID. I don't know enough about Power BI if that's a possibility. Is going old school and using access a good option? Any suggestions would greatly be appreciated.

Answer 1

You may try merge query.

Result:

Answer 2

Ms query:

https://support.microsoft.com/en-us/office/use-microsoft-query-to-retrieve-external-data-42a2ea18-44d9-40b3-9c38-4c62f252da2e#:~:text=1%20On%20the%20Data%20tab%2C%20in%20the%20Get,Query%20from%20Excel.%203%20Double-click%20...%20See%20More https://support.microsoft.com/en-us/office/create-a-parameter-query-in-microsoft-query-c67d9af7-c8a0-4bf7-937c-087cb25f7ad3

or other sql tools.

Sql:

select * from Sheet1 inner join Sheet2 on Sheet1.ID=Sheet2.ID;

Answer 3

Hi,

Write a VLOOKUP() function is sheet2 to bring over the relevant columns from sheet1

Answer 4

Here's an example very dummied down. See Sheet 3 for my notes.

EXCEL REPORT SAMPLE.xlsx

Answer 5

Could you share us some test file and show us expected result? Then I will have a try.

It could be done with Power Query or Macro.

For sharing a sample workbook, upload to OneDrive or any other cloud drives. Then post a link here.

Why a sample file is important for troubleshooting. How to do it. - Microsoft Community