Merge, Identify Duplicates, report duplicates with all details
Budget: ₹1,500 – ₹12,500 INR
I have uploaded a Google Drive folder here:
https://drive.google.com/drive/folders/1CzxYcMTKFk7ykIyr0_AX8NHIpqEuzcaK
Inside each sub-folder you will see one Excel file that contains a voter list for a single part number. The folders themselves are already arranged by part number, so locating the correct source files is straightforward. Voter list is the name of the file in every folder. Close to 10,000 excel files are there in the drive
What I need you to do
• Open only the voter list Excel in every part-number folder and merge the data into one master Excel workbook.
• In that master file perform an exact-match duplicate check using this key: Name + Relative Name + Age + Gender + House Number.
• For every duplicate row create two full records – one tagged “Source”, the other “Copy”. Each record must include:
– Serial No.
– Part No.
– Assembly No. & Assembly Name
– Name, Relative Name, Age, Gender, Door (House) No. (One version required without door number being part of this list)
– EPIC ID of both source and copy
• Once the master workbook is complete, split the data back out into separate Excel files, each file representing its original Part-Assembly combination, and place them in the same folder structure that currently exists.
Also count and report how many flags are available in each variety, the flags are marked in a separate column.
Acceptance criteria
1. A single consolidated Excel workbook containing every voter record with duplicates clearly paired and fully detailed.
2. Part-wise / assembly-wise Excel files regenerated from that master and stored in their respective folders.
3. No data loss: row counts in the split files must equal the counts in the original source plus any duplicate copy rows you have added.
You are free to use Python (pandas), Power Query, VBA, or any equivalent tool, so long as every step above is achieved and the final output remains in standard Excel format (.xlsx).
Very urgent project to be completed within 4 hours. Code is not required only the output required. Totally 65 lac records are roughly available.
This is rough sample output
https://docs.google.com/spreadsheets/d/17A-_ec5C_Ms3PdCF_Q9Pvlv3Br8MFPHP/edit?usp=drivesdk&ouid=112365170969190837597&rtpof=true&sd=true
In this assembly name is to be added and door number to be added into the matching criteria
https://drive.google.com/drive/folders/1CzxYcMTKFk7ykIyr0_AX8NHIpqEuzcaK
Inside each sub-folder you will see one Excel file that contains a voter list for a single part number. The folders themselves are already arranged by part number, so locating the correct source files is straightforward. Voter list is the name of the file in every folder. Close to 10,000 excel files are there in the drive
What I need you to do
• Open only the voter list Excel in every part-number folder and merge the data into one master Excel workbook.
• In that master file perform an exact-match duplicate check using this key: Name + Relative Name + Age + Gender + House Number.
• For every duplicate row create two full records – one tagged “Source”, the other “Copy”. Each record must include:
– Serial No.
– Part No.
– Assembly No. & Assembly Name
– Name, Relative Name, Age, Gender, Door (House) No. (One version required without door number being part of this list)
– EPIC ID of both source and copy
• Once the master workbook is complete, split the data back out into separate Excel files, each file representing its original Part-Assembly combination, and place them in the same folder structure that currently exists.
Also count and report how many flags are available in each variety, the flags are marked in a separate column.
Acceptance criteria
1. A single consolidated Excel workbook containing every voter record with duplicates clearly paired and fully detailed.
2. Part-wise / assembly-wise Excel files regenerated from that master and stored in their respective folders.
3. No data loss: row counts in the split files must equal the counts in the original source plus any duplicate copy rows you have added.
You are free to use Python (pandas), Power Query, VBA, or any equivalent tool, so long as every step above is achieved and the final output remains in standard Excel format (.xlsx).
Very urgent project to be completed within 4 hours. Code is not required only the output required. Totally 65 lac records are roughly available.
This is rough sample output
https://docs.google.com/spreadsheets/d/17A-_ec5C_Ms3PdCF_Q9Pvlv3Br8MFPHP/edit?usp=drivesdk&ouid=112365170969190837597&rtpof=true&sd=true
In this assembly name is to be added and door number to be added into the matching criteria
Related categories:
Python
Data Processing
Data Entry
Excel
Data Mining
Excel VBA
Data Scraping
Data Visualization
Data Analysis
Data Management