Karaoke Forums

Can anyone help me with Excel? How do you get it to print two collums of song list on one page?  
As so... Number    Song    Artist       Number   Song    Artist

only way I know how is to export to WORD and then take that and do a dual column... not saying it's not possible, it's just what I used to do.

Also, Access, if you know how to configure it.

Lonman does that, and the program I use for my songbooks is an Access derivative.

you make 7 columns across in your spreadsheet

Column1:Number    Column2:Song     Column3:Artist   Column4:emtpy column Column5:Number     Column6:Song    Column7:Artist

I can't put it all across here cuz of the space you know?

but you can't "swing" stuff up from a page and make it print side by side if that's what you're asking..

The new laser monochrome printers like the Brother 2040 give the options of multiple columns per page either portrait or landscape.. They are also  capable of duplex or printing both sides..

 I would like this format in landscape in 2 or 4 columns....
  Artist        Artist          Artist        Artist

 Song1       Song1        Song1       Song1
 Song2       Song2        Song2       Song2

_________________
Join The Karaokle Singers Social Network. Upload Your Music!!

Karaoker,

I'm assuming the song list is in a single set of columns in Excel and you wish to print a report that displays two sets of columns side by side.  In addition, you want to maintain the sequence of the listings with the next item of the list at the bottom of the page in the left column set being at the top of thje page in the right column set.

You could, of course, cut and paste from the worksheet contining the single set of columns to a second worksheet in the format you wish.  But that's a lot of cutting and pasting, and each time you added a song to your master single column worksheet, you'd have to do it again.

