Filtering Odd and Even Numbers to Clean Data in Excel

As you’ll have undoubtedly picked up from all the frenzied media coverage of BrightonSEO, there’s always great excitement when a new tool comes along that helps us with a particular aspect of search engine optimisation. Deep down, however, the real journey to the centre of the Earth turns up not a hollowed out world wherein dinosaurs roam, but rather lots and lots and lots of data analysis. This being the case, the one tool I couldn’t survive without in my job is Excel. Actually, the coffee maker, too, but that’s another story for another time, perhaps.

As anyone in Vertical Leap Towers would tell you, I get quite animated when I work out how to do automate something in Excel. Or at least, how to do something in an easier fashion than before. And this it was this morning. I had a long list of data that I’d essentially copy and pasted from a website. We’re often presented with dirty data such as this, and finding short cuts to cleaning it up is always, always a highlight. This particular data set consisted of alternating hyperlinks (which I did want) and descriptions (which I didn’t). I couldn’t just use the standard filtering function, as there was no one term that covered everything, and I didn’t fancy going through 1200 rows deleting every other one.

What to do, then?

1. Insert a new column to the left of the data.

2. Index the data; that is, number it from 1 to N, where N is the total number of data points (type in ‘1’ in cell A1, hold down control and drag the series to the bottom).

3. You now have a list of data with even and odd numbering. However, Excel won’t let you filter by odd or even, so you need to be able to identify ‘even’ and ‘odd’ by an indicator that it recognises.

4. Insert another new column to the left of the data (and to the right of the numbered series).

5. We’re going to use the =mod(X,Y) function, which returns the remainder of X/Y. That is, if X=5 and Y=9, the remainder would be 4. So, we’re going to identify the numerator as the number in the cell, and have the denominator be 2. Therefore, even numbers will return 0, and odd ones will return 1. The formula will look like this (assuming the list of numbers is in column A and starts at A2):

=mod(A2,2).

Put this in cell B2, copy down the column. You’ll be left with a series of alternating 1s and 0s.

5. Use the filter to select which set of data you’d like to delete – that corresponding to even numbers, or odd.

6. Delete!

And so you have it, a clean set of data.