Print Alphabetical Album List

[David Wakeford]

Hi,

Wondered if anyone can tell me how to Print an Alphabetical List of Artists on my B2 WITHOUT the number record first (or at all) so that I can check easily if album has been previously loaded.

When I use "List Albums" on Brennan the list is printed numbers first so Albums are pretty mixed up.  I know I can view albums via  the front panel display but I want a printed Artist alphabetical list.

Thanks

Dave W

[Daniel Taylor]

Put a USB stick in USB port C. 

Use the "b2db to USB C" command from the Settings > Maintenance menu.

That will get you the list with numbers.

Import the file into a spread sheet program, such as Excel.

Delete the leading colums that are either blank or have numbers in them.

Do a sort on Column A.

Save the file.

That will be a file with all the albums sorted by Artist name.

After doing the above, I got a file that starts out like this:

Animal    Bag    /    Offering    -1994               
Aquarium    Rescue    Unit    /    EeePeee    -1994           
Aquarium    Rescue    Unit    /    The    Calling    -2003       
"Bach,Steve"    /    Zero    Gravity    -19878               
"Band,The"    /    Greatest    Hits    "(Capitol,"    2000)           
"Bernstein,Leonard"    /    Rhapsody    in    Blue;    West    Side    Story    -1990
"Bolling,Claude"    /    Original    Boogie    Woogie    -1968           
Bonham    /    The    Disregard    of    Timekeeping    -1989       
"Cantrell,Jerry"    /    Boggy    Depot    -1998               
"Clapton,Eric"    /    Crossroads    Box    Set    -1988    Disc1       
"Clapton,Eric"    /    Crossroads    Box    Set    -1988    Disc2       
"Clapton,Eric"    /    Crossroads    Box    Set    -1988    Disc3       
"Clapton,Eric"    /    Crossroads    Box    Set    -1988    Disc4       

I chose to name my artists as last name first.  If you do it first name first, the list will appear sorted by first name. The year shown here is because I included it in the Album name as (1988) or similar, and the spreadsheet changed that to a negative number.  If you don't have that, it won't appear.

Try it.  Play around with it.  Probably you'll get something you like.

[Jasper Warwick]

I build a spreadsheet that auto scans the B2 and builds sorted lists of artist and album, more details here.

https://groups.google.com/forum/#!searchin/brennanb2/excel%7Csort:date/brennanb2/9XIheY_Q2Mk/dPOb58GlCwAJ

It will also auto build playlists based on genre.

[Daniel Taylor]

Hi Chris,

I'm having trouble figuring out how to do what you recommend.  I don't know how to add the formula to the cells without replacing the Artist/Album information that's already there.  And it gives me an error message telling me there is something wrong with the formula.  Also, I don't know how to copy the formula to the other cells, without erasing what was previously there.  Would you please advise?

[Chris A]

Hi Daniel

I am not surprised you are having trouble following my suggestion! I am not a spreadsheet "expert" and it is some time since I last performed this myself so this has taught me not to rely solely on my memory when posting recommendations!!!

Bear with me - I will update my post with corrected information that works (though that may not be today). Sorry to have caused confusion.

Chris.

[Daniel Taylor]

I'll be patient.  And I look forward to your response. 

[Daniel Taylor]

Thanks, Chris.

After your update, I'm getting good results with Microsoft Excel 97.  (Old, but it still works.)

Copying the formula to every new cell is quite time consuming, especially if there is a large number of albums.

Is there a short way to copy the formula to every cell in a column from start row to end row?

[Daniel Taylor]

I figured it out.  After grabbing a copy of the formula, you highlight the column of cells to receieve it and then paste.

However, I cannot delete the original column of text data, as the new column without the leading numbers does not have the actual data, but only a formula that refers to the previous data.  When I sort on the new colum, nothing changes because it sorts on the formula rather than the data to which the formula refers.

[Chris A]

My earlier attempts at explaining this were flawed (and, also, incomplete); there are also subtle differences of approach depending upon whether Open Office or Microsoft spreadsheets are used. To avoid further confusion, I will be deleting my earlier posts (Jan 10 and 11).

Another way tackle the issue is this:

Open the Album List in a spreadsheet program (as Daniel suggests). However, instead of deleting the numbers manually, you can use one of the spreadsheet's "Functions" that will strip out the first five leading characters (ie numbers and/or blank spaces) leaving the information you require. I have not tested this approach on a recent release of Microsoft Excel but it does work with Excel 2000 and with the current version of Open Office. I will deal with these separately even though some of the steps are similar.

 Excel 2000:

Assuming your first Artist Name appears in Cell A1, type the following function into an empty cell to the right of this (into, for example, Cell B1): 

=RIGHT(A1,LEN(A1)-5)                [Note: this uses a comma in the syntax]

Next, copy that function into the required number of cells in Column B.

Next, copy the contents of Column B and use Paste Special/Values into Column C.

Column C can now be sorted alphabetically and then printed out. Finally save.

 

 

Open Office: 

Assuming your first Artist Name appears in Cell A1, type the following function into an empty cell to the right of this (into, for example, Cell B1): 

=RIGHT(A1;LEN(A1)-5)                [Note: this uses a semicolon in the syntax]

Next, copy that function into the required number of cells in Column B.

Next, copy the contents of Column B and use Paste Special/Text & Numbers into Column C.                [Note: Open Office seems to assume the first cell at the top of a column (eg C1) is a "Heading" and won't sort it. The workaround is to Paste Special/Text & Numbers into Cell C2 (rather than C1).

Column C can now be sorted alphabetically and then printed out. Finally save.

Hope this works for you.

[Daniel Taylor]

Success.  Well done, Chris.  Thanks a bunch.

[Chris A]

Thanks, Daniel - I got there in the end!