6.3 Duplicate Record Removal & Text-to-Columns Cleansing

6.3 Duplicate Record Removal & Text-to-Columns Cleansing

Data imported from enterprise resource planning (ERP) platforms, customer relationship management (CRM) systems, external web services, and legacy text files rarely arrives ready for immediate analysis. It frequently contains duplicated rows, concatenated strings, inconsistent delimiters, and numeric codes that risk corruption if parsed incorrectly. On the MO-211 exam, candidates must demonstrate complete command of two core data cleansing workflows: executing permanent deduplication via Remove Duplicates (and contrasting it with the dynamic =UNIQUE() function) and transforming delimited or fixed-width text into structured worksheet columns using the Text to Columns wizard.

---

Data > Remove Duplicates Mechanics

The Remove Duplicates feature provides an automated mechanism to identify and purge redundant rows from a dataset in place.

$\text{Data} \longrightarrow \text{Data Tools} \longrightarrow \text{Remove Duplicates}$

Configuring Deduplication Parameters

When the dialog opens, Excel presents the following configuration options:

+-------------------------------------------------------------+
|                      Remove Duplicates                      |
+-------------------------------------------------------------+
| [x] My data has headers                                     |
|                                                             |
| Columns:                     [ Select All ]  [ Unselect All]|
|   [x] Customer ID                                           |
|   [ ] Transaction Date                                      |
|   [x] Store Code                                            |
|   [ ] Sale Amount                                           |
|                                                             |
|                                        [  OK  ]  [ Cancel ] |
+-------------------------------------------------------------+

1. My data has headers: Always verify this checkbox. If checked, Excel excludes row 1 from deduplication evaluation and displays actual column names. If unchecked, the header row is treated as data and may be permanently deleted.
2. Column Selection Checkboxes:
   • Full Record Evaluation: Leaving all columns checked deletes rows only when every cell across the entire row matches another record identically.
   • Composite Primary Keys: Checking a subset of columns instructs Excel to evaluate uniqueness based strictly on those fields. For instance, selecting both Customer ID and Store Code while leaving Transaction Date unchecked purges any subsequent visit by the same customer to the same store, regardless of the date.
3. Retention Rule: When duplicates are detected, Excel retains the first instance encountered in top-to-bottom row order and permanently deletes all subsequent matching rows, shifting remaining rows upward.

Case-Insensitivity & Cleanliness Rules

• Case-Insensitive Matching: Excel's Remove Duplicates tool does not distinguish between uppercase and lowercase letters. The values "SEATTLE", "Seattle", and "seattle" are evaluated as exact duplicates.
• Whitespace Sensitivity: Trailing spaces, leading spaces, or invisible non-breaking space characters (CHAR(160)) prevent duplicate detection. The string "Chicago " (with a trailing space) is not recognized as a duplicate of "Chicago". Analysts should clean data with =TRIM() and =CLEAN() prior to deduplication.

---

Destructive Deduplication vs. Dynamic =UNIQUE()

On modern Microsoft 365 environments, candidates must recognize the operational distinctions between the native dialog tool and the dynamic array formula =UNIQUE().

The =UNIQUE() Function Syntax

=UNIQUE(array, [by_col], [exactly_once])

• array: The input range or table to evaluate.
• [by_col]: Optional logical value. FALSE (or omitted) compares row by row; TRUE compares column by column.
• [exactly_once]: Optional logical value. FALSE (or omitted) returns all distinct unique items; TRUE returns only items that occur exactly once in the source list.

---

The Text to Columns Cleansing Wizard

When data is exported from external databases as comma-separated values (CSV) or fixed-width mainframe logs, multiple fields often arrive concatenated within a single column. The Text to Columns wizard splits this text into discrete cells.

To launch the wizard, select the single column of text and navigate to Data > Data Tools > Text to Columns (or press Alt+A+E).

Step 1: Selecting the Parse Mode

• Delimited: Characters such as commas, tabs, semicolons, or pipes separate each field.
• Fixed Width: Fields are aligned in rigid vertical columns with spaces padding the gaps between fields.

---

Step 2: Configuring Delimiters or Break Lines

If Delimited is Selected:

1. Choose delimiter checkboxes: Tab, Semicolon, Comma, Space, or Other (enter custom characters such as a pipe | or hyphen -).
2. Treat consecutive delimiters as one: Essential when files use multiple spaces between words or data columns. Leaving this unchecked causes Excel to create empty blank columns for each extra space.
3. Text qualifier: Dropdown offering ", ', or {none}. When text contains internal delimiters enclosed in quotes (e.g., "New York, NY"), setting the qualifier to " ensures Excel treats the comma inside quotes as literal text rather than a column break.

If Fixed Width is Selected:

Excel provides an interactive graphical ruler window:

• Create a break line: Click at the desired character position.
• Move a break line: Click and drag the vertical line to a new position.
• Delete a break line: Double-click on the vertical line.

---

Step 3: Column Data Formats & Destination Range

Step 3 is the most critical stage for preventing data corruption on the MO-211 exam:

+-------------------------------------------------------------+
|               Convert Text to Columns Wizard                |
+-------------------------------------------------------------+
| Column data format:                                         |
|   ( ) General                                               |
|   (o) Text                                                  |
|   ( ) Date:  [MDY         v]                                |
|   ( ) Do not import column (skip)                           |
|                                                             |
| Destination:  [ $B$2                                      ] |
|                                                             |
| [Back]                                [ Finish ] [ Cancel ] |
+-------------------------------------------------------------+

1. Column Data Format Options:
   • General: Default format. Converts numeric strings to numbers, date strings to dates, and all remaining values to text.
   • Text: Essential for numeric identifiers containing leading zeros (such as ZIP codes 01420, Social Security numbers, or part codes 00912). If left as General, Excel truncates the leading zeros (converting "01420" to 1420). Setting the format to Text preserves the exact string.
   • Date: Specifies the exact calendar sequence of raw text using the dropdown (e.g., MDY, DMY, YMD). This forces Excel to parse international date formats correctly without relying on system locale defaults.
   • Do not import column (skip): Instructs Excel to omit the selected column entirely during the import process, dropping unwanted fields.
2. Destination Range:
   • By default, Excel populates the Destination field with the first cell of the source range (e.g., $A$2), which overwrites your raw source data.
   • To preserve original raw strings for audit trails, change the Destination reference to an adjacent empty column (e.g., $B$2).
3. Click Finish to execute the separation.