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.