Standardized Data Formats Eliminate Errors in Visa Comparison Process

A Case on How Standardized Data Formats Eliminated Errors in Visa Comparisons for 90,000 Entries.

The Client

The client is a leading provider of workforce outsourcing and recruitment services in Saudi Arabia. Specializing in retail, healthcare, and hospitality sectors, they deliver tailored staffing solutions to over 800 corporate clients. They maintain a commitment to excellence with ISO 9001:2015 certification, ensuring standardized recruitment processes, well-trained staff, and consistent service quality.

Challenges Faced by the Client

Visa comparison is a legal obligation in workforce deployment, especially when managing a large volume of foreign hires. This involves recruitment agencies cross-referencing their internal visa data with the government’s portal to reconcile issued, utilized, and canceled visas correctly. A single mismatch could lead to misallocated work permits, legal penalties, and administrative delays, highlighting the need for 100% accuracy.

However, managing this process efficiently at scale proved challenging for our client. They encountered inefficiencies in comparing visa balances between their internal system and the national labor platform, leading to these sub-challenges:

1. Excel Performance Bottlenecks: Handling up to 90,000 records slowed Excel down, making it unresponsive, increasing the right of incorrect visa status updates and non-compliance.
2. Complex Formula Maintenance: Existing formulas for comparing visa data were difficult to maintain and highly time-consuming. For example, verifying 850 visa numbers consumed nearly 850 minutes (14 hours) just for entry and validation.
3. Inconsistent Data Formatting: Inconsistencies such as extra spaces or mismatched spellings in job titles and nationality fields resulted in frequent rework.
4. Slow Data Extraction: Retrieving input files from the internal system often took 6+ minutes due to outdated query logic and portal delays.
5. Language Mismatches: Some Occupation and Nationality names were listed in a different language. Although the terms had the same meaning, the mismatch led to comparison errors.

Solutions

To overcome these challenges, we implemented a series of automation-driven optimizations:

• We replaced volatile formulas with optimized functions and implemented data validation rules to highlight discrepancies in large datasets.
• Using TRIM and SUBSTITUTE functions, we eliminated spacing and formatting errors. We also automated data comparisons using macros to highlight mismatches accurately.
• Instead of one-by-one comparisons, we used automation to process 100+ debit numbers per minute using VBA logic in the Comments column.
• We added a lookup file that matched Occupation and Nationality terms written in a different language but carrying the same meaning. This module was added to catch duplicate records, incomplete fields, and mismatches before final submission.

Solutions Benefits

• Visa comparisons that previously took hours are now completed in just 20–25 minutes, even with 95,000+ records.
• By automating data validation and formatting standardization, inconsistencies in occupation names and nationality fields were resolved, ensuring accuracy in visa processing.
• Visa record extraction time dropped from 6 minutes to 60 seconds per file with optimized SQL queries.
• 100+ debit numbers compared per minute, eliminating the 1-minute manual effort per entry.
• Automated data reconciliation between the internal and government portals eliminates discrepancies, ensuring real-time, accurate visa balance tracking.
• Power Automate now provides real-time data visibility, allowing HR teams to make informed decisions with accurate, up-to-date visa status information.
• Language-related discrepancies were resolved using a mapping file, ensuring multilingual comparisons no longer caused mismatches.
• As a result, the client saved 4 full-time employee efforts and achieved $250K in annual cost savings through automation.

Conclusion

About Us

UB Technology Innovations, Inc. (UBTI) is a leading global technology solution provider with over 3 decades of experience across all industries, specializing in Capital Markets, Logistics, and Healthcare. We are the preferred Microsoft Solutions Partner backed by a world-class team of Microsoft Certified experts with rich experience in Azure Cloud Platform and Data Analytics.

One of the Top 10 winners of the
Microsoft’s Azure AI & ML Workathon