Data Migration: Map & Consolidate Relational Real Estate Spreadsheets into Multi-Tab Master Template

Project Title: Data Migration Expert: Map and Consolidate Relational Real Estate Spreadsheets into Multi-Tab Master Template (7-Day Deadline) Project Description: I am onboarding onto a property management software platform (Re-Leased) and have a critical data migration bottleneck. I need a clean, 100% accurate, multi-tab Master Excel spreadsheet populated by extracting data from a large volume of messy, mismatched raw source spreadsheets. [1] Because this project involves highly sensitive client, tenant, and supplier information (names, emails, and bank details), we must use a strict 2-step delivery process to maintain 100% data security. You will never see my live data. YOUR EXACT STEP-BY-STEP WORKFLOW: • STEP 1 (Data Masking Script): I will show you the file structures. Before I send you any data, you must write a brief Python script or tool that I can run locally on my machine. This script must keep my exact column headers and file names completely intact but replace all row data with randomized, realistic dummy data (semantic masking). I will run this locally and send you the fake/masked files to work with. • STEP 2 (Building the Mapping Engine): You will use those masked files to build the main data migration engine. • STEP 3 (Final Delivery): You will send the final automation tool back to me as a local Python script or a Power Query Excel template. I will drop my real data into the folder on my own computer, hit run/refresh, and generate the final master sheet locally. DATA MAPPING LOGIC YOU MUST CODE: 1. Active Filter Rule: I will provide a 'Master Property List' and 'Master Client List'. Your script must use these as gatekeepers. Only migrate data from the raw source files if the property/client is explicitly listed as ACTIVE on these master lists. Drop all old, inactive records. 2. Relational Stacking: The target template contains multiple distinct relational tables stacked vertically or separated by tabs (Properties, Tenancies, Rent Reviews, System Lookups). Your tool must parse the source files and route them to the correct structural locations. [1] 3. Learn by Example & Conflict Resolution: I will provide a partially completed spreadsheet showing how I manually mapped a few examples. Use this as your blueprint. However, your script must use the latest raw source spreadsheets as the "source of truth" if data conflicts. For example, if a tenant name, rent amount, or lease date has changed in the raw files, use the new data over the sample file data. However, you can still pull non-conflicting static information from the sample sheet, such as the property use column (e.g., "what the property is used for"). [1] 4. Property Manager Overrides: For any column in the target master spreadsheet asking for the assigned Property Manager, your script must automatically hardcode it to only one option: Jonathan Brickman (or JRB depending on the template format). Do not pull property manager names from the raw files. 5. Intuitive Mapping: The column headers in the raw source files do not match the target master spreadsheet. You must map them intelligently (e.g., matching 'Postcode' to 'Postal Post/Zip Code'). 6. Partial Fill & Task List: If required fields in the master template cannot be found in the source data, DO NOT stop or crash. Populate everything you possibly can, leave the missing fields blank, and generate a clear 'Manual Action Task List' text file at the end showing me exactly which rows require human attention. COLLABORATION & SCOPE: • There are a significant number of source spreadsheets. As you audit the data, you may need to ask for additional context or supplementary files. I am looking for a collaborative partner who will proactively tell me what they need. • The final engine must be dynamic, meaning if I drop an extra source spreadsheet into the folder later using your formatting rules, the script should be able to read it without breaking. • For complex questions, I prefer that you record a quick 60-second screen share video (using Loom or Upwork's built-in recorder) pointing out the exact cells or rows causing the issue so we can communicate efficiently. TIMELINE & BUDGET: • Deadline: This project must be fully completed, tested, and delivered within 7 days. Please reply with the words 'REAL ESTATE' at the very top of your proposal so I know you have read and agreed to these data security, collaboration, and delivery terms.