cleaning data

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. Cells › Group Similar Values… 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.

The Group Similar Values window on a column of cities, comparing by Same words: four ticked groups, Madrid with madrid and MADRID, San Sebastián with San Sebastian and san sebastián, Barcelona with barcelona, and Las Palmas with las palmas, each with the value it will become; “6 cells will change” beside Merge Selected

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.5 and 15 are different numbers, however alike they look.
  • Email addresses, only when they are the same address with other capitals (JOHN.SMITH@EXAMPLE.COM and john.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@ and smith.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.