Tom Gorham
Working out the number of artists in an iTunes library is no easy task, so it was Numbers that came to the rescue to solve this conundrum…
iWork’s component applications can turn their hand to all manner of tasks. They can help you write novels, organise your personal finances and take your presentations to another level. But until last week, one thing I wasn’t aware they could do was to solve a pub argument about iTunes.
The good-natured dispute concerned who had the biggest music collection. It swung from chest-beating about collections of old vinyl albums to the number of songs contained in respective iTunes libraries and from there to whose iTunes library contained the most eclectic list of artists.
You’d have thought that this argument would be easy to solve with a quick comparison of iTunes libraries. But it turns out that working out the number of artists in any library is tougher than you’d think. I thought that counting the number of folders in the ‘iTunes Music’ folder would provide a decent estimate, as they’re arranged by artist, but that only works if all media is correctly organised in the iTunes folder – and, in many situations, that’s not the case.
However, it turns out that there is a way to work out and display the number of unique artists – and it was iWork’s Numbers that came to the rescue. First, you need to export your library data from iTunes. To do this, select your music library in iTunes’ source list (you can also export by individual playlist) and then choose File > Library > Export playlists. This exports a plain text record of your library.
When you open this file in Numbers, you can see all the library metadata for each of your tracks, from title to size and time, neatly in a single table. Convert the top row, which contains the metadata headings, to a header row before you work with this data.
The first step is to discard a lot of the information you don’t need for this exercise. As you only really want the Artist field – in my table, this was in column B of the table, you can delete most the rest of the columns to its right.
You now have a comprehensive list of every artist in your iTunes database. You could apply a formula in Numbers to count the number of entries in the Artist column, but the problem is that the Artist column contains a huge number of duplicates, as artists are listed next to each track. What you want to do is to count each artist only once to get an accurate figure of the number of individual artists.
To do this, create a secondary column. In this case, I created this in row D of the Numbers table. In cell D2 of this column, enter the following formula:
=IF(COUNTIF($B2:$B$2,B2)=1,B2,”").
In this formula, ‘B2′ is a reference to first cell of the column containing the artist names in my table, so you’ll need to change this if your columns are arranged differently. Copy and paste this formula into cell D3 and drag down column D to fill the formula into all its cells for as many rows as there is a corresponding entry in the Artists column.
I should explain what the formula does, in passing explaining that the dollar symbols in the formula indicate absolute values, which don’t change as the formula is pasted into new cells. The first $B2 indicates an absolute column, while the $B$2 indicates an absolute row and an absolute column. As you paste these values into cells in column D, those values don’t change. However, the formula’s ‘B2′ value, without dollar signs, is a relative reference, so means as the cell is copied down column D, it’s value will change relatively.
The formula counts the number of occurrences of each artist against the entries above it in the Artists column. If the total number of occurrences is 1 – that is, the artist hasn’t been listed before, then that artist name is added to the respective cell in column D. Otherwise, it’s left blank.
When you’ve completed this, what you’ll have is a list of all unique artists in column D, with all duplicates removed.
What I wanted to do was make this more useful by both counting the number of unique artists in the database and the number of tracks for which each artist is responsible.
To count how many tracks for which each artist is responsible, I added another column, E. In cell E2, I entered the following formula:
=COUNTIF(B:B,D2)
Like the previous formula, this checks the unique artist listed in column D and counts the number of times that name appears in the list of artists in column B. Copy this formula down column E to apply it to all the artists in column D.
However, here’s the first problem: while this accurately counts the number of times an artist appears, it also calculates the number of occurrences of blank cells in the column D. The simplest way to get around this is to wrap the above formula inside another formula that first tests whether the cell in column D is blank and, if so, makes the respective cell in column E blank, too.
=IF(D2=”",”",COUNTIF(B:B,D2))
Only if the content of the cell in column D is not blank will Numbers go ahead and count the number of times that artist appears.
As for counting the total number of artists, I tried to do this using Numbers’ ‘CountA’ formula, which differs from the normal ‘Count’ formula, as instead of tallying all instances of numbers in a column, it also counts text instances. I ran into a problem, though: while some cells in column D appear blank, they contain formulae, and the ‘CountA’ formula includes those, too, which it ends up counting every cell, whether it contains an artist or not.
The workaround was simply to add the instances of numbers in column E using the ‘Count’ function. So, in a separate cell, I added the simple calculation: =COUNT(E:E), and the result matched the number of unique artists in the iTunes library.
One other task remaining to be done was to tidy up the list to remove unsightly empty rows. There are various complicated ways to do this using formulae, but Numbers makes it easy with its filtering feature, which removes rows from view according to criteria that you set. Click the Reorganize button in the toolbar and, in the resulting window, under the ‘Show rows that match the following’, select column D and choose ‘Is not blank’ from the Criteria drop-down menu. The table will automatically sort to reveal only those rows that have an artist listed in column D.
If I’m honest, by the time I’d worked out how to do this, not only had the argument run its course, but the participants had forgotten they’d had it. I’d like to think the research wasn’t in vain, though: the technique to isolate unique entries in any list is a useful technique that can be applied to all sorts of situations. For example, if you’re a teacher who wants to keep a record of individual responses to a question, you could run a variation of this formula to quickly extract the results.
