I have three different excel sheets containing similar data that I need to compare to find the differences. I believe a VB script would make the most sense but am open to other solutions to be the most efficient. The sheets can be in individual excel files or as different tabs in the same file whatever makes the most sense.
Collection List – 9-30-2013 – This sheet is the authoritative data set.
cci export – this needs to be compared to the above sheet as it changes frequently.
Cci account import – This was an initial list of accounts that was provided to build what became the data in the cci export sheet.
The unique cells to compare are
- “Client Reference Number”
- “Agency”
Meaning you will need to look compare the two sheets as follows:
- Ensure that should the “Agency” field contain the string “CCI” and it should exist in the “cci export” sheet.
- Should show anything that the “Export” field is set to Y and is not in the CCI export sheet as an exception.
- Should flag in a new column with the intention of showing what was in the “CCI account import” sheet and that didn’t make it to the cci export data.
Any exceptions I would like to be flagged or highlighted in some fashion that I can sort and then correct in the “Collection List” sheet. Example a new column can be added called “Exception” with a Y for example. Additionally I will need to have a way to exclude rows, logically a new column added called “Exclude” that would be ignored for future runs of the macro or script. Likewise the exception column would need to be cleared at the beginning of each run so I can correct discrepancies.
I have provided a small subset of the data and can provide the actual data to the winning bidder. The data size is as follows:
Collection List – 9-30-2013 – 1971 rows
Cci export = 390 rows.
Cci import = 1618 rows
Though the sample I have provided obviously is much shorter.
The data evolves with the cci export list growing frequently. My goal is to have some way to make this semi-automatic to see changes to the cci export list as they happen. Meaning I will run this macro or whatever it is roughly every couple weeks to flag the exceptions.
Hi; your 5-star excel and vba coding expert is ready to help you... kindly check your PM and get back to me if you wish to proceed with a top-rated and reliable partner here....regards,
Hi Palin. Being highly proficient in VB and Excel, I can write a new VB macro for you that does the work. Lets begin and get started on it. I have solved many similar sheets before and I can do one for you too.
Regards
Good day, I am expert. I teach excel. I can say there is no formula in excel that I cannot handle it. I've got lots of experiences in excel automating and conditional formatting.
Here are the links for my past experiences:
https://www.freelancer.com/projects/Excel/Drawing-EXCEL.html
https://www.freelancer.com/projects/4848189.html
https://www.freelancer.com/projects/Visual-Basic-Excel/Excel-user-form.html
https://www.freelancer.com/projects/Excel/Excel-Spreadsheet-Timecard-for-work.html
https://www.freelancer.com/projects/Data-Entry-Excel/Excel-Line-Restructured.html
Regards.
I will give u simple application which will tkae the excel sheets as input and give u output as u described in project solution. Please see sample applications in your PMB
I am very proficient in dealing with word, ppt, access, excel files, and good at VBA programming, I have exceed 6 years of VBA programming experience now. Please trust me, and let expert do it.
Hey dpalin, we have an experts team with plenty of years in Excel VBA.. Yes, you are right - VB script can be used to achieve the comparison.. please reply.. thanks
I have already worked for you and I can complete this within a day.
I will create a simple .net form application to compare the excel sheets.
From the understanding I got, we need to compare the collection list with the export list. Where should the output be captured, i mean the additional columns, in the collection list itself?
Please let me know and I can start right away