import pandas as pd
import tkinter as tk
from tkinter import filedialog, messagebox

def select_file():
    file_path = filedialog.askopenfilename(filetypes=[("Excel files", "*.xls *.xlsx")])
    if file_path:
        return file_path
    else:
        messagebox.showwarning("No file selected", "Please select a file.")
        return None

def load_columns(file_path, listbox):
    df = pd.read_excel(file_path, None)
    columns = df[list(df.keys())[0]].columns.tolist()
    listbox.delete(0, tk.END)
    for col in columns:
        listbox.insert(tk.END, col)
    return df

def on_select_main(event):
    global main_selected_column
    main_selected_column = main_listbox.get(main_listbox.curselection())
    messagebox.showinfo("Selected Column", f"You selected (Main File): {main_selected_column}")

def on_select_lookup(event):
    global lookup_selected_column
    lookup_selected_column = lookup_listbox.get(lookup_listbox.curselection())
    messagebox.showinfo("Selected Column", f"You selected (Lookup File): {lookup_selected_column}")

def on_select_lookup_columns(event):
    global lookup_selected_columns
    selected_indices = lookup_listbox.curselection()
    lookup_selected_columns = [lookup_listbox.get(i) for i in selected_indices]
    messagebox.showinfo("Selected Columns", f"You selected (Lookup File Columns): {', '.join(lookup_selected_columns)}")

def vlookup_data(main_file_path, lookup_file_path, main_col, lookup_col, lookup_cols):
    main_xls = pd.ExcelFile(main_file_path)
    lookup_xls = pd.ExcelFile(lookup_file_path)
    
    main_sheets = main_xls.sheet_names
    lookup_sheets = lookup_xls.sheet_names
    
    writer = pd.ExcelWriter(main_file_path, engine='openpyxl')
    
    for sheet in main_sheets:
        main_df = pd.read_excel(main_xls, sheet_name=sheet)
        
        for lookup_sheet in lookup_sheets:
            lookup_df = pd.read_excel(lookup_xls, sheet_name=lookup_sheet)
            
            merged_df = pd.merge(main_df, lookup_df[[lookup_col] + lookup_cols], how='left', left_on=main_col, right_on=lookup_col)
            merged_df.drop(columns=[lookup_col], inplace=True)
            
            main_df = merged_df
        
        main_df.to_excel(writer, sheet_name=sheet, index=False)
    
    writer.save()
    messagebox.showinfo("Success", "VLOOKUP operation completed successfully.")
    print(f"File saved successfully: {main_file_path}")

def start_vlookup():
    global main_file_path, lookup_file_path, main_selected_column, lookup_selected_column, lookup_selected_columns
    if main_selected_column and lookup_selected_column and lookup_selected_columns:
        vlookup_data(main_file_path, lookup_file_path, main_selected_column, lookup_selected_column, lookup_selected_columns)
    else:
        messagebox.showwarning("Selection Missing", "Please select all necessary columns.")

app = tk.Tk()
app.title("VLOOKUP Across Multiple Sheets")
app.geometry("800x600")

main_file_path = None
lookup_file_path = None
main_selected_column = None
lookup_selected_column = None
lookup_selected_columns = []

frame = tk.Frame(app)
frame.pack(pady=20)

main_file_button = tk.Button(frame, text="Load Main Excel File", command=lambda: load_columns(select_file(), main_listbox))
main_file_button.pack(pady=5)

main_listbox = tk.Listbox(frame, selectmode=tk.SINGLE, width=50, height=10)
main_listbox.pack(pady=5)
main_listbox.bind("<<ListboxSelect>>", on_select_main)

lookup_file_button = tk.Button(frame, text="Load Lookup Excel File", command=lambda: load_columns(select_file(), lookup_listbox))
lookup_file_button.pack(pady=5)

lookup_listbox = tk.Listbox(frame, selectmode=tk.MULTIPLE, width=50, height=10)
lookup_listbox.pack(pady=5)
lookup_listbox.bind("<<ListboxSelect>>", on_select_lookup)

lookup_columns_button = tk.Button(frame, text="Select Lookup Columns", command=lambda: on_select_lookup_columns(None))
lookup_columns_button.pack(pady=5)

start_button = tk.Button(frame, text="Start VLOOKUP", command=start_vlookup)
start_button.pack(pady=20)

app.mainloop()


    

I tried this code to open main file and lookup file ,i want to select common column but condition is i might have different column name in both file,SO how to do left on and right on. The script should allow me to select a main file and a lookup file, select the common columns and the columns to be looked up, and perform the VLOOKUP operation across multiple sheets.