Cleaning the IP Register Before the Defaulter Circular Goes Out
An ESIC branch office maintains a workbook, IP_Register.xlsx, covering 1,240 insured persons. The columns are A Sl. No., B IP Number (twelve digits), C Name, D Date of Joining, E Monthly Wages, F Employee Contribution at 0.75%, G Employer Contribution at 3.25% and H Status. The file was built by pasting data from three sources. The branch notices these problems. Thirty-seven IP numbers appear twice, but the duplicate rows carry slightly different names, with trailing spaces and different capitalisation. Column D mixes 12/07/2023, 12-Jul-23, July 12, 2023 and a few 12.07.2023, so sorting by date gives nonsense. Column F has been typed in by hand; three cells display ##### and two display #VALUE!. A circular must go to 210 defaulting employers held on a second sheet, each with the employer name, street, city and PIN in separate columns. Three colleagues must be able to add new rows and correct wages, but must not be able to alter the formulas in F and G. The branch manager wants the cleanest fix that destroys no record.
1. How should the duplicates and the mixed dates be handled?
Read the worked answer
Do not use Remove Duplicates. It deletes rows silently, keeps whichever copy it meets first, and here the duplicates differ in spelling, so Excel will not even recognise them as duplicates - the 37 repeats would survive the tool and the genuinely identical ones would vanish without trace. Flag them instead, in a helper column, using a key built on TRIM and PROPER of the IP number and name, with COUNTIF or SUMPRODUCT counting occurrences of that key. Then sort or filter on the flag and merge each pair by hand after checking which row holds the correct wages and status. For the dates, a single DATEVALUE across the column will not work, because 12/07/2023 and 12.07.2023 may be read with different day-month orders under a different locale. The safe route is Data, then From Table/Range, into Power Query, set the column type to Date using the locale that matches the source (United Kingdom for dd/mm/yyyy), keep the original column alongside as a check, and load the cleaned result back to a new sheet so the raw paste is preserved.
2. What do ##### and #VALUE! mean, and how should columns F and G be built?
Read the worked answer
3. How should the sheet be protected for three colleagues, and how should the 210-letter circular be produced?
Read the worked answer
Layer the protection. First select the input ranges (A to E and H), open Format Cells, Protection, and clear Locked; leave F and G locked. Then Review, Protect Sheet, set a password, and allow only the options actually needed, such as selecting unlocked cells and inserting rows. That stops colleagues overwriting formulas but does not stop them reading, copying or, with the password, unlocking the sheet, so for confidentiality use File, Info, Protect Workbook, Encrypt with Password, and keep one master copy on the shared drive with version history. For the circular, put the 210 employers on a clean sheet with a single header row (Employer, Address1, Address2, City, PIN) and no blank rows inside the range; then Mailings, Start Mail Merge, Letters, choose that sheet as the recipient list, insert the merge fields, preview several records, and use Finish and Merge, Edit Individual Documents so all 210 letters can be checked before printing. A single stray blank row or a merged header cell will silently drop or misalign records.