merge two spreadsheets with different columns but the same IDs

Hi. Here's my setup:

Two sheets in different workbooks, each with different columns (for example, one case has columns like case type, name, file number; the other has a column called note), but with rows representing the same client. Each row has an ID (present in both sheets), in case that helps.

The goal is to merge the two spreadsheets into a single spreadsheet, to be used as report. This needs to be done once a month, and the more efficiently and with the least amount of user input, the better. The users are not computer people.

I have seen tutorials that appear to relate to this sort of thing, and it looks like a Power Query is the way to go, but I haven't seen quite my exact use case, and would appreciate some guidance. My initial attempts to solve this myself have been unsuccessful.

Thanks

For bonus credit, each sheet has a column that I don't want in the final report (Column1, it is called), and the ID does not need to be printed in report. Also, the Case name appears in both reports and only needs to appear once in the report. Finally, it would be very helpful if the final report has an extra column that has one of two present values, depending on the first two letters of the File Number. For example, if the File Number is MC22-0423, the value might be Mighty Car, and if the File Number is IB33-8888, the value would be Ideal Bar.

Thanks!!

Please see the yellow banner at top of the page on how to attach a sample workbook.

Bring your two tables into Power Query and then join them (merge) on the common field

Please Login or Register  to view this content.