Advance VLookup using Python

Python is amazing language, it is very easy to play with code using Python, In this article, we are going to learn, how could we make Advance VLookup as we use in MS Excel. By using this code, you can merge multiple excel sheets into single excel sheets using Python.

Steps to run this code:

1. Create master or main excel file format as given below in image. As you see in this file, you may have any number of columns but key column is “Roll No”, on the base of “Roll No” this code will work, so make one column as “Roll No” or you may take any column name as key column but we need to change that column in Python code also so in place of “Roll No” you can change any column as you want, otherwise keep same if you have less knowledge of python code.

Main Excel file Format

2. Now, create a folder with name of “excelfile”, like show in image below:

Folder Name

3. Now, copy all excel file that you want to merge into this folder called “excelfile”.

excelfile folder files

4. Now, change format of your all excel file as given in image below, keep one column “Roll No” and rest of other is based on you like Q3 or you may have more also.

All excelfile Folder File Format

5. Now, go to the folder in which you have python code and run python program name “DVKMergeExcel.py” as shown in image below:

Python File to Run

6. Now, enjoy and have main file containing all excel file data into single one on the bases of “Roll No”.

Python Code for DVKMergeExcel.py:

import os

import pandas as pd

data_location = “excelfile/”

if os.path.exists(data_location) and os.path.exists(“mainfile.xlsx”):

for file in os.listdir(data_location):

print(“…Merging file “+file)

df= pd.read_excel(data_location + file)

mainfile = pd.read_excel(“mainfile.xlsx”);

all_data = pd.merge(mainfile,df,on=”Roll No”,how=”outer”,sort=True);

all_data.to_excel(“mainfile.xlsx”, index = False)

print(“Merging completed successfully”)

else:

print(“\n”)

print(“=====================For Your Help=============================”)

print(“Create ‘excelfile’ folder and paste your all excel file in it”)

print(“Paste mainfile.xlsx in current directory”)

print(“===============================================================”)

print(“\n”)

print(“====================Programmer Information======================”)

print(“Created By: Dr. Vipin Kumar”)

print(“Associate Professor, DCA”)

print(“& Addl. Head (Skill Development), SDFS”)

print(“KIET Groups of Institutions, Delhi-NCR, INDIA”)

print(“===============================================================”)

If you want to download this code, download from GitHub link below:

https://github.com/DrVipinKumar/Advance-VLookup-Python.git

If you want to watch working of this code, watch at YouTube Channel link below:

https://www.youtube.com/watch?v=ZxLWYm-Apwk