Unique album listing; need Excel?

GO TO ADMIN PANEL > ADD-ONS AND INSTALL VERTIFORO SIDEBAR TO SEE FORUMS AND SIDEBAR

derring do

New member
Joined
Sep 27, 2005
Messages
247
Points
0
Location
orlando
I've asked this before, but it's been 6 months, so here goes again. :)

As a safety measure, I'd like to have a text-listing of all the albums we own, all of which are now in iTunes. ITunes will give me the number of albums (792), but I can't find a place to give just a single record for the album and artist.

In the past I've copied the whole library file into Excel, and manually deleted the extra columns, then the redundant records, down to the single record for each album I am hoping for.

Is this the best (only?) way to do this? I am not an Excel jockey, so if there is a database command which will "select unique" or something to make this process easier, I'd love to hear about it. Thanks.
 

Baphomet

Resident Pagan Deity
Joined
Feb 1, 2006
Messages
670
Points
0
Age
58
Location
Right Behind You.
Okay, it just so happens that I *am* a bit of an Excel Jockey, so maybe I can help.

Let me make sure I understand what it is you want though.

You want an Excel spreadsheet that lists ONLY: Album Title and one particular track FROM that album. Is that correct?
 

derring do

New member
Joined
Sep 27, 2005
Messages
247
Points
0
Location
orlando
Yes, basically, that's very close. What I'd actually like is just the artist name and album title. Just enough info to let me keep track of all of my purchased music.

So, with my library I'd end up with a spreadsheet with 792 records, one for each artist/album. Does that make sense?

Thanks for your interest and help, Baphomet.
 

Baphomet

Resident Pagan Deity
Joined
Feb 1, 2006
Messages
670
Points
0
Age
58
Location
Right Behind You.
Okay... Got it. So your final output would have, basically, two columns:

Album Name
Artist Name

So what you're up against, basically, is getting rid of all the extraneous data.

Okay, this would be a snap if we were dealing with numbers (Sub Total function) but I'm not so sure about using it with cell ranges that only include data (i.e. text).

Give me a few minutes here... this has me intrigued in the extreme.

By the way, you have Excel, do you also have MS Access?
 

derring do

New member
Joined
Sep 27, 2005
Messages
247
Points
0
Location
orlando
Yes, precisely: two column output.

I had thought of subtotal, and run into the same issue.

Nope, no Access. A database is surely what I need for this. Of course, it would be nice if iTunes acted like a database, and provided this sort of information, too.

Thanks again, always happy to provide a challenge. ;)
 

Baphomet

Resident Pagan Deity
Joined
Feb 1, 2006
Messages
670
Points
0
Age
58
Location
Right Behind You.
Try this and see what you think.

Open your workbook and select your Header Row.
Click Data | Filter | Auto Filter
You now have a little Drop Down arrows on each column in your header row.
Use these Drop Down menus to Filter what you see. Play around with it... let me know what you think. This is a quick fix, but Auto Filter can be a very handle tool to work with.
 

derring do

New member
Joined
Sep 27, 2005
Messages
247
Points
0
Location
orlando
Thanks for the hint... I'll carve some time this afternoon and give this a try. At a minimum, I've learned something new about Excel, and am grateful for it. Thanks!
 

Baphomet

Resident Pagan Deity
Joined
Feb 1, 2006
Messages
670
Points
0
Age
58
Location
Right Behind You.
The solution offered is not ideal. I'm confident though, that I can crack this nut. Gotta handle some other issues, but I've bookmarked this thread. Do the same and keep an eye on it.

It ain't over till the Pagan Deity sings.
 

rockmyplimsoul

Active member
Joined
Jan 3, 2006
Messages
5,483
Points
36
Location
In the Pub
Website
www.last.fm
Here's how:
1. Paste your iTunes library selection into Excel.
2. Delete columns other than Artist and Album (song, rating, genre, etc.)
3. Ensure that there is a header row for "Artist" and "Album".
4. Select all cells, including the headers "Artist" and "Album".
5. Choose Data/Filter/Advanced Filter.
6. In the pop-up window, accept the default selection "Filter the list, in place".
7. IMPORTANT! In the same window be sure to check "Unique Records Only".
8. Click OK. You now have a list of all albums by each artist, with no repeats.
 

derring do

New member
Joined
Sep 27, 2005
Messages
247
Points
0
Location
orlando
Totally and completely awesome! Thank you, rmp, that is very slick.

Thanks again, baphomet, for your time and help, too.

I'll feel better with another listing of what we own, outside of iTunes...
 

derring do

New member
Joined
Sep 27, 2005
Messages
247
Points
0
Location
orlando
Great!

The only problem I've had is with compilations, and the way I coded them (each artist/album comes in with its own record). I'm going to exclude the compilations, and make a separate list of them, sorting only on album title.

:D
 
Last edited:

yustr

New member
Joined
Feb 21, 2007
Messages
4
Points
0
rockmyplimsoul said:
Here's how:
1. Paste your iTunes library selection into Excel.
I'm new here. Just got my 80G a couple of months ago. I too was wanting this same thing and came across this thread. My question is :How do I do this first step? Is it within the export functin? Is it just a highlight and copy?

Great forum.

Thanks, yustr
 

rockmyplimsoul

Active member
Joined
Jan 3, 2006
Messages
5,483
Points
36
Location
In the Pub
Website
www.last.fm
Yep, just highlight what you want then copy and paste. You can do this on your entire library, a playlist, or your iPod's contents (if it's manually sync'd). Note that you can change the view of what you copy by hiding certain columns first. That just eliminates the need to delete columns once you paste to Excel. Have fun!
 

silvery

New member
Joined
May 3, 2005
Messages
152
Points
0
Thank You!!!!!!!!!!

rockmyplimsoul

Thanks a million! I used to export the list in iTunes and then import into Excel, and then remove the columns I did not want. But the problem was that if there was too much info in the comments field it would bleed over into the first column. So, I came to iLounge tonight to search how to get around that. This worked like a charm, so perfect, so simple. Brilliant!
 
Top