Grouping similar values
Find the values of a column that are the same one written in different ways (capitals, accents, spaces, typos) and merge each group into one value.
The same city typed by ten people comes out as Madrid, madrid, MADRID. and Madird. looks through the cursor’s column for values like these, shows them in groups, and merges each group you choose into a single value. The Column profile opens it too, with Group similar… above its list of values.
Each group shows its values with how many cells have each, and below them the value they will all become: at first the most common one. To use another, click it; to write a new one, type it in the box. Tick the groups to merge and press Merge Selected: the cells change, in one step of Ctrl+Z⌘Z, and the window stays open with the groups that are left. The line beside the button says how many cells will change before you press it.

Four ways of comparing
The list at the top chooses how alike two values have to be. Start with the first and go down: each one finds what the one before leaves.
| Way | Two values are the same when… | Example |
|---|---|---|
| Same words | they have the same words, whatever their capitals, accents, punctuation, spaces and order | Gómez, Ana and ana gomez; U.S.A. and USA |
| Same letters | they have the same letters and digits in a row, whatever is between them | New York, NewYork and new-york |
| Similar spelling | a letter is changed, missing, added, or swapped with the next | Madrid and Madird; Barcelona and Barcelone |
| Same sound | their words sound the same in English, in any order | Catherine and Kathryn; Meyer and Maier; Steven and Stephen |
Same words only finds one value written in different ways, so its groups come ticked. The other three can also bring together values that are really different, so theirs come unticked: look at each group and tick the ones that are right.
Similar spelling allows one letter of difference for every five letters of the shorter value, up to the limit you choose beside it (1, 2 or 3 letters). So Chile and Chili are grouped, and Iran and Iraq, with four letters, never are. A difference in the digits is never a typo: Room 12 and Room 13 stay apart.
Same sound reads each word the way it would be said in English, with the Double Metaphone method: ph sounds like f, th in Kathryn like the one in Catherine, and the vowels after the first letter don’t count. It is made for names, and works for English ones and reasonably well for German and other European ones; on other text it finds little. It is the loosest way: different names can sound alike (Mary and Moore, Ann and Ian), so check each group before ticking it. Only words of two or more letters are compared by their sound: digits, initials, codes such as A12 and words in other scripts have to be the same.
What is compared
- Only text. Numbers, dates, times, empty cells and missing values (
NA,null…) are never grouped:1.5and15are different numbers, however alike they look. - Email addresses, only when they are the same address with other capitals (
JOHN.SMITH@EXAMPLE.COMandjohn.smith@example.com), whichever way of comparing you choose. Mail doesn’t tell capitals apart, but a dot, an accent, another order or a letter changed makes another address:john.smith@andsmith.john@are two people. These groups always come ticked, and the value they become is the address in lower case if one of them is written that way. - The values of the whole column, below the header, whatever is selected.
- Values exactly as they are written: a value with a space at the end is a different value, shown with a dot at its edge, and usually lands in the group of the one without it.
A value keeps its quotes if it had them, and the new value gets them if it needs them.
Large columns
Same words, Same letters and Same sound work with any number of values. Similar spelling compares the values with each other, so it takes a moment with thousands of them, and with more than 30,000 different values, or values that are nearly all alike, it says there are too many. Merge what the first two ways find first: there will be fewer left to compare.
To correct a single value everywhere, without looking for others like it, a double click on it in the Column profile is quicker. And values that are the same thing without looking alike (USA and United States) are merged there by hand: see Merging values by hand.