If you are handy with Visual Basic for Applications, (Excel's macro language) you could write VB code that walks the rows in the single column in the single column worksheet and creates the dual column worksheet on the fly automatically.  

But short of that, I agree with the previous post.  This is an application that ideally belongs in a database like MS Access where you have the reporting tools to do what you want.  Excel is a pretty weak database manager especially on the reporting side.

Fortunately, it's pretty easy to import an Excel table into Access.  If you have MS Office Pro on your machine, you will also have MS Access.

If you don't have MS Access but have MS Word, you may want to spend some time in the help system for both products looking at how mailing labels can be produced in a Word mail merge from Excel.  What you want to do is very similar to producing mailing lables from a Word document using an Excel table as a source.  Word's mail merge function may provide the flexibility to do what you want with no need to convert your song list from Excel.

Tom

My current format is in a landscape fashion with 2 columns....


    Artist         Song1              Artist        Song1
                     Song2                             Song2

It winds up with about 100 songs per page side and is easy to read.. With the format I showed above 4 columns could be used...

 I think I am using Micro Office 2003 but with my simple mind and microsofts inability to write any software without slaughtering the english language and coming up with a total different language with new buzz words, I am lost with access or excel... Why in the hell should it take 3 quarters of college credits to learn a word or spreadsheet processor?

_________________
Join The Karaokle Singers Social Network. Upload Your Music!!

Karaoker,

When I posted my last post I hadn't seen your follow up post.  If you want to list the artist in one row then all the songs in following rows, mail merge probably won't be flexible enouth.  On the other hand - Artist, Song, Song number in a printed row is similar to City, State, Zip on the same line on a mailing label.  If you go the mailing label route, you are certain to have to make some compromises in how you lay out your report.

On the other hand, with Visual Basic for Applications, the limits all go away.  VBA is bacicelly Visual Basic 6.0 with extensions added to deal with objects uniue to Excel - worksheets, cells, ranges, etc.  It is very powerful, but you need to understand VB to write with it.  Excel provides a macro recorder that generates VB code based on actions you take but the code would need to be tweaked in a very significant manner for it to be useful on a reoccurring basis.

On the other hand, MS Access has very powerful reporting tools that could do what you want without the need to write any code at all.

Tom

Karaoker,

Our posts tend to keep crossing each other.  MS Office 2003 comes in a number of versions.  The MS Office pro version includes Access.  I can understand your frustration but spreadsheets weren't designed to do what you want to do.  Data bases were.  You're trying to use a hammer to unscrew a screw.

Your alternative to learning Access is to spend the money for the song list software that does what you want.  What you want to do isn't all that tough in Access.  But it comes down to either money or time.

Good luck.

Tom

I wish I was Spock and could do a mind meld with you Yuo have some knowledge that I want I will try to explain what I want again...The ability to list the artist and all songs by that artist below in the same column... I currently can list the artist onlyy once and all the artist songs in column 2 but if I could put the artists songs below the artist name I could then have 4 columns...

_________________
Join The Karaokle Singers Social Network. Upload Your Music!!

I understand what you are trying to do.  I sing Karaoke and have seen song books laid out in the format you are trying to replicate.  But you probably will also want songs listed aplhabetically by title.  That's one set of data but two different reports with somewhat different layouts.

Access was designed to allow you to output multiple reports from the same set of data.

Do you have Access?

Tom

On an excel forum I finally got the macro....This takes the format with the Artist in col A and Title in col B and puts the titles in col A under the artists This underlines the Artist but you can sub Underline with Bold and it makes the artist bold.... It also only lists the Artist once...



Sub Trkmov()

Dim iLastRow As Long
Dim i As Long
Dim iStart As Long
Dim rng As Range

iLastRow = Cells(Rows.Count, "B").End(xlUp).Row
iStart = iLastRow
For i = iLastRow To 2 Step -1
If Cells(i, "A").Value <> Cells(i - 1, "A").Value Then
Rows(i + 1).Insert
Cells(i + 1, "A").Value = Cells(i, "B").Value
iStart = i - 1
Cells(i, "A").Font.Underline = True
Else
Cells(i, "A").Value = Cells(i, "B").Value
End If
Cells(i, "B").Value = ""
Next i
End Sub




I will probably use this with 4 col in a landscape fashion....

Karaoker,

That macto is a step in the right direction.  I see the following issues arising.
1.  It is rewriting your list on the same worksheet as your original list, destroying the original list in the process.  If you want to maintain the original list for some other purpose like an alpha song list, make sure you don't save the version that's been modified by this macro under the same name as your original list.
2.  I don't see a song number in this routine.  Are you planning to produce song number and title in your 'by artist' list.
3.  It doesn't produce a four column layout.  To do that you will need to do a fair amount of manual cutting and pasting to produce what you want.  Then when you add new songs, you'll have to do it all over again.  If you want to maintain the list in the four column format (rather than the original) it will turn into a real nightmare.  For example, inserting a row to add a song will insert the row across all four columns.  Maintaining this list over time will be very tedious.  You don't impress me as a very patient guy.  
4.  By the way, what do you plan to do when an artist song list spills over to the next column or next page?  Do you want to repeat the artist name?

Do you understand the VB Code in that macro?  If so, I'd be happy to make some suggestions as to how to revise it to make maintenance a whole lot less frustrating.  If not, and you twist my arm real hard, I'd be happy to take a look at your list and take a crack at writing the macro you need to accomplish what you want.  I figure about 100-150 lines of VB code.

Tom

Thanks Tom  I'm using Roxbox right now and pulling the file out of the songlist.mdb access file.   I just delete all the columns except artist and track title resize sort then save as a seperate xls..  I havnt used song numbers for a couple of years now in fact most now just give me a song by the time they have the mic it is playing...

Yea this one is limited like you say it needs the artist listed on the next page and things like that and would like the option of 2 or 3 or 4columns..  

Found this today  which is more what I want.. It is the front end for access and with a laser printer I can print 6 columns in a landscape fashion and it is fairly easy to read Would like to have more control over this (font etc)... Printing duplex I can put 13k on about 30 sheets.... Maybe this one would give you some ideas too....

I'll attach a short xls for you tp play with....rename it with zip ext....

Here's an explanation of your routine.

Sub Trkmov()

Declares a subroutine (macro)

Dim iLastRow As Long
Dim i As Long
Dim iStart As Long
Dim rng As Range

Declares four variables to be used by the subroutine, one of which is never used.

iLastRow = Cells(Rows.Count, "B").End(xlUp).Row

Finds the last row in the worksheet containing song information and assigns the number of the row to the variable iLastRow

iStart = iLastRow

Assignes the value of iLastRow to iStart.  This is not a necessary step is the routine doesn't use iStart to do anything.

For i = iLastRow To 2 Step -1
....
....

Next i

This loop walks every line between iLastRow and Row 2 from the bottom up 1 line at a time.  In each line it executes the code between the For and Next statements.  In other words it starts at the bottom of your lists and works its way to the top (row 2).  Here's an explanation of the code between For and Next.

If Cells(i, "A").Value <> Cells(i - 1, "A").Value Then
....
Else
....
End If

This statement tests to see whether the Artist on this line in column A is the same as the Artist on the line immediately above.  If it isn't the same, the following code is executed.

Rows(i + 1).Insert

Inserts a new row immediately below the line being processed

Cells(i + 1, "A").Value = Cells(i, "B").Value

Should copy Artist name from the current line to the new line.  It actually copies song title to the new line.  I believe the statement should read
Cells(i + 1, "A").Value = Cells(i, "A").Value

iStart = i - 1

Not sure why he's doing this as the variable iStart is not used elsewhere in the routine.

Cells(i, "A").Font.Underline = True

Underlines the text in column A in the current row.  I think it should underline the text in row i + 1. - bug?  - should read - Cells(i+1, "A").Font.Underline = True

If the artist in the row immediately above is the same, it executes the following code.

Cells(i, "A").Value = Cells(i, "B").Value

Copies the song title from column B to column A.   I think there may be another bug here as the If - Else test will cause one set of commands or the other set of commands to be executed but not both.  Without testing it seems to me you'll end up with a Artist name rather than the song in the current row as well as the following row whenever the artist changes.  The above command should be in the block of text for both If and Else to solve this problem.

Once the If - Else - End if block has been executed, the following command is executed.

Cells(i, "B").Value = ""

Clears the song title in Column B of the current row.

Unless you sent whoever wrote this a list, it hasn't been tested, has two unused statements and as many as three bugs that need to be fixed.  And it destroys your original list in the process of creating the new one.  Aside from the bugs, this is not how I'd approach what you want to do.  If you are going to try to fix it make sure you have your original list saved under a different file name !!!!!

Tom

Yea this is strictly an experimental project Here is the original thread so you can follow the evolution... here

It does seem to work The problems and necessary features you listed I was already contemplating.....I would send you a roxbox mdb data file for access but its about 24 mb.... You could download a demo of Roxbox and build a database (would have to be zips) I really would like your opinion on that frontend for access.  It is about 95% what I want....

Access can do it nicely.  Within Excel, you can get a free "Add-In" called the ASAP Utilities that has a feature called "Paper Saver" (under ASAP's Format menu), that you select a range, and tell it how many columns and rows per page you it to format to.  It's works, but for real print control, if you have Access (or maybe Crystal Reports) and know how to use it, that is what I would recommend.  The ASAP Utility can be gotten at:

http://asap-utilities.com/

I've used it for years - it has some really nice features if you do a lot of work in Excel.

Good luck.

_________________
Sounds Great! Entertainment
Bartlett, Illinois

If you have Excel you probably have Access.  It's easy to use and you can make reports come out anyway you want, by artist, CDG, Title, or whatever.  I have a file already set up. If you'd like I'll send it to you.  You can also import Excel files.

Jackson

_________________
Singing just for you