Compare rows in Excel file and mark duplicates.
7-step algorithm is provided.
## Deliverables
Your solution should be written on .NET
I have to have all source codes as well as a fully working compiled release
It must work with .XLS format. If you want to make it work with .XLSX format it is optional.
My Date format and currency format in Excel must be preserved
I have an Excel file with many spreadsheets (tabs)
Each record in every row (or line) in every tab consists of 3 first cells (fields): Date, Description, Amount. Other cells on the right after the Amount may be comments and should be ignored.
Two Tabs contain master records (rows, or lines). Each of the rows from those two tabs supposed to have one and only one copy in one Tab other than one of the master tab.
Problem: some rows from the master tabs were either copied into more than one non-master tab or not copied to any of them. All of those they need to be identified.
Solution (this is an algorithm ready for you to code based in it):
1. Allow user to identify one or more master tabs.
2. Look up first record of the first master tab. A record is 3 left cells in this order : Date, Description, Amount. Ignore formatting and styles, like background color.
3. Search in every row of every non-master tab for exact match of this record (all 3 left cells should match) until the first match.
4. If an exact match is found, mark entire row of the non-master tab with green background.
5. Continue to search in ALL remaining records of of this and all other non-master tabs. If another match is found, mark entire row for all of those matches with red background and change the very first match as described in Step 4 above to red background too.
6. If there is one and only one match found - mark entire row of master tab with green background. If more than one exact match found - mark the row in master tab with red background. If no matches found anywhere at all - mark the row in master tab with yellow background.
7. Repeat steps above for all other records in all master tabs.
Bonus functionality.
It's not mandatory but it's nice to have. Please quote with and without it:
1. Add a row with a TOTAL cell under all amounts on each tab, including master tabs and non-master tab.
2. Add summary spreadsheet (tab) as shown in example.
Thank you everyone for your bids.