VBA and R script to compare data
- or -
Post a project like this£250(approx. $312)
- Posted:
- Proposals: 6
- Remote
- #2429498
- OPPORTUNITY
- Expired
Experienced Graphics designer |Experienced WordPress Full Stack|SharePoint Expert|Data Entry Team |flyer/business card/Brochure||2DAnimation||Photoshop work|
Islamabad
1901142359825247906013650062522480315561
Description
Experience Level: Expert
Estimated project duration: 1 day or less
I would need help in writing a VBA and R script to compare data in 2 Excel files.
1. Input data - 2 Excel files (Pivot tables converted to normal tables)
2. Each file will have 3 sheets named the same e.g. Sheet1, Sheet2, Sheet3
3. Each Sheet will hold X number of columns with different data attributes and values
4. Each Sheet will have same attributes that should (could) for a KEY for matching records between 2 files e.g. CONCATONATED ColumnName1-ColumnName2-ColumnName3 etc.
5. Records should be matched between 2 Excel Files/ Each Sheet using this Key and Values compared for each Key / row of data
6. Results should be exported to a new Excel file using the previously created Key e.g. Key1, Value1(from File1), Value1(from File2), MatchStatus, Difference
7. Script should handle comparing 0 to 0 and NULLs (blank Cells)
8. Any missing records between files should be exported along with comparison results (similarly to a FULL OUTER JOIN in SQL)
9. Edge case – attribute data that created the Key is presented in 1-to-many relationship e.g. Value1 in ColumnName1 will be repeated only once until all combinations with values in ColumnName2+ ColumnName3+ ColumnName4 are exhausted then a new Value2 in ColumnName1 is present. I think in order for each row of data to have a unique Key the blank Cells should be populated with the previous top row (however I’m not trying to force a solution around this).
10. Script should be written in such a way that I will be able to modify the input Sheet names and Column Names
Example file attached
1. Input data - 2 Excel files (Pivot tables converted to normal tables)
2. Each file will have 3 sheets named the same e.g. Sheet1, Sheet2, Sheet3
3. Each Sheet will hold X number of columns with different data attributes and values
4. Each Sheet will have same attributes that should (could) for a KEY for matching records between 2 files e.g. CONCATONATED ColumnName1-ColumnName2-ColumnName3 etc.
5. Records should be matched between 2 Excel Files/ Each Sheet using this Key and Values compared for each Key / row of data
6. Results should be exported to a new Excel file using the previously created Key e.g. Key1, Value1(from File1), Value1(from File2), MatchStatus, Difference
7. Script should handle comparing 0 to 0 and NULLs (blank Cells)
8. Any missing records between files should be exported along with comparison results (similarly to a FULL OUTER JOIN in SQL)
9. Edge case – attribute data that created the Key is presented in 1-to-many relationship e.g. Value1 in ColumnName1 will be repeated only once until all combinations with values in ColumnName2+ ColumnName3+ ColumnName4 are exhausted then a new Value2 in ColumnName1 is present. I think in order for each row of data to have a unique Key the blank Cells should be populated with the previous top row (however I’m not trying to force a solution around this).
10. Script should be written in such a way that I will be able to modify the input Sheet names and Column Names
Example file attached
Michal F.
97% (6)Projects Completed
6
Freelancers worked with
6
Projects awarded
33%
Last project
27 Jul 2020
United Kingdom
New Proposal
Login to your account and send a proposal now to get this project.
Log inClarification Board Ask a Question
-
Hi Michal, have you already found someone for this job or are you still opened for accepting new proposals?
818052
We collect cookies to enable the proper functioning and security of our website, and to enhance your experience. By clicking on 'Accept All Cookies', you consent to the use of these cookies. You can change your 'Cookies Settings' at any time. For more information, please read ourCookie Policy
Cookie Settings
Accept All Cookies