I have two worksheets in two different Excel files. They both contain a list of names, id numbers, and associated data. One is a master list that includes general demographic fields, and the other is a list that only includes name and id, and an address. This list was pared down from the master list by another office.
I want to use the 2nd list to filter the first. Additionally, I want the results to include other fields from the master worksheet alongside the address fields from the second worksheet. I know how I could do this very easily with a database inner join, but I'm less clear on how to do this efficiently in Excel. How can join two worksheets in Excel? Bonus points for showing how to do outer joins as well, and I would greatly prefer knowing how to do this without needing a macro.
=INDEX ( Column_I_want_a_return_value_from , ( MATCH ( My_Lookup_Value , Column_I_want_to_Lookup_against , 0 ))
import pandas as pd; file1 = pd.read_csv("in_1.csv", sep=","); # alternatively read_excel() file2 = pd.read_csv("in_2.csv", sep=";"); merged = file1.merge(file2, left_on='ID', right_on='OTHER_ID', how='outer'); merged.to_excel("out.xlsx